audit trigger problem

Question

audit trigger problem

anthony wang

Old Hand

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

#125141

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 5 (of 5 total)

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

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 1

The problem is the Insert trigger fires only once for the batch of records inserted, not once for each record in the batch. Most inserts are "single" record inserts unless you are using "Select .... INTO .... FROM" or INSERT .... SELECT type statements. However "UPDATES" are a different animal in that you often get multi record updates with single statements (depends on the where clause) and the same as the insert trigger the update trigger will fire only once per batch update.

You will need to redesign your trigger to handle multiple records in the "inserted/deleted" trigger tables. I've never tried to do column by column auditing I've always resorted to "Shadow" tables that are identical to the main table (except they have their own identity column) and simply use a form of: insert into xxx_shadow (col1,.....) select col1,.... from inserted

That gets all the rows. I can provide some actual example code which determines if an Insert/Update or Delete activity is occuring so the row is "stamped" correctly, if you would like (nothing fancy but it's worked for me in the past. If you do a search for "auditing" on this board you should find some good examples of different techniques available (there are lots of smart folks around here with a lot of different experience).

James.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

Anthony... it's bad form on this forum to post the same question more than once... ticks people off. Go to your other thread and read what I posted.

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

David Burrows SSC Guru Points: 65144 More actions · Answer 3

...it's bad form on this forum to post the same question more than once... ticks people off. Go to your other thread and read what I posted

And referring to a thread without specifying it's id

Damn. Now I gotta search for it

Far away is close at hand in the images of elsewhere.
Anon.

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

Heh... you're right, David. But it was easy to find... just clicked on the author's name and looked at the previous posts for the author. Here's the URL for the other post...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=387006

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