CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Aykut Celik
Aykut Celik

Reputation: 511

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

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

Upvotes: 4

Views: 6008

Answers (2)

Eric
Eric

Reputation: 95093

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

Upvotes: 10

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

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

Upvotes: 1

Related Questions