April 25, 2013 at 4:14 am
Hi,
Have just created an audit trigger which will fire whenever a row is inserted, updated or deleted from a table. Problem is I get a row in my audit table [Audit].[SystemInfo] when I do an insert but not for an update or delete. I know those parts of the IF statement are executing as I'm writing to a logging table to check. Here is the code, any ideas on what is wrong?
ALTER TRIGGER dbo.SystemInfoTrg
ON dbo.SystemInfo
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SessionIDUNIQUEIDENTIFIER; SET @SessionID = NEWID();
DECLARE @InsertedINTEGER;
DECLARE @UpdatedINTEGER;
DECLARE @DeletedINTEGER;
SELECT @Inserted = COUNT(1) FROM INSERTED;
SELECT @Deleted = COUNT(1) FROM DELETED;
IF (@Inserted > 0 AND @Deleted > 0)
BEGIN
INSERT INTO [Audit].SystemInfo SELECT 'Deleted', @SessionID, * FROM DELETED;
INSERT INTO [Audit].SystemInfo SELECT 'Inserted', @SessionID, * FROM INSERTED;
INSERT INTO logging (f1) VALUES ('@Inserted > 0 AND @Deleted > 0');
END
ELSE BEGIN
IF (@Deleted > 0)
BEGIN
INSERT INTO logging (f1) VALUES ('Deleted');
INSERT INTO [Audit].SystemInfo SELECT 'Deleted', @SessionID, * FROM DELETED;
END;
IF (@Inserted > 0)
BEGIN
INSERT INTO logging (f1) VALUES ('Inserted');
INSERT INTO [Audit].SystemInfo SELECT 'Inserted', @SessionID, * FROM INSERTED;
END;
END
END
April 25, 2013 at 6:10 am
It works now, not sure what was wrong. :w00t:
April 25, 2013 at 10:35 am
You may gain some performance by avoiding counting all the rows in inserted and deleted:
ALTER TRIGGER dbo.SystemInfoTrg
ON dbo.SystemInfo
AFTER INSERT, DELETE, UPDATE
AS
SET NOCOUNT ON;
DECLARE @SessionIDUNIQUEIDENTIFIER;
SET @SessionID = NEWID();
IF EXISTS(SELECT TOP (1) * FROM inserted)
AND EXISTS(SELECT TOP (1) * FROM deleted)
BEGIN
INSERT INTO [Audit].SystemInfo SELECT 'Deleted', @SessionID, * FROM DELETED;
INSERT INTO [Audit].SystemInfo SELECT 'Inserted', @SessionID, * FROM INSERTED;
INSERT INTO logging (f1) VALUES ('@Inserted > 0 AND @Deleted > 0');
END
ELSE BEGIN
IF EXISTS(SELECT TOP (1) * FROM deleted)
BEGIN
INSERT INTO logging (f1) VALUES ('Deleted');
INSERT INTO [Audit].SystemInfo SELECT 'Deleted', @SessionID, * FROM DELETED;
END;
ELSE
BEGIN
INSERT INTO logging (f1) VALUES ('Inserted');
INSERT INTO [Audit].SystemInfo SELECT 'Inserted', @SessionID, * FROM INSERTED;
END;
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 26, 2013 at 12:52 am
Good point, thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply