April 25, 2013 at 10:07 am
Very, very nice. Thank you, Hugo!
April 25, 2013 at 11:23 am
Great question, Hugo. Thanks.
Rob Schripsema
Propack, Inc.
April 25, 2013 at 12:31 pm
Hugo Kornelis (4/25/2013)
L' Eomot Inversé (4/25/2013)
But I have a minor quibble: if this is a business rule, it should be enforced by the schema if possible, not by code; and this is a classic example of a rule that can be enforced by a check constraint.For a production implementation, I fully agree that a trigger, even a well-coded one, would be a terrible way to implement this rule.
I was pretty sure you would:-P
But for a QotD about triggers, using a CHECK constraint would not have been a very smart choice! 😉
And there's the challenge to ingenuity: devise a rule which can't be implemented as a check constraint (or any other sort of constraint) and therefor has to be implemented as a trigger (because ad hoc updates and inserts and deletes are allowed, so a trigger is the only way you can be sure of executing the code when needed) while at the same time the rule and the trigger are sufficiently non-complex to make a good QotD. I think that might be very difficult, depending on where the commplexity limit is.
Tom
April 25, 2013 at 7:46 pm
Awesome question..
Thanks..
April 26, 2013 at 3:52 am
Nice question Hugo...
April 26, 2013 at 4:49 am
Another interesting question, thanks Hugo.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 27, 2013 at 8:27 pm
Good Question.
When I saw the Count(*) I knew that that was where to concentrate.
David
April 30, 2013 at 12:13 am
May 1, 2013 at 5:20 am
Yes to me too it is returning only 1 count.
When ist row is being inserted and the trigger fires and there is sucessful insertion and in second insert when trigger fire it dectets the negative values it is erring out.
May 1, 2013 at 7:50 am
flangoo (5/1/2013)
Yes to me too it is returning only 1 count.When ist row is being inserted and the trigger fires and there is sucessful insertion and in second insert when trigger fire it dectets the negative values it is erring out.
Correct. And then the third insert attempt runs (since it is in a new batch), but due to the badly coded trigger, it won't catch the violation, so it will insert two more rows. And then the select returns a count of three.
If you got a different result when test-running the code on your test database, you probably either used an old, out-of-support version of SQL Server, or you made a mistake when copying the code.
May 9, 2013 at 1:39 am
Thats a great question. For some reason last few questions have been very efficient. 🙂
May 31, 2013 at 1:39 am
nice useful question
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply