在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
