Simple SQL Server trigger example

Note the use of inserted and deleted queries
-------------------------

-- When a person record is added to the person table
-- Save the primary key personname of the record in the changetable table.
Use testdb
If exists (Select name from sysobjects
Where name = 'personinsert' and type = 'tr ')
Drop trigger personinsert
Go
Create trigger personinsert
On person
For insert
As

Insert into testdb. DBO. changetable
(Changetablename, changetype, changetablepk, changetablepkvalue)
Select 'person ', 'insert', 'personname', personname from inserted
Go

-- When a person record is modified in the person table
-- Save the primary key personname of the record in the changetable table.

Use testdb
If exists (Select name from sysobjects
Where name = 'personupdate' and type = 'tr ')
Drop trigger personupdate
Go
Create trigger personupdate
On person
For update
As

Insert into testdb. DBO. changetable
(Changetablename, changetype, changetablepk, changetablepkvalue)
Select 'person ', 'update', 'personname', personname from inserted
Go

-- When a person record is deleted from the person table
-- Save the primary key personname of the record in the changetable table.

Use testdb
If exists (Select name from sysobjects
Where name = 'persondelete' and type = 'tr ')
Drop trigger persondelete
Go
Create trigger personupdate
On person
For Delete
As

insert into testdb. DBO. changetable
(changetablename, changetype, changetablepk, changetablepkvalue)
select 'person ', 'delete', 'personname', personname from deleted
go