How to create a trigger that uses INSERT, DELETE, UPDATE events

Question

I want to create a trigger for logging. Therefore, I need the event names INSERT, UPDATE, or DELETE.ie: one of these statements is used to execute the query, my trigger will start and run in the log.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER LogBuses ON Bus_table AFTER INSERT,DELETE AS BEGIN DECLARE @PlateNo nvarchar(50) IF INSERT//something like that-INSERTING- DELETING SELECT @PlateNo=PlateNo from inserted insert into Logger (EffectedTable,ActionName,EffectDate,EffectedID) VALUES ('Bus_table','Insert',SYSDATETIME (),@PlateNo); ELSE IF DELETE SELECT @PlateNo=PlateNo from deleted insert into Logger (EffectedTable,ActionName,EffectDate,EffectedID) VALUES ('Bus_table','Insert',SYSDATETIME (),@PlateNo); 

End go

Answer 1

You use the inserted and deleted tables. It is inserted if only the inserted table is populated, deleted if only the deleted table is populated and updated if both tables are full. Use if exists (select 1 from inserted) for testing.

 if exists (select 1 from inserted) and exists (select 1 from deleted) --update else if exists (select 1 from inserted) --insert else if exists (select 1 from deleted) --delete 

Answer 2

Create a stored procedure that logs and runs for each event that invokes the procedure, passing the necessary data.