在SQL Server 2005中，触发器可以分为两大类：DML触发器和DDL触发器

? DML触发器：DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程。DML触发器又分为两类：After触发器和Instead Of触发器

? DDL触发器：DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。

?DML触发器的分类

SQL Server 2005的DML触发器分为两类：

? After触发器：这类触发器是在记录已经改变完之后（after），才会被xx执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。

?

?Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作

?

create table employees(

??? employee_id int primary key not null,

??? last_name varchar(50) null,

??? first_name varchar(50) null,

??? hire_date datetime null,

??? job_id int null,

??? email varchar(500) null,

??? department_id int null,

??? salary int null,

??? commission_pct int null

)

?

create trigger employees_update

?on employees

?? after update

?as

?begin

???????? print '有一个员工更改了'

?end

?

create trigger employees_insert

?on employees

?? after insert

?as

?begin

???????? print '有一个员工增加了'

?end

?

create trigger employees_delete

?on employees

?? after delete

?as

?begin

???????? print '有一个员工删除了'

?end

?

?

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 12345,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

?

delete employees

?

update employees set last_name = 'dabing' where employee_id=12345;

?

select * from employees

select commission_pct from employees where employee_id=12345;

?

?

?

?

drop trigger employees_insert

create trigger employees_insert

? on employees

? after insert

as

begin

? declare @employee_id int

? set @employee_id = (select employee_id from inserted)

? if(@employee_id > 5 )

? begin

? print '员工号大于的不能添加'

? Rollback Transaction

? end

end;

?

alter trigger employees_insert

? on employees

? instead of insert

as

begin

? declare @employee_id int

? set @employee_id = (select employee_id from inserted)

? if(@employee_id > 5 )

? begin

? print '员工号大于的不能添加'

? end

end;

?

?

?

?

drop trigger employees_delete

create trigger employees_delete

? on employees

? instead of delete

as

begin

? declare @employee_id int??????????????????

? set @employee_id = (select employee_id from deleted)

? if(@employee_id > 2 )

? begin

? print '员工号大于的不能删除'

? end

end;

?

delete employees where employee_id = '3'

?

?

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 1,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 2,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 3,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 4,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 5,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 6,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

select * from employees