sql sever 触发器总结及实验

一、基础知识

Ⅰ 定义
触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。

1. 触发器保存在数据库服务器中
2. 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
3. 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

Ⅱ 分类
按照触发事件的语言分类，可以将触发器分成DML触发器和DDL触发器。

1. DML触发器是指可以被对数据表的insert、update和delete操作所触发的触发器。
2. DDL触发器是指可以被对数据库对象的create、alter和drop操作所触发的触发器。

按照触发器的触发和执行事件的不同，可以分为after类型触发器和instead of类型触发器。

1. after类型触发器又被称为后触发触发器，把触发触发器执行的insert、update或者delet语句所代表的事件称为“触发事件”。after类型触发器即是指“触发事件”成功执行完毕后，“被触发事件”执行的触发器。
2. instead of类型触发器又被称为替换触发器。简单的说，instead of类型的触发器当“触发事件”发生时，系统并不执行“触发事件”的具体操作（比如insert、update或者delete数据），而是直接执行“被触发事件”。

二、sql语句总结

Ⅰ 创建

create trigger <触发器名>
on <表名>
[with encryption] --对创建触发器的文本加密
after|instead of <update|insert|delete>
as
<T-sql语句>

注意：

1. after类型的触发器只能创建在数据表上，而不能创建在视图上。
2. instead of类型的触发器可以定义在表上和视图上。对于每个触发操作（insert、update或delete）只能定义一个instead of类型触发器。
3. 一张数据表可以创建多个因同一触发操作而生成的触发器。
4. inserted表和deleted表
   inserted表和deleted表是系统为每个触发器准备的临时表，存放于内存中。
   当触发器被触发执行时，inserted表和deleted表将记录触发器执行过程中涉及到的每条记录信息，以方便用户使用和查询。inserted表和deleted表中的记录只能查看，不能修改，当触发器执行完毕后，与之相关的临时表也随之删除。inserted表用来存放当触发操作为insert命令时所产生的数据信息。deleted表用来存放当触发操作为delete命令时所产生的数据信息。update作为修改操作，即相当于删除数据再添加数据，因此，当触发操作为update命令时，既生成deleted表又生成inserted表。

Ⅱ 查看与重命名

exec sp_helptrigger ‘<表名>’[,’<类型>’]
exec sp_rename <oldname>,<newname>
exec sp_help ‘<触发器名>’ --查看所有者、创建日期等

Ⅲ 禁用和启用

alter table <表名>
enable|disable trigger <触发器名>

Ⅳ 删除

drop trigger <触发器名> on <表名>

三、实验题目及解答

dz表

ts表

jy表

1、为dz表和ts表设计插入触发器dz_insert和ts_insert，分别在插入记录后提示“借书证号为XXXX的读者记录已被插入”或“总编号为XXXX的图书记录已被插入”，其中XXXX为插入的具体字段值；写一条insert语句，插入记录，对插入触发器进行测试。

--------创建dz的插入触发器----------
create trigger dz_insert
on dz
for insert
as
declare @id_no nvarchar(10)
select @id_no=借书证号 from inserted
print '借书证号为'+@id_no+'的读者记录已被插入'

--------dz插入触发器的测试----------
insert into dz
values('999','信息系','ceshi','男',1999-07-01,'教授','1号楼424')

--------创建ts的插入触发器----------
create trigger ts_insert
on ts
for insert
as
declare @book_no nvarchar(10)
select @book_no=总编号 from inserted
print '总编号为'+@book_no+'的图书记录已被插入'

--------ts插入触发器的测试----------
insert 
into ts
values('999999','ceshi','张三','电子工业出版社','TP',24)

2、为图书表设置更新触发器，根据图书编号来更新书名、作者、出版社、分类号和单价(根据图书编号找到相应记录，然后更新书名、作者等信息)，更新完毕后提示“更新后的总编号为：XX，书名为：XX，作者为：XX，出版社为：XX，分类号为：XX，单价为：XX”(其中XX代表相应字段的值)；输入update语句，例如将图书编号为J007的图书名改为网络安全基础，测试更新触发器。

--------创建ts更新触发器----------
create trigger ts_update1
on ts
for update
as
declare @no nvarchar(10)
declare @book nvarchar(10)
declare @author nvarchar(4)
declare @publisher nvarchar(10)
declare @class nvarchar(2)
declare @price real
select @no=总编号,@book=书名,@author=作者,@publisher=出版单位,@class=分类号,@price=单价 from inserted
print '更新后的总编号为：'+@no+'，书名为：'+@book+'，作者为：'+@author+'，出版社为：'+@publisher+'，分类号为：'+@class+'，单价为：'+cast(@price as varchar)

--------测试ts更新触发器----------
update ts
set 书名='大学英语（一）'
where 总编号='113388'

3、为图书表设置更新触发器，根据总编号来更新书名、作者、出版社、分类号和单价(根据总编号找到相应记录，然后更新书名、作者等信息)，更新完毕后提示“图书表更新成功”；编写测试语句进行测试。

--------创建ts更新触发器2----------
create trigger ts_update2
on ts
for update
as
print('图书表更新成功!')

--------测试ts更新触发器----------
update ts
set 书名='大学英语（二）'
where 总编号='113388'

4、为dz表和ts设置删除触发器，若删除dz记录或ts记录，则jy表中的相关读者记录或书籍的记录也应删除，并提示“dz（或ts）表和jy表相关记录已一并删除” ；编写测试语句进行测试。

--------创建ts删除触发器----------
create trigger dz_delete
on dz
for delete
as
delete
from jy
where 借书证号 in (select 借书证号 from deleted)
print 'dz表和jy表相关记录已一并删除'

--------测试ts删除触发器----------
delete
from dz
where 借书证号='112'

--------创建dz删除触发器----------
create trigger ts_delete
on ts
for delete
as
delete
from jy
where 总编号 in (select 总编号 from deleted)
print 'ts表和jy表相关记录已一并删除'

--------测试dz删除触发器----------
delete
from ts
where 总编号='332211'

5、为ts创建instead of 触发器，当删除某条记录时，显示“对不起，不允许删除操作” ；编写测试语句进行测试。

--------创建ts禁止删除触发器----------
create trigger ts_sorry
on ts
instead of delete
as
print '对不起，不允许删除操作'

--------测试ts删除触发器----------
delete
from ts
where 总编号='112266'

6、使用查看命令查看dz表中已创建的触发器；使用命令查看某各触发器代码。

exec sp_helptrigger 'dz'

exec sp_helptext 'dz_insert'
exec sp_helptext 'dz_delete'

7、修改某触发器的名称。

exec sp_rename dz_insert,dz_insert_2