Definition:
A trigger is a special stored procedure that is automatically executed when you attempt to execute a specified data modification statement on a specified table. Microsoft SQL Server allows you to create multiple triggers for any given insert, update, or delete statements.
Basic Syntax: (the syntax in the help is too long)
Create trigger [triggername]
On [tablename]
For [insert] [, delete] [, update]
As
-- The operation statement to be executed by the trigger.
Go
Note:
The following statements are not allowed in triggers:
Alter database, create database, disk init,
Disk resize, drop database, load database,
Load log, reconfigure, restore database,
Restore log
Trigger instance:
Program code 1) create a test table (testtable)
If exists (select * From sysobjects where id = object_id (N 'testtable') and objectproperty (ID, N 'isusertable') = 1)
Drop table testtable
Go
Create Table testtable (testfield varchar (50 ))
2) create a table-based trigger (testtrigger)
If exists (Select name from sysobjects where name = 'testtrigger' and type = 'tr ')
Drop trigger testtrigger
Go
Create trigger testtrigger
On testtable
For insert, delete, update
As
If exists (select * From inserted)
If exists (select * From deleted)
Print '... Update'
Else
Print '... insert'
Else
If exists (select * From deleted)
Print '... delete'
Go
3) operate the testtable table and test the trigger testtrigger.
Execute the insert into statement, update statement, and delete Statement respectively to check the effect.
Insert into testtable values ('testcontent! ')
Update testtable set testfield = 'updatecontent'
Delete from testtable