audit trigger problem

Question

audit trigger problem

anthony wang

Old Hand

Points: 323
More actions
August 1, 2007 at 8:10 am

#62609

I have several tables that implements triggers to audit data change and put change into audit table. But I just found a strange issue. If I insert several records to a audited table name Notification, the trigger will only insert last record into audit table. Here is the smaple of the code.
CREATE TRIGGER tr_uoaNotificationIns
       ON uoaNotification
       FOR INSERT
       AS

BEGIN

    DECLARE @tblName varchar(30),
            @rtnCode int,
            @bValue varchar(255),
            @aValue varchar(255),
            @colName varchar(30),
            @incidentID int,
            @userid varchar(30)

    SET @tblName = 'uoaNotification'
    SET @userid = dbo.f_GetContextEmpID()
    SELECT @incidentID = inserted.IncidentID FROM inserted


    BEGIN
        SET @colName = 'NotifyName'
        SELECT @bValue = ''
        SELECT @aValue = CAST(inserted.NotifyName AS VARCHAR(50)) FROM inserted
        IF RTrim(@bValue) <> RTrim(@aValue)
BEGIN
         EXEC @rtnCode = dbo.usp_uoaAuditIns @incidentID, @tblName, @colName, @bValue, @aValue, @userid
END
    END


    BEGIN
        SET @colName = 'NotifyDtm'
        SELECT @bValue = ''
        SELECT @aValue = CAST(inserted.NotifyDtm AS VARCHAR(50)) FROM inserted
        IF RTrim(@bValue) <> RTrim(@aValue)
BEGIN
         EXEC @rtnCode = dbo.usp_uoaAuditIns @incidentID, @tblName, @colName, @bValue, @aValue, @userid
END
    END

       BEGIN
        SET @colName = 'NotifyTypeID'
        SELECT @bValue = ''
        SELECT @aValue = CAST(inserted.NotifyTypeID AS VARCHAR(30)) FROM inserted
        IF RTrim(@bValue) <> RTrim(@aValue)
BEGIN
         EXEC @rtnCode = dbo.usp_uoaAuditIns @incidentID, @tblName, @colName, @bValue, @aValue, @userid
END
    END

END
Thank you very much for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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

rubes SSCrazy Points: 2058 More actions · Answer 1

The following statement, "SELECT @incidentID = inserted.IncidentID FROM inserted", will only keep the last record from inserted. Therefore, the rest of the processing only works against that incident id.

You have this approach in several other spots. It looks like you need a significant amount of reworking to handle multiple records in one insert statement.

Regards,
Rubes

anthony wang Old Hand Points: 323 More actions · Answer 2

Does that mean I have to use recursive trigger to do that? Do I have to use cursor here. I really don't want to. Is there any way around it to loop through the inserted table?

Thanks, Anthony

rubes SSCrazy Points: 2058 More actions · Answer 3

Well, I think you have several options...

Don't use a trigger at all. Instead, populate the audit table in the stored procedure that would populate the production table.
Use a cursor inside the trigger.
Use a non-cursor while loop inside the trigger.
Simplify the trigger... (Insert Into AuditTable Select fieldlist from Inserted).

Personally, I'd opt for option 1 or 4. If you need fancy logic requiring function calls or multiple statements, do #1. If you can accomplish everything in a simple insert, do #4.

Hope that helps.

Regards,
Rubes

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

Heh... first, your not auditing changes, you auditing the insertion of new data. If you want to audit changed, then you need to make the triggier FOR UPDATE, instead.

I've seen this type of problem before and it normally comes with being an Oracle programmer. Oracle triggers create the OLD and NEW variables for each column in a row and you have to use a very RBAR "FOR EACH ROW" in the trigger. SQL Server doesn't work that way... SQL Server triggers contain a DELETED and INSERTED table for all the rows that have been inserted, deleted, or updated. Both tables exist for all 3 types of queries but only the INSERTED table has anything in it for Inserts, only the DELETED table has anything in it for Deletes, and both tables have row in them for UPDATES. For updates, the INSERTED table contains the new modified rows and the DELETED table contains the old rows.

Do read about triggers in Books Online... the paradigm shift between Oracle (or something similar) and SQL Server is huge.

Also, I don't know what the dbo.usp_uoaAuditIns sproc does, but I guarantee it will absolutely be a huge problem for you triggers. Please post the code for it and let's see if we can help you rewrite these triggers so that the handle more than single row inserts and they actually stand a chance of having some performance associated with them.

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

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