SQL Server知识梳理九触发器的使用
一、触发器概述
1、SQL Server提供两种类型的触发器：AFTER触发器和INSTEADOF触发器。
l AFTER触发器作用在表上，晚于约束处理。对于AFTER触发器，如果一个表同时具有约束和触发器，在进行数据操作时，首先进行约束检查，检查成功后再激活触发器。如果检查失败将中止数据操作，并且无法激活触发器。
l INSTEAD OF触发器作用在表或视图上，早于约束处理。INSTEAD OF触发器将替代数据操作语言，执行INSTEAD OF触发器中的代码。
2、触发器可以访问两种动态生成的特殊表：inserted表、deleted表。
二、创建触发器
1、语法
（1）DML
CREATETRIGGER 触发器名 ON 表或视图名
[WITHENCRYPTION]
For|AFTER|INSTEADOF [[INSERT],[UPDATE],[DELETE]]
AS
(主体部分)
（2）DDL
CREATETRIGGER 触发器名
ON ALLSERVER | DATABASE
For|AFTER
(事件)
AS
(主体部分)
附注：
事件：CREATE_TABLE、ALTER_TABLE、DROP_TABLE....
2、实例
（1）DML
①设计一个简单的AFTER INSERT触发器，这个触发器的作用是：在插入一条记录的时候，发出“又添加了一个学生的成绩”的友好提示。
create triggerscore_insert
on 成绩表
after insert
as
begin
print '又添加了一个学生的成绩'
end
②在成绩表上创建一个instead of insert触发器，实现：当向表成绩表插入记录时检查分数的合理性，如果不合理就不进行插入操作，否则允许。
create triggerscore_insert_instead
on 成绩表
instead of insert
as
begin
set nocounton
declare @scoreint
select @score=成绩frominserted
if(@score<0or @score>100)
print '分数不合理'
else
insert into 成绩表(学号,课程编号,成绩)
select 学号,课程编号,成绩frominserted
end
③给score表创建一个after insert触发器，实现：当向成绩表插入记录时，提示‘向成绩表插入了内容为：…的记录’，也就是把插入表中的记录信息显示出来。
CREATE TRIGGER显示_Insert
ON 成绩表
AFTER INSERT
AS
BEGIN
declare
@编号int,
@学号varchar(14),
@课程编号int,
@成绩int
select @编号=编号,@学号=学号,@课程编号=课程编号,@成绩=成绩from inserted
print '插入的记录为：'+CAST(@编号AS varchar(16))+','+@学号+','+CAST(@课程编号AS varchar(16))+','+CAST(@成绩AS varchar(6))
END
④创建一个触发器‘删除课程表记录_tri’：实现当删除课程表中某门课程的记录时，对应于选课表中相关该课程的所有记录也均删除。
create trigger删除课程表记录_tri on 课程表
for delete
as
delete 选课表from 选课表,deleted
where 选课表.课程号=deleted.课程号
⑤创建一个触发器‘授课表记录操作_tri’：当向授课表中插入新纪录时检查该纪录的职工号在教师表中是否存在，同时检查课程号在课程表中是否存在，若有一项不满足条件，则返回错误信息
create trigger授课表记录操作_tri on 授课表
for update
as
begin
declare @职工号 varchar(10),@课程号varchar(10)
if exists(select* from 授课表 where @职工号=职工号)
begin
if exists(select *from 课程号 where @课程号=课程号)
insert into 授课表(职工号,课程号)
values(@职工号,@课程号)
else
print '课程号不存在'
end
else
print '该纪录的职工号不存在'
end
⑥创建触发器‘修改学生表1数据_tri’：实现当修改学生表1的记录时，返回提示信息‘学生表1的数据被修改！！’。
create trigger修改学生表数据_tri on 学生表
for update
as
begin
print '学生表的数据被修改！！'
end
（2）DDL
①建立一个DDL触发器，用于保护
数据库中的数据表不被修改，不被删除。
create trigger禁止对数据表操作
on database
for ALTER_TABLE,DROP_TABLE
as
begin
PRINT '对不起，您不能对数据表进行操作'
ROLLBACK
end
②建立一个DDL触发器，用于保护当前SQL Server服务器里所有数据库不能被删除。
CREATE TRIGGER不允许删除数据库
ON allserver
FOR DROP_DATABASE
AS
begin
PRINT '对不起，您不能删除数据库'
ROLLBACK
end
三、修改触发器