Why does backup cause trigger to fire?

  • A job runs every morning at 3.00 am to back up a database. Many of the tables have triggers on to write updates, deletes and inserts to audit tables.

    A typical trigger looks like this.

    USE [ProjectDB_Live]

    GO

    /****** Object: Trigger [dbo].[trgStakeHolders] Script Date: 03/02/2015 10:23:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [dbo].[trgStakeHolders] ON [dbo].[StakeHolders]

    FOR INSERT, UPDATE, DELETE

    AS

    SET NOCOUNT ON

    SET XACT_ABORT ON

    SET ARITHABORT ON

    DECLARE @User varchar(255)

    DECLARE @EditTime datetime

    DECLARE @AuditType varchar(1)

    DECLARE @InsertID int

    DECLARE @DeleteID int

    set @User = suser_sname()

    set @EditTime = getdate()

    SELECT @InsertID = StakeholderID FROM Inserted

    SELECT @DeleteID = StakeholderID FROM Deleted

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

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

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

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

    BEGIN

    INSERT StakeHolders_Audit

    SELECT

    @AuditType,

    AuditDate = @EditTime,

    EditedBy = @User,

    StakeholderID,

    Active,

    DateAdded,

    dtRegistered

    FROM Inserted

    END

    IF @AuditType = 'D'

    BEGIN

    INSERT StakeHolders_Audit

    SELECT

    AuditDate = @EditTime,

    EditedBy = @User,

    StakeholderID,

    Active,

    DateAdded,

    dtRegistered

    FROM Deleted

    END

    I added a trigger to a table over the weekend - structured the same as the one above and, when the back up ran last night, it copied every row in the table into the audit table as if every row in the table had been updated. Any ideas why this might happen on one table out of about a hundred?

  • It wouldn't have been the backup. Backups don't make data changes, they definitely don't fire DML triggers

    Check the job, make sure no one's added another step that goes and updates all rows in that table. Check that there's no other job that runs at the same time. Also check the user recorded in the audit table, see if it's a different user to the SQLAgent service account, could just be coincidence.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If your backup job only performs a backup, then there's something else causing the triggers to fire. Check the things on Gail's list.

    My first guess is that you have users connected and working during the backup.

  • Btw, trigger coding should concentrate on efficiency, not "step by step" coding, something like below. In this case, the code below is probably easier to follow as well.

    ALTER TRIGGER [dbo].[trgStakeHolders] ON [dbo].[StakeHolders]

    AFTER DELETE, INSERT, UPDATE

    AS

    SET NOCOUNT ON

    SET XACT_ABORT ON

    SET ARITHABORT ON

    IF NOT EXISTS(SELECT TOP (1) 1 FROM inserted)

    BEGIN --DELETE was done

    INSERT StakeHolders_Audit (

    AuditDate,

    EditedBy,

    StakeholderID,

    Active,

    DateAdded,

    dtRegistered

    )

    SELECT

    AuditDate = GETDATE(),

    EditedBy = SUSER_SNAME(),

    StakeholderID,

    Active,

    DateAdded,

    dtRegistered

    FROM Deleted

    END --IF

    ELSE

    BEGIN --INSERT or UPDATE was done

    INSERT StakeHolders_Audit (

    AuditType,

    AuditDate,

    EditedBy,

    StakeholderID,

    Active,

    DateAdded,

    dtRegistered

    )

    SELECT

    AuditType = CASE WHEN EXISTS(SELECT TOP (1) 1 FROM deleted) THEN 'U' ELSE 'I' END,

    AuditDate = GETDATE(),

    EditedBy = SUSER_SNAME(),

    StakeholderID,

    Active,

    DateAdded,

    dtRegistered

    FROM Inserted

    END --ELSE

    GO --end of trigger

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • I'll never understand why people audit the INSERTED table, ever. It's a gross duplication of data.

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

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

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