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) "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".
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy