March 2, 2015 at 3:31 am
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?
March 2, 2015 at 5:53 am
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
March 2, 2015 at 6:23 am
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.
March 2, 2015 at 10:58 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
March 2, 2015 at 2:09 pm
I'll never understand why people audit the INSERTED table, ever. It's a gross duplication of data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply