Hi friends,
I'm trying to create a trigger for audit, I would like to get some help here with after insert,update and delete for specific columns (field_key & field_ser) in table test.
CREATE TABLE [dbo].[test](
[ID] [int] NOT NULL,
[name] [varchar](50) ,
[field_key] [int] ,
[field_ser] [varchar](40) )
CREATE TABLE [dbo].[audit] (
[seq] [int] ,
[varchar](50) ,
[a_date] [date],
[col] [varchar](50) ,
[old_col] [varchar](50) ,
[new_col] [varchar](50) )
For Example, when a record is inserted(or updated or deleted) for column test.field_key, it has to trigger an insert in audit table specifically with old value(audit.old_col) & new value (audit.new_col).
Similarly when a record is inserted(or updated or deleted) for column test.field_ser, it has to trigger an insert in audit table specifically with old value(audit.old_col) & new value (audit.new_col).
I tried the below but I know I'm not doing it right.. any help is greatly appreciated. Thank you.
CREATE TRIGGER test_trg ON test
AFTER INSERT,UPDATE,DELETE AS
DECLARE @id int,
@name varchar(50),
@field_key int,
@field_ser varchar(50));
SELECT @id = i.id FROM inserted i;
SELECT @name = i.name FROM inserted i;
SELECT @field_key = i.field_key FROM inserted i;
SELECT @field_ser = i.field_ser FROM inserted i;
SELECT @id = d.id FROM deleted d;
SELECT @name = d.name FROM deleted d;
SELECT @field_key = d.field_key FROM deleted d;
SELECT @field_ser = d.field_ser FROM deleted d;
October 14, 2022 at 12:39 pm
Quick question, do you need to capture inserts? If you are capturing updates and deletes, the inserts will be in the source table, normally no need to duplicate that data!
😎
Given the code you posted, your approach will fail as it can potentially only capture a single row in a multi-row addition, update or deletion.
HINT: construct the insert statement to the audit table as an insert-select from the pseudo tables.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER test_trg1
ON dbo.test
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(field_key)
BEGIN
INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
SELECT i.name, GETDATE() AS date, 'field_key', o.field_key, i.field_key
FROM inserted i
LEFT OUTER JOIN deleted d ON d.id = i.id
WHERE d.field_key IS NULL OR i.field_key <> d.field_key
END /*IF*/
IF UPDATE(field_ser)
BEGIN
INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
SELECT i.name, GETDATE() AS date, 'field_ser', o.field_ser, i.field_ser
FROM inserted i
LEFT OUTER JOIN deleted d ON d.id = i.id
WHERE d.field_ser IS NULL OR i.field_ser <> d.field_ser
END /*IF*/
/*end of trigger*/
GO
CREATE TRIGGER test_trg2
ON dbo.test
AFTER DELETE
AS
SET NOCOUNT ON;
INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
SELECT d.name, GETDATE() AS date, 'field_key', d.field_key, NULL
FROM deleted d
INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
SELECT d.name, GETDATE() AS date, 'field_ser', d.field_ser, NULL
FROM deleted d
/*end of trigger*/
GO
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".
October 21, 2022 at 12:32 pm
Thank you so much
October 21, 2022 at 2:57 pm
Thank you so much
You're the one that will have to support all that. Do you understand why Scott didn't use any variables? Do you know why Set NoCount On is so very important in a trigger?
--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