Triggers 2

  • Very, very nice. Thank you, Hugo!

  • Great question, Hugo. Thanks.

    Rob Schripsema
    Propack, Inc.

  • 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

  • Awesome question..

    Thanks..

  • Nice question Hugo...

  • 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

  • Good Question.

    When I saw the Count(*) I knew that that was where to concentrate.

    David

  • Very interesting one.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] ๐Ÿ˜‰

  • 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.

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thats a great question. For some reason last few questions have been very efficient. ๐Ÿ™‚

  • nice useful question

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply