Is this trigger okay?

  • Is the trigger below correctly written? The reason I ask is that when I look in the Audit tables I often see what look like duplicated entries.

    ALTER TRIGGER [dbo].[trgTAAdditionalWork] ON [dbo].[TAAdditionalWork]

    FOR INSERT, UPDATE, DELETE

    AS

    SET NOCOUNT ON

    SET XACT_ABORT ON

    SET ARITHABORT ON

    DECLARE @User varchar(255)

    DECLARE @EditTime smalldatetime

    DECLARE @AuditType varchar(1)

    DECLARE @InsertID int

    DECLARE @DeleteID int

    set @User = suser_sname()

    set @EditTime = getdate()

    SELECT @InsertID = TAAdditionalWorkID FROM Inserted

    SELECT @DeleteID = TAAdditionalWorkID FROM Deleted

    IF @InsertID IS NULL AND @DeleteID IS NOT NULL SET @AuditType='D' --delete

    IF @InsertID IS NOT NULL AND @DeleteID IS NULL SET @AuditType='I' --insert

    IF @InsertID IS NOT NULL AND @DeleteID IS NOT NULL SET @AuditType='U' --update

    IF @AuditType = 'I' OR @AuditType = 'U'

    BEGIN

    INSERT TAAdditionalWork_Audit

    SELECT

    @AuditType,

    AuditDate = @EditTime,

    EditedBy = @User,

    TAAdditionalWorkID,

    ACID,

    ActionID,

    CommissionedID,

    DtRevisedFinalSpec,

    DtActualFinalSpec,

    SpecSent,

    ContainsCIC,

    Stripped,

    StrippedBy1ID,

    StrippedBy2ID

    FROM Inserted

    END

    IF @AuditType = 'D'

    BEGIN

    INSERT TAAdditionalWork_Audit

    SELECT

    @AuditType,

    AuditDate = @EditTime,

    EditedBy = @User,

    TAAdditionalWorkID,

    ACID,

    ActionID,

    CommissionedID,

    DtRevisedFinalSpec,

    DtActualFinalSpec,

    SpecSent,

    ContainsCIC,

    Stripped,

    StrippedBy1ID,

    StrippedBy2ID

    FROM Deleted

    END

    Thanks for any help. I've often looked at it and it looks okay to me - but in the Audit table there are thousands of duplicate entries recorded as taking place at exactly the same time.

  • sku370870 (8/24/2010)


    Is the trigger below correctly written? The reason I ask is that when I look in the Audit tables I often see what look like duplicated entries.

    ALTER TRIGGER [dbo].[trgTAAdditionalWork] ON [dbo].[TAAdditionalWork]

    FOR INSERT, UPDATE, DELETE

    AS

    SET NOCOUNT ON

    SET XACT_ABORT ON

    SET ARITHABORT ON

    DECLARE @User varchar(255)

    DECLARE @EditTime smalldatetime

    DECLARE @AuditType varchar(1)

    DECLARE @InsertID int

    DECLARE @DeleteID int

    set @User = suser_sname()

    set @EditTime = getdate()

    SELECT @InsertID = TAAdditionalWorkID FROM Inserted

    SELECT @DeleteID = TAAdditionalWorkID FROM Deleted

    IF @InsertID IS NULL AND @DeleteID IS NOT NULL SET @AuditType='D' --delete

    IF @InsertID IS NOT NULL AND @DeleteID IS NULL SET @AuditType='I' --insert

    IF @InsertID IS NOT NULL AND @DeleteID IS NOT NULL SET @AuditType='U' --update

    IF @AuditType = 'I' OR @AuditType = 'U'

    BEGIN

    INSERT TAAdditionalWork_Audit

    SELECT

    @AuditType,

    AuditDate = @EditTime,

    EditedBy = @User,

    TAAdditionalWorkID,

    ACID,

    ActionID,

    CommissionedID,

    DtRevisedFinalSpec,

    DtActualFinalSpec,

    SpecSent,

    ContainsCIC,

    Stripped,

    StrippedBy1ID,

    StrippedBy2ID

    FROM Inserted

    END

    IF @AuditType = 'D'

    BEGIN

    INSERT TAAdditionalWork_Audit

    SELECT

    @AuditType,

    AuditDate = @EditTime,

    EditedBy = @User,

    TAAdditionalWorkID,

    ACID,

    ActionID,

    CommissionedID,

    DtRevisedFinalSpec,

    DtActualFinalSpec,

    SpecSent,

    ContainsCIC,

    Stripped,

    StrippedBy1ID,

    StrippedBy2ID

    FROM Deleted

    END

    Thanks for any help. I've often looked at it and it looks okay to me - but in the Audit table there are thousands of duplicate entries recorded as taking place at exactly the same time.

    It looks ok-ish, but if you have to check what kind of operation is occuring, and have a seperate branch for each, it really should be two different triggers. Generally you want to avoid constructs like this:

    SELECT @InsertID = TAAdditionalWorkID FROM Inserted

    SELECT @DeleteID = TAAdditionalWorkID FROM Deleted

    Although you're not actually using the variables for anything but a NULL check, it still sends a "smells funny" vibe any time I see something like this in a trigger.

    Aside from that small weirdness, I don't see any reason why it should be inserting duplicate rows into your audit table.

  • If you insert multiple records into TAAdditionalWork then you will get "duplicate" entries based on AuditDate in your Audit table. However I would not expect to see duplicates across the entire record unless your original data is duplicated. Are you saying the entire record is duplicated or just the AuditDate?

  • Thanks for the replies.

    The whole record is duplicated.

  • Do you have any other triggers associated with these tables?

  • Hi ,

    I too faced the similar problems and i came up with a solution. But before telling that, i would like 2 ask the following questions,

    1. Did you tried to insert the data's directly in back end ? ? Still yor are getting duplicates ? ?

  • sku370870 (8/24/2010)


    Is the trigger below correctly written? The reason I ask is that when I look in the Audit tables I often see what look like duplicated entries.

    It is not correctly written. But the errors I see in this trigger can't cause duplicated entries in the audit tables. I suggest you check if there are other triggers defined on the tables - maybe old ones that should have been replaced but were left in place when a new trigger was defined?

    Also, does your code use the new MERGE statement? I know that a MERGE can fire insert, update, AND delete triggers; I do not know if a single trigger for all these operations is fired once or multiple times (and I can't test it, as I don't have SQL2008 installed right now).

    The most important error is that this trigger is built on the assumption of a single row being inserted, deleted, or updated. A trigger is fired once per executed statement, not once per affected row; the "inserted" and "deleted" pseudo-tables contain all affected rows. "SELECT @InsertID = TAAdditionalWorkID FROM Inserted" will set the variable to one of the values in the pseudo-table, with no way to predict which one it will be. I'm afraid that your main problem is not duplicated audit entries, but missing audit entries.

    Another potential future problem is the use of INSERT without specifying the column list for the target table. A future change to the audit table layout might cause this code to switch columns or load columns with wrong data. If you're lucky, you get a conversion error. If you're unlucky, it might takes months before you notice the problem, and then you have a lot of manual cleanup to do...


    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/

  • select B.Name as TableName,A.name as TriggerName

    from sysobjects A,sysobjects B

    where A.xtype='TR'

    AND A.parent_obj = B.id

    Make use of the abocve query and check for other triggers that are involved in the table.

Viewing 8 posts - 1 through 7 (of 7 total)

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