Geek Answers Handbook

SQL Server trigger when inserting, deleting, and updating a table

I have a Product table and another ProductLog .

The log table should track two columns in the Product table. Every time there is an insert, update or delete in these columns, I need to update the log table.

Do I need to write three separate triggers or one trigger to handle these operations?

I also need to know the type of operation, for example, I will need to know if there was an entry in the log table due to insertion, deletion or updating. If anyone gives me an example that would be great.

+8
sql-server datatrigger
source share
2 answers

You only need one trigger

 CREATE TRIGGER [ProductAfter] ON [Product] AFTER INSERT, UPDATE, DELETE

You can determine which DML statement triggers the trigger based on the number of entries in the inserted and deleted tables available in the body of the trigger. For INSERT , deleted empty, for DELETE , inserted empty, for UPDATE both inserted and deleted not empty. For example,

 IF @@ROWCOUNT = 0 -- exit trigger when zero records affected BEGIN RETURN; END; DECLARE @type CHAR(1);-- 'U' for update, 'D' for delete, 'I' for insert IF EXISTS(SELECT * FROM inserted) BEGIN IF EXISTS(SELECT * FROM deleted) BEGIN SET @type ='U'; END ELSE BEGIN SET @type ='I'; END END ELSE BEGIN SET @type = 'D'; END;

In addition, see Tracking data changes , there is another option for tracking changes without triggers.

+24
source share

or simply

 DECLARE @type CHAR(1)= case when not exists(SELECT * FROM inserted) then 'D' when exists(SELECT * FROM deleted) then 'U' else 'I' end
+6
source share

More articles:


All Articles