Binaryworld - How to write a trigger for INSERT, UPDATE or DELETE operation ? ... [ SQL Server -> Trigger ]

Here is very basic code to write trigger for INSERT, UPDATE or DELETE operation on a table. Internally SQL Server manages two tables (inserted and deleted) to perform trigger operations.

All inserted records can be retrived by quering "inserted" table

Click here to copy the following block

select * from inserted

All deleted records can be retrived by quering "deleted" table

Click here to copy the following block

select * from deleted

All updated records can be retrived by joining with inserted and deleted tables. Since there is no seperate table named "updated" for updated records so you have to find updated records by joining "inserted" and "deleted" tables

Click here to copy the following block

SELECT i.*
FROM inserted i
INNER JOIN deleted d
ON i.ProductID = d.ProductID

Click here to copy the following block

set nocount on
go
if object_id('tr_tbl') is not null
drop table tr_tbl
go
create table tr_tbl(i int)
go
create trigger tr_test on tr_tbl for insert, update, delete as
if exists(select * from inserted) and exists(select * from deleted)
print 'Update...'
else
if exists(select * from inserted)
print 'Insert...'
else
print 'Delete...'
go

insert tr_tbl values(1)
update tr_tbl set i = i + 1
delete tr_tbl