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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With