Introduction to DML Triggers

  • Comments posted to this topic are about the item Introduction to DML Triggers

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Good informative article... Well explained...

  • Good article. I have a small correction. If the after trigger fails (or raises an exception), it does not fail the transaction. The client must handle it.

    A well behaved client has code like this:

    start transaction

    try

    update/insert/delete/sp

    commit

    except

    rollback;

    other actions like inform the user

    end;

    More often clients rely on autocommit and since the error condition happens after action, it's committed anyway. You can check this in management studio: put a raiserror in a trigger, execute update without BEGIN TRANSACTION, you have nothing to rollback and updates are in place.

  • Jack,

    Thanks for the article. I don't know if it is your cheeky picture or your writing style, but you have become one of my favorites on this forum.

    I am very familiar with Oracle triggers and I have not read up on the SQL Server implementation, but you left me with the impression, based on your set examples, that the trigger only fires once for each insert command, versus for every record inserted. I would assume it is for every record inserted, so the need for set stuff is moot. Did I just not get the reasoning behind your example?

    Tobar

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • I had a faulty trigger implementation a while back that I couldn't figure out. You're article let me see the problem.

    Thanks Jack

  • Tobar (10/14/2008)


    Jack,

    Thanks for the article. I don't know if it is your cheeky picture or your writing style, but you have become one of my favorites on this forum.

    I am very familiar with Oracle triggers and I have not read up on the SQL Server implementation, but you left me with the impression, based on your set examples, that the trigger only fires once for each insert command, versus for every record inserted. I would assume it is for every record inserted, so the need for set stuff is moot. Did I just not get the reasoning behind your example?

    Tobar

    Tobar,

    You had the correct impression. In SQL Server a trigger fires once per DML operation (Insert\Update\Delete). So, if I insert 3 rows in 1 operation the trigger fires once and the virtual inserted table will hold 3 rows and I will need to use set-based operations. Your understanding is why I wrote the article, because most folks new to SQL Server believe a trigger fires once per row, but triggers fire once per operation regardless of the number of rows affected.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Thanks for the clarification. Makes it harder on the trigger writer but potentially less stressful to the data base.

    <><
    Livin' down on the cube farm. Left, left, then a right.

  • IF Exists(Select * From inserted Where ContactId > 10)

    BEGIN

    Insert Into Person.ContactLog

    ( ContactID,Action )

    Select

    ContactID

    'Update'

    From

    inserted

    Where

    ContactId > 12

    END

    Jeff I have a question about the need to check for existance when nothing is being done on the else statement.

    Does this not create added overhead because if it doesn't exist it's filtered by the where clause of your select statement.

    I'm asking because in one of our systems we had a

    if (select count(*) from inserted) = 0 then

    return

    update MyTable

    set modifieddate = getdate()

    inner join inserted on

    Mytable.ID = inserted.ID

    When we commented out our if statement we saw a huge improvement in execution speed of the trigger.

  • Robert (10/14/2008)


    Good article. I have a small correction. If the after trigger fails (or raises an exception), it does not fail the transaction. The client must handle it.

    A well behaved client has code like this:

    start transaction

    try

    update/insert/delete/sp

    commit

    except

    rollback;

    other actions like inform the user

    end;

    More often clients rely on autocommit and since the error condition happens after action, it's committed anyway. You can check this in management studio: put a raiserror in a trigger, execute update without BEGIN TRANSACTION, you have nothing to rollback and updates are in place.

    Robert,

    Thanks for the comment. I believe you are incorrect in stating that the transaction will not fail on an error with the trigger. An error in a trigger will cause a rollback in my experience. Your are correct in stating that the client application should handle it, and then you can re-submit a corrected transaction, but left to itself the data modification is rolled back either explicitly like in your code, or implicitly. The trigger takes place WITHIN the outer transaction whether explicit or implicit. For example with this trigger in the AdventureWorks database:

    [font="Courier New"]ALTER TRIGGER [Person].[uContact] ON [Person].[Contact]

    AFTER UPDATE NOT FOR REPLICATION

    AS

    BEGIN

         SET NOCOUNT ON;

      

         UPDATE [Person].[Contact]

              SET [Person].[Contact].[ModifiedDate] = GETDATE()

         FROM

              #inserted -- non-existent table used intentionally

         WHERE

              inserted.[ContactID] = [Person].[Contact].[ContactID];

    END;[/font]

    If you run this code with explicit transactions:

    [font="Courier New"]-- row before update

    SELECT

       'Last Name Before Update' AS TYPE,

       LastName

    FROM

       person.contact

    WHERE

       ContactID = 12

    GO

    BEGIN Try

       BEGIN TRANSACTION

           UPDATE [AdventureWorks].[Person].[Contact]

                SET [LastName] = 'Changed'

           WHERE

                ContactID = 12

       -- this will never be hit due to error in trigger

       COMMIT TRANSACTION

       SELECT 'After Commit'

    END Try

    BEGIN Catch

       SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE

       -- if the explicit rollback is not entered another error is raised

       -- and the transcation is rolled back

       --RollBack Transaction

    END Catch

    GO

    -- row not updated

    SELECT

       'Last Name After Failed Update due to Trigger Error' AS TYPE,

       LastName

    FROM

       person.contact

    WHERE

       ContactID = 12[/font]

    or this code with implicit transaction:

    [font="Courier New"]-- row before update

    SELECT

       'Last Name Before Update' AS TYPE,

       LastName

    FROM

       person.contact

    WHERE

       ContactID = 12

    GO

    BEGIN Try

       UPDATE [AdventureWorks].[Person].[Contact]

            SET [LastName] = 'Changed'

       WHERE

            ContactID = 12

       -- this will never be hit due to error in trigger

       SELECT 'After Commit'

    END Try

    BEGIN Catch

       SELECT 'Error in Trigger' AS error, Error_Message() AS MESSAGE

    END Catch

    GO

    -- row not updated

    SELECT

       'Last Name After Failed Update due to Trigger Error' AS TYPE,

       LastName

    FROM

       person.contact

    WHERE

       ContactID = 12

    [/font]

    The end result is the same. The LastName for ContactID 12 is not updated.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • upperbognor (10/14/2008)


    Jeff I have a question about the need to check for existance when nothing is being done on the else statement.

    Does this not create added overhead because if it doesn't exist it's filtered by the where clause of your select statement.

    You are correct that I should have omitted the IF in the example code to provide a better trigger since the Where clause, which was incorrect as well (I used 12 instead of 10), does filter out the unwanted rows. The example was designed to show that the trigger using a variable to handle an IF will not work as anticipated (the prior trigger code) and to show the correct way to handle and IF condition in a trigger. The code within the IF was not the "important" part of the example. Perhaps there were other business rules that would have been enforced in that IF that required the IF. In this case that was not true.

    Thanks for the catch.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Andy Lennon (10/14/2008)


    I had a faulty trigger implementation a while back that I couldn't figure out. You're article let me see the problem.

    Thanks Jack

    Awesome. That's why I write so it's rewarding to hear it helped someone.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • There's one part of this article that I particularly appreciated. I had often read about certain tasks not not belonging in a trigger. But I could not figure out how one could automate and ensure the action happened if it wasn't coded at the database level. Now I know the idea of setting up the staging table in the trigger and later scheduling a job to do tasks off of the staging table. I rarely use triggers and when I do, it is usually just for basic auditing or occasional data validation stuff. So, I don't have an immediate use for this idea. But I will remember the idea and appreciate you writing about it.

    Thanks, - JJ

  • Very well written article.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Awesome article Jack!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • JJ, Timothy, and Barry,

    Thanks for the kind words. I just hope the article can be helpful especially to beginners.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 15 posts - 1 through 15 (of 31 total)

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