January 22, 2009 at 11:14 am
I would like to create a trigger (or some other database check) to verify that a newly inserted (or updated) row in the "Invoices" table has at least one related row in the "InvoiceLines" table.
All rows are inserted (or updated) as part of the same transaction. The Invoices row is inserted/updated first (it must exist before a InvoiceLines row can be inserted), so a trigger on the Invoices table has not been fruitful (obviously, no lines exist when the Invoices row is inserted).
Surely this has been done before, but by Google searches have turned up empty.
TIA.
January 22, 2009 at 1:57 pm
I don't think this has been done before.
You can't have an invoice lines without an Invoice.
But you want to require an invoice has Invoice lines when you insert the Invoice. This cannot be done.
What are you trying to protect against when trying to fulfill this requirement?
You can handle some of this with transactions
-- If both insert fine good.
Begin Tran
Insert into Invoices
Insert into InvoiceLines
-- If error inserting invoice lines then rollback.
If @@Error > 0
Rollback
Else
Commit
also to prevent someone from deleting all invoice lines without deleteing the invoice you can set up cascading deletes.
January 22, 2009 at 2:06 pm
It seems like you answered your own question...
>>>>The Invoices row is inserted/updated first (it must exist before a InvoiceLines row can be inserted)
Does that mean you have a foreign key constraint in place ?
I am not sure what you are trying to accomplish - are you concerned that a parent (invoice) record may get posted but no invoice LineItems ?
This seems like logic better suited to the application to me.
From a database constraint standpoint (based on the design - master/detail) it is not possible I thing, because it is a CIRCULAR relationship. Both entities cannot BOTH have a constraint saying that one must exist in BOTH, because at some point in time, only one exists.
Chicken, or egg ?
Unless I misunderstood..
You could however have maybe an ON UPDATE trigger that checks for existence of children - but again, what do you do with that result ? It seems more applicable at the application level...
"Hey user! you tried to post an invoice with no lines"
and either do not allow it, rollback the invoice header insert etc.
January 22, 2009 at 2:09 pm
You could control that through a proc that creates both at the same time. Might be a bit of a pain to put together, but it could certainly check for that. If @@Rowcount was zero for the line items insert, it could roll back the whole thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 7:43 pm
bbaley (1/22/2009)
It seems like you answered your own question...>>>>The Invoices row is inserted/updated first (it must exist before a InvoiceLines row can be inserted)
Does that mean you have a foreign key constraint in place ?
Yes, exactly. You obviously can't have invoice lines without an invoice, and that kind of constraint is trivial to set up.
bbaley (1/22/2009)
I am not sure what you are trying to accomplish - are you concerned that a parent (invoice) record may get posted but no invoice LineItems ?
Concerned? No. Convinced. It's already happened.
bbaley (1/22/2009)
This seems like logic better suited to the application to me.
I share your opinion. To me, *all* business logic is better suited to the application.
bbaley (1/22/2009)
From a database constraint standpoint (based on the design - master/detail) it is not possible I thing, because it is a CIRCULAR relationship. Both entities cannot BOTH have a constraint saying that one must exist in BOTH, because at some point in time, only one exists.Chicken, or egg ?
Unless I misunderstood..
Nope, you understand fine. 🙂 However, I disagree that it is circular logic. As I understand it, the concept of a transaction is "all or none" - and that's exactly what I'm trying to accomplish. I guess I would need some kind of trigger that fires just before the transaction is committed so that I can analyze the new database state in total before confirming the commit. It sounds like there is no database-level facility for that though.
Using a stored proc is almost the same as putting the business logic into the application (which has already been done). The application already has a centralized business object for handing invoices and invoice lines. If some process were bypassing the BO it would certainly bypass the SP too. I was just hoping there might be something additional I could do at the database level (i.e. db constraint) to ensure that an Invoice could not exist without at least one associated InvoiceLine.
Thanks for all of the replies. I really appreciate you guys taking the time to answer. If anyone else has any ideas I'd love to hear them. As I said, the application has been modified so it's not a dire situation, but I would still like to know if it's possible for my own edification.
January 22, 2009 at 7:46 pm
GSquared (1/22/2009)
"There does not now, nor will there ever, exist a programming language in which it is the least bit hard to write bad programs." - Lawrence Flon
By the way, that quote is golden.
January 23, 2009 at 7:48 am
Sure, I get it !
"circular reference" was a poor choice of words...
I think what GSquared was talking about was not necessarily a trigger, but nesting the insert/transaction, checking @@rowcount and rolling back the entire transaction set (detail and parent) if (any) child row inserts failed...
one example;
----------------
BEGIN TRANSACTION
DECLARE @RowCount INT
INSERT INTO T1 VALUES(...);
SET @RowCount = @@ROWCOUNT
IF @RowCount = 1
COMMIT TRANSACTION;
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Insert Failed, 16, 1);
END
....although I think @@Error might be cleaner and can be integrated into a more complete error checking for the entire insert logic;
(since you should check @@error anyway, why do both? )
another example;
---------------------
USE pubs
GO
DECLARE @ins_error int
BEGIN TRAN
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for the INSERT statement.
SELECT @ins_error = @@ERROR
IF @ins_error = 0
BEGIN
-- sucess
COMMIT TRAN
END
ELSE
BEGIN
IF @ins_error <> 0
PRINT "An error occurred during execution of the INSERT statement."
ROLLBACK TRAN
END
GO
I think also if you SET XACT_ABORT ON and start an explicit transaction- the entire batch will abort when an error
occurs and avoid leaving the transaction open after client-side attention events, like timeouts - which is probably when you are seeing this happen ? (guessing) - since you said the application already has logic to try and mitigate it.
.... since you mentioned it is an Invoice application...I assume there is different logic for inserting a COMPLETE invoice (header and detail) and for adding(inserting) just a new line item, etc ?
January 23, 2009 at 8:28 am
bbaley (1/23/2009)
since you said the application already has logic to try and mitigate it.
Sorry for the confusion. The application was changed *after* we discovered that invoices were being saved without invoice lines. I haven't seen it happen since the application was changed.
bbaley (1/23/2009)
I think what GSquared was talking about was not necessarily a trigger, but nesting the insert/transaction, checking @@rowcount and rolling back the entire transaction set (detail and parent) if (any) child row inserts failed...
I think you are right, and I think a stored proc would work fine. However, I was looking for more of a "database constraint" solution. One that cannot be circumvented under any circumstance, similar to the foreign key constraint that requires a parent to be present when inserting a child. Although, I must admit that even that constraint requires the parent to be inserted first even if all rows are being inserted in the same transaction. So I guess that your comment about "circular logic" is correct after all, now that I think about it more.
I guess this type of situation doesn't come up very often. It's the first time I've really encountered it. Although, I must admit that I tend to just put constraints in the application and leave it at that. I wonder if it might be nice if SQL Server had something similar to a two-phase commit for normal (non-distributed) transactions. If the individual statements were the "cohorts" then normal triggers could fire during the "commit request" phase and some new type of trigger could fire during the "commit" phase. These new triggers would then have access to all rows changed during the transaction and could perform more complex validation. Just thinking out loud, maybe I'm off my rocker.
Anyway, thanks again for the response.
January 23, 2009 at 9:01 am
Just to beat the dead horse deader.......
Since we were talking about logic on the application side vs. logic on the db side...
seems I always end up in some argument with DBA's I work with about what logic belongs where.... (I have the disadvantage of being a developer not a DBA)
In my opinion, the problem of "complex validation" on the database server side always seems to come up short or difficult, because what do you "do" with it? the error, or the indicator...
Somehow, the user has to know about it and ultimately do something....
Oh and by the way, your stored procedure can return @@Error result to the application also - so you could decide what to do there too, after the db has done it's part...
that way the database can manage constraints- and the application can respond to the same error event at the same time, with application logic, yes ?
January 23, 2009 at 9:41 am
The reason to do a check on @@Rowcount in this case, as opposed to just checking for errors, is that a failed insert isn't necessarily an error.
If it doesn't insert because it violates a constraint (PK, FK, check), that's one thing. That'll be caught by checking for errors.
If it doesn't insert because there's no data to insert, or because a trigger rolls back the insert, or some such, then you'll get a 0 for the error code (no error) and a 0 for the rowcount (which is a problem in this case).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 1:08 pm
bbaley (1/23/2009)
Just to beat the dead horse deader.......Since we were talking about logic on the application side vs. logic on the db side...
seems I always end up in some argument with DBA's I work with about what logic belongs where.... (I have the disadvantage of being a developer not a DBA)
In my opinion, the problem of "complex validation" on the database server side always seems to come up short or difficult, because what do you "do" with it? the error, or the indicator...
Somehow, the user has to know about it and ultimately do something....
Validation in DB vs validation in the application is an old debate. It can be an interesting to point out pros and cons of each, but ultimately people have to make up their own mind and once they do, they rarely change it.
As for me, I try to use the following as a general rule: The application should prevent user errors, the DB should prevent programmer errors.
Overgeneralized? Sure. Do I break it sometimes? Of course. But, in general, I try to use DB constraints to prevent a programmer (including myself) from doing something insane, like inserting invoices lines without an invoice. If that were to happen it wouldn't be the user's fault, so giving the user an error message probably isn't going to solve the problem (although they would probably pass the message along).
The opposite problem (inserting invoices without invoices lines) isn't something I would normally request of the DB, although it was an oversight by the programmer (in this case, me) that caused the problem. It's a marginal case, and I'm fine with letting the application handle it. I was just kinda wondering if/how the more "DBA" type people who tend to be more database focused might handle such a problem. It sounds like using a stored proc is the answer, which presumably means that access to the tables themselves would be need to be removed in order to prevent the application from bypassing the stored proc.
January 23, 2009 at 1:40 pm
bbaley (1/23/2009)
Just to beat the dead horse deader.......Since we were talking about logic on the application side vs. logic on the db side...
seems I always end up in some argument with DBA's I work with about what logic belongs where.... (I have the disadvantage of being a developer not a DBA)
In my opinion, the problem of "complex validation" on the database server side always seems to come up short or difficult, because what do you "do" with it? the error, or the indicator...
Somehow, the user has to know about it and ultimately do something....
Oh and by the way, your stored procedure can return @@Error result to the application also - so you could decide what to do there too, after the db has done it's part...
that way the database can manage constraints- and the application can respond to the same error event at the same time, with application logic, yes ?
In most cases, I'm a belt-and-suspenders guy on these things. I think the front end should tell you exactly what to do with your junk data before it ever sends it to the database, but I also think the database should have protection built into it to make sure that it catches the ball if the front end drops it.
That helps with the "is that the only application accessing the data" problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 6:58 pm
GSquared (1/23/2009)
If it doesn't insert because there's no data to insert, or because a trigger rolls back the insert, or some such, then you'll get a 0 for the error code (no error) and a 0 for the rowcount (which is a problem in this case).
GSquared:
Sure, I guess I was going on the premise there were no (other) triggers and/or the application would prempt any "no data to insert" event....
In your experience, what can cause an insert of the "no data to insert" type ?
(assuming there were proper constraints and foreign keys defined)
If you don't mind, an example that I could try/illustrate would be very handy for me to visualize instances I may have mishandled ! (although I like to pretend I am aware of all the "bad" data and orphaned records I have created)...
January 23, 2009 at 8:17 pm
bbaley (1/23/2009)
GSquared (1/23/2009)
If it doesn't insert because there's no data to insert, or because a trigger rolls back the insert, or some such, then you'll get a 0 for the error code (no error) and a 0 for the rowcount (which is a problem in this case).GSquared:
Sure, I guess I was going on the premise there were no (other) triggers and/or the application would prempt any "no data to insert" event....
In your experience, what can cause an insert of the "no data to insert" type ?
(assuming there were proper constraints and foreign keys defined)
If you don't mind, an example that I could try/illustrate would be very handy for me to visualize instances I may have mishandled ! (although I like to pretend I am aware of all the "bad" data and orphaned records I have created)...
Let's say you've got:
insert into dbo.MyTargetTable (columns)
select columns
from dbo.MySourceTable
where Column = @VariableValue
If the Where clause produces no rows, you're going to insert no rows.
This is usually a minor detail, perhaps even expected and desired. But what happens if you have:
insert into dbo.MyFirstTargetTable (columns)
select columns
from dbo.MySourceTable
where Column = @VariableValue
set @FirstID = scope_identity()
insert into dbo.MySecondTargetTable (columns)
select columns
from dbo.MySourceTable
where Column = @VariableValue
set @SecondID = scope_identity()
Seems like an okay piece of a proc, doesn't it? Until you realize that, if no rows are inserted into the second table, scope_identity will have the value for the first table, and anything you do with that value, expecting it to be the value from the second table, is going to be wrong.
Add all the error-checking you want to that, add Try...Catch blocks, etc., and it still won't catch a zero-rows insert into that second table.
Now, imagine what your code will do if that insert is two rows, or 200? Suddenly scope_identity isn't adequate. It'll have A right value, but it won't have all the values.
That's why @@Rowcount can be very, very important. Very.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply