Triggers for Auditing

  • Hi Richard,

    I Agree that  INSERTED and DELETED tables have blob fields within INSTEAD OF triggers.  Thank you for correcting the mistake. However, I disagree with what you say with UPDATETEXT/WRITETEXT.  If you are using stored procedures to update the tables, you can always refer the additional column (with less cost)  and update last_modified field.  Why do you want triggers on the first place.  Triggers is one of the mechanism where we can control ad-hoc queries. What about adhoc queries using UPDATETEXT/WRITETEXT? It shows that, triggers is not the best solutions.

    On the other hand, triggers could be used against tables which do not have blob fields. I use it.  it is one of the best methods to add audit component against, existing table.

     

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • "What about adhoc queries using UPDATETEXT/WRITETEXT?"

    I would never allow such a thing, unless it was in a custom SP that updated the audit table in later statement.  The same is true if you update a field in the original table:  I would never allow it outside a custom SP.  Therefore, I would not worry about the trigger-firing issue.  But it is good that you pointed out this undocumented (in BOL, AFAIK) problem.

    I would guess that your need to write/update text blobs line by line is very unusual.  I guess that >90% of databases will not encounter this issue.  But if you need it, you need it!

    Good luck,

    Rich

  • "On the other hand, triggers could be used against tables which do not have blob fields. I use it.  it is one of the best methods to add audit component against, existing table."

    I just re-read your post, and I think I understand you point a bit better.  I think it is reasonable to write audits into a text/ntext field, although I have not seen anyone else doing that.  You could write into the same record or into a blob in another audit table (This may be easier and more flexible).  However, in either case, you should be able to write to the blob field inside an "Instead Of" trigger. 

    In response to the previous post by Serqiy, this does NOT fire reentrant/recursive triggers by default.  I don't know what will happen if you turn on the recursive_triggers option - but I would not want to try it.)

  • Hi Sergiy,

    There's no need to. An INSTEAD OF UPDATE trigger will enver fire on an UPDATE statement that is executed in the context of the trigger. Cut and pastte the code below in Query Analyzer for a quick proof.

    CREATE TABLE Test (A int NOT NULL PRIMARY KEY,

                       B int)

    go

    INSERT INTO Test (A, B)

    VALUES (1, 1)

    go

    CREATE TRIGGER TestTrig

    ON Test

    INSTEAD OF UPDATE

    AS

    PRINT 'Trigger nest level ' + CAST(TRIGGER_NESTLEVEL() AS varchar(3))

    UPDATE Test

    SET    B =   (SELECT inserted.B

                  FROM   inserted

                  WHERE  inserted.A = Test.A)

    WHERE EXISTS (SELECT inserted.B

                  FROM   inserted

                  WHERE  inserted.A = Test.A)

    go

    UPDATE Test

    SET    B = 2

    WHERE  A = 1

    go

    SELECT * FROM Test

    go

    DROP TRIGGER TestTrig

    DROP TABLE Test

    go

    Best, Hugo


    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/

Viewing 4 posts - 16 through 19 (of 19 total)

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