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

Question

I want to create a trigger for logging.So i need event names of INSERT,UPDATE or DELETE.i.e : one of these statements is used in query execution my trigger will trig and starts logging.

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

Eric · Accepted Answer · 2011-12-28 21:04:32Z

10

You use the inserted and deleted tables. It's inserted if just the inserted table is populated, deleted if just the deleted table is populated, and updated if both tables are populated. Use if exists (select 1 from inserted) to test.

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

answered Dec 28, 2011 at 21:04

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maxim Krizhanovsky · Accepted Answer · 2011-12-28 21:04:01Z

1

Create a stored procedure that logs, and triggers for each event, that call the procedure, passing the needed data.

answered Dec 28, 2011 at 21:04

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related