Trigger for Auditing

  • Super Cat

    SSCertifiable

    Points: 7316

    Probably been asked before but if I want to create a trigger for DML auditing on a particualr table.
    Will FOR INSERT, DELETE suffice rather than
    FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a Delete

    So the question again For I&D or IU&D.

  • Thom A

    SSC Guru

    Points: 98644

    You say auditing but what is it you're trying to achieve? Are you trying to check it's in a valid format, for example? This might be more easily achieved by using constraints, for example, rather than relying on triggers.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Super Cat

    SSCertifiable

    Points: 7316

    Simple audit who change what when and to what.
    From a few years back I remember reading that INSERTED UPDATED and DELETED are not all needed.
    What I'm trying to find or remember is which one is not needed. I believe it is UPDATED as UPDATEs can be recorded as DELETED.

  • Jeff Moden

    SSC Guru

    Points: 996645

    Talib123 - Wednesday, July 5, 2017 4:41 AM

    Probably been asked before but if I want to create a trigger for DML auditing on a particualr table.
    Will FOR INSERT, DELETE suffice rather than
    FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a Delete

    So the question again For I&D or IU&D.

    IMHO, never audit inserts.  Original inserts will be in the original table until there's an update or delete.  Only log the "DELETED" side of a trigger.  Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lowell

    SSC Guru

    Points: 323450

    create a table with the same schema as the table you want to audit.
    add additional columns for capturing whodunnit information.
    then you just need a trigger to handle update and delete;

    so say you have a table name dbo.Invoices, you might want to create a new schema History,and create the table History.Invoices to capture changes.
    that History table cannot have the same primary key, as the same row could be modified more than once.
    So if I assume you created Hostiry.Invoices, here's how i would do it:

    --add some auditing columns
    ALTER TABLE History.Invoices ADD
    [EventAction] VARCHAR(30)     NOT NULL,
    [EventDate]      DATETIME               NOT NULL,
    [DBName]       VARCHAR(128)               NULL,
    [CurrentUser]     VARCHAR(128)               NULL,
    [HostName]      VARCHAR(128)               NULL,
    [ApplicationName]    VARCHAR(128)               NULL,
    [ProcedureName]    VARCHAR(128)               NULL,
    [Userid]       SMALLINT                 NULL,
    [UserName]      VARCHAR(128)               NULL,
    [sUserid]      INT                  NULL,
    [sUserName]      VARCHAR(128)               NULL,
    [Is_ServerAdmin_Sysadmin] INT                  NULL,
    [Is_DB_owner]     INT                  NULL,
    [Is_DDL_Admin]     INT                  NULL,
    [Is_DB_Datareader]   INT                  NULL,
    [ORIGINAL_LOGIN]    VARCHAR(128)               NULL,
    [net_transport]    VARCHAR(128)               NULL,
    [protocol_type]    VARCHAR(128)               NULL,
    [auth_scheme]     VARCHAR(128)               NULL,
    [local_net_address]   NVARCHAR(128)               NULL,
    [local_tcp_port]    NVARCHAR(128)               NULL,
    [client_net_address]   NVARCHAR(128)               NULL,
    [physical_net_transport] NVARCHAR(128)               NULL

    GO
    create TRIGGER TR_InvoiceAudit ON [dbo].[Invoices]
    WITH EXECUTE AS OWNER --avoid permissions problems
    FOR UPDATE, DELETE
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @Action VARCHAR(30)

    SELECT @Action = CASE
           WHEN NOT EXISTS(SELECT 1 FROM INSERTED)
           THEN 'Deleted'
           ELSE 'Update'
          END
          -- I only need the "old" value, or the deleted value, since the current is in the real table; i can join on keys to compare if needed.
    INSERT INTO History.Invoices([OriginalColumnList],
              [EventAction],
              [EventDate],[DBName],[CurrentUser],[HostName],[ApplicationName],[ProcedureName],[Userid],[UserName],[sUserid],[sUserName],[Is_ServerAdmin_Sysadmin],[Is_DB_owner],[Is_DDL_Admin],[Is_DB_Datareader],[ORIGINAL_LOGIN],[net_transport],[protocol_type],[auth_scheme],[local_net_address],[local_tcp_port],[client_net_address],[physical_net_transport])
    SELECT [OriginalColumnList],
      @Action AS [EventAction],
      getdate()                    AS EventDate,
      CONVERT(varchar(128),DB_NAME())            AS DBName,
      CONVERT(varchar(128),CURRENT_USER)           AS CurrentUser,
      CONVERT(varchar(128),HOST_NAME())            AS HostName,
      CONVERT(varchar(128),APP_NAME())            AS ApplicationName,
      CONVERT(varchar(128),OBJECT_NAME(@@PROCID))        AS ProcedureName,
      USER_ID()                    AS Userid,
      CONVERT(varchar(128),USER_NAME())            AS UserName,
      SUSER_ID()                   AS sUserid,
      CONVERT(varchar(128),SUSER_SNAME())           AS sUserName,
      IS_SRVROLEMEMBER ('sysadmin')             AS [Is_ServerAdmin_Sysadmin],
      IS_MEMBER('db_owner')                AS [Is_DB_owner],
      IS_MEMBER('db_ddladmin')               AS [Is_DDL_Admin],
      IS_MEMBER('db_datareader')              AS [Is_DB_Datareader],
      CONVERT(varchar(128),ORIGINAL_LOGIN())          AS [ORIGINAL_LOGIN],
      CONVERT(varchar(128),ConnectionProperty('net_transport'))    AS 'net_transport',
      CONVERT(varchar(128),ConnectionProperty('protocol_type'))    AS 'protocol_type',
      CONVERT(varchar(128),ConnectionProperty('auth_scheme'))    AS 'auth_scheme',
      CONVERT(varchar(128),ConnectionProperty('local_net_address'))  AS 'local_net_address',
      CONVERT(varchar(128),ConnectionProperty('local_tcp_port'))   AS 'local_tcp_port',
      CONVERT(varchar(128),ConnectionProperty('client_net_address'))  AS 'client_net_address',
      CONVERT(varchar(128),ConnectionProperty('physical_net_transport')) AS 'physical_net_transport'
      FROM [DELETED] [MySource]
    END -- TRIGGER

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Jeff Moden - Wednesday, July 5, 2017 6:46 AM

    Talib123 - Wednesday, July 5, 2017 4:41 AM

    Probably been asked before but if I want to create a trigger for DML auditing on a particualr table.
    Will FOR INSERT, DELETE suffice rather than
    FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a Delete

    So the question again For I&D or IU&D.

    IMHO, never audit inserts.  Original inserts will be in the original table until there's an update or delete.  Only log the "DELETED" side of a trigger.  Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.

    1) I'm going to play counterpoint to the recommendation to not log INSERTs and to only log DELETED side. An upside for doing it the other way is that everything you need for any investigation is now in a single table. The production table never need be hit for any investigation you need to do. No join, and in many/most cases you won't need a SORT to get things into a form that would be consumable for an investigation either.

    2) Jeff, I don't really understand the statement about data explosion. If I only log INSERTs, DELETEs and the INSERT-side of UPDATES, how do I get (unwanted) data explosion?

    3) VERY important: NEVER put more than ONE form of audit code in each trigger. i.e. if you need to audit all three DML actions, you MUST have 3 triggers. I have a client right now that is absolutely crushing their system with 3-fer audit triggers due to the need to join INSERTED/DELETED 3 times just to find out which action the trigger is firing for. Yes, it would be more efficient to do 2 EXISTS instead of the joins they are doing. But it would be even faster so simply do the singular action required. 

    That last point brings up a good Guruism:

    The fastest thing you can do in SQL Server is NOTHING!!  

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Super Cat

    SSCertifiable

    Points: 7316

    Thanks for all the advice guys.

  • Jeff Moden

    SSC Guru

    Points: 996645

    TheSQLGuru - Wednesday, July 5, 2017 7:23 AM

    Jeff Moden - Wednesday, July 5, 2017 6:46 AM

    Talib123 - Wednesday, July 5, 2017 4:41 AM

    Probably been asked before but if I want to create a trigger for DML auditing on a particualr table.
    Will FOR INSERT, DELETE suffice rather than
    FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a Delete

    So the question again For I&D or IU&D.

    IMHO, never audit inserts.  Original inserts will be in the original table until there's an update or delete.  Only log the "DELETED" side of a trigger.  Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.

    1) I'm going to play counterpoint to the recommendation to not log INSERTs and to only log DELETED side. An upside for doing it the other way is that everything you need for any investigation is now in a single table. The production table never need be hit for any investigation you need to do. No join, and in many/most cases you won't need a SORT to get things into a form that would be consumable for an investigation either.

    2) Jeff, I don't really understand the statement about data explosion. If I only log INSERTs, DELETEs and the INSERT-side of UPDATES, how do I get (unwanted) data explosion?

    3) VERY important: NEVER put more than ONE form of audit code in each trigger. i.e. if you need to audit all three DML actions, you MUST have 3 triggers. I have a client right now that is absolutely crushing their system with 3-fer audit triggers due to the need to join INSERTED/DELETED 3 times just to find out which action the trigger is firing for. Yes, it would be more efficient to do 2 EXISTS instead of the joins they are doing. But it would be even faster so simply do the singular action required. 

    That last point brings up a good Guruism:

    The fastest thing you can do in SQL Server is NOTHING!!  

    Heh... I'll have to disagree but not for the reason you might suspect.  You shouldn't be auditing INSERTs to begin with and you should only audit the DELETED logical table.  If you're doing whole row auditing (instead of column auditing), you don't even have to look at the INSERTED logical table in triggers.  Of course, the exception to that rule is if you want to check every column for an actual change and not just an accidental repeated update.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996645

    Jeff Moden - Thursday, July 6, 2017 10:19 PM0oi]

    Jeff Moden - Thursday, July 6, 2017 10:19 PM

    TheSQLGuru - Wednesday, July 5, 2017 7:23 AM

    Jeff Moden - Wednesday, July 5, 2017 6:46 AM

    Talib123 - Wednesday, July 5, 2017 4:41 AM

    Probably been asked before but if I want to create a trigger for DML auditing on a particualr table.
    Will FOR INSERT, DELETE suffice rather than
    FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a Delete

    So the question again For I&D or IU&D.

    IMHO, never audit inserts.  Original inserts will be in the original table until there's an update or delete.  Only log the "DELETED" side of a trigger.  Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.

    1) I'm going to play counterpoint to the recommendation to not log INSERTs and to only log DELETED side. An upside for doing it the other way is that everything you need for any investigation is now in a single table. The production table never need be hit for any investigation you need to do. No join, and in many/most cases you won't need a SORT to get things into a form that would be consumable for an investigation either.

    2) Jeff, I don't really understand the statement about data explosion. If I only log INSERTs, DELETEs and the INSERT-side of UPDATES, how do I get (unwanted) data explosion?

    3) VERY important: NEVER put more than ONE form of audit code in each trigger. i.e. if you need to audit all three DML actions, you MUST have 3 triggers. I have a client right now that is absolutely crushing their system with 3-fer audit triggers due to the need to join INSERTED/DELETED 3 times just to find out which action the trigger is firing for. Yes, it would be more efficient to do 2 EXISTS instead of the joins they are doing. But it would be even faster so simply do the singular action required. 

    That last point brings up a good Guruism:

    The fastest thing you can do in SQL Server is NOTHING!!  

    Heh... I'll have to disagree but not for the reason you might suspect.  You shouldn't be auditing INSERTs to begin with and you should only audit the DELETED logical table.  If you're doing whole row auditing (instead of column auditing), you don't even have to look at the INSERTED logical table in triggers.  Of course, the exception to that rule is if you want to check every column for an actual change and not just an accidental repeated update.

    Sorry... I didn't actually address your second question... If you log INSERTs, that's an automatic doubling of the data because the original data lives both in the original table and the and the audit table.  For rows that are never updated, that's mostly a waste of storage.  If you record just the INSERTED logical table for updates, then not so bad a data explosion as what a lot of people get because they record both the INSERTED and DELETED logical tables for every blooming update. 

    I do agree that if you spend a whole lot of time researching what's in an audit table, then recording INSERTs and just the INSERTED logical table for updates (as well as any DELETEs) will make life easier there and will, as you stated, keep you from having to join to the original table.  If you don't have the need to do such research very often, then the disk savings by not auditing the original insert can be worth it especially when it comes to whole row auditing.

    If you're doing the other type of auditing, columnar auditing, then it's essential that you don't audit inserts because the disk space requirements for the audit table will easily be 5 times the size of the original table just for the INSERTs.

    As you say, you can get by the requirement of joining the INSERTED and DELETED tables altogether using EXISTS but I've found that using NOT EXISTS is slightly more effective.  Here's that snippet of code from the audit triggers that I have in production.  And, if the answer is "I", I simply do a return with no action taken.  The code will seem backwards at first until you remember that I'm using NOT EXISTS.  Because of the "short circuit" done by the case and the NOT negation, INSERTs only have to look at one of the logical tables instead of both.

    --===== Determine the type of trigger action
     SELECT @Operation   = CASE
                           WHEN NOT EXISTS (SELECT TOP 1 1 FROM DELETED)  THEN 'I'
                           WHEN NOT EXISTS (SELECT TOP 1 1 FROM INSERTED) THEN 'D'
                           ELSE 'U'
                           END
    ;

    It is a shame that SQL Server doesn't have a "TriggerType()" function built into it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • TheSQLGuru

    SSC Guru

    Points: 134017

    I don't like the unnecessary TOP 1 1 you have in your code, although fortunately the optimizer has been coded to ignore it and do the same thing as a check without it. 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Jeff Moden

    SSC Guru

    Points: 996645

    TheSQLGuru - Monday, July 10, 2017 1:28 PM

    I don't like the unnecessary TOP 1 1 you have in your code, although fortunately the optimizer has been coded to ignore it and do the same thing as a check without it. 🙂

    Ah... I absolutely agree.  It's not there for functionality.  It's there to convince the uninformed.  I originally couldn't convince folks at work as to what "*" would do there.  It's an appeasement for doubters and provides a clue for the uninformed.  As you say, it get's ignored by the thing that matters most so I never fixed it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 11 (of 11 total)

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