触发器

触发器
●触发器的概念及分类
●创建触发器
●触发器的工作原理
●修改和删除触发器
●查看触发器
●触发器与事务
一、触发器的概念及分类
触发器是一种特殊类型的存储过程。

两种技术比较：
存储过程是通过存储过程名字被直接调用的。

触发器主要是通过事件（如对表的insert、update、delete操作等）进行触发而被执行的。

触发器与表紧密相关，每条sql语句执行一次触发器。

在对表执行 UPDATE、INSERT或DELETE 语句时，触发器会自动触发执行，以防止对数据进行不正确的修改。

1．触发器的种类
·INSERT触发器
·UPDATE触发器
·DELETE触发器
2．触发器的主要作用
·能实现数据的完整性与一致性。

·增强约束。

触发器能够实现比CHECK语句更为复杂的约束。

·跟踪、监督数据库。

触发器可以检测到数据库内的所有操作，杜绝所有的未经许可的更新操作。

·级联触发。

触发器可以检测到数据库内的操作，并自动地级联触发影响整个数据库相关表的触发器。

·存储过程的调用。

触发器可以调用一个或多个存储过程。

二、创建触发器
1．使用SQL语句创建触发器
语法：Create Trigger trigger_name
On table
For {[delete ] [,] [insert] [,] [update] }
As
[ if update (column)
[ {and | or} update (column )]
[… n]
]
Sql_statement [… n]
[return ]
[例1]：修改学生年龄时，打印语句“多少个记录被更新”。

Create trigger trig_sample
On 学生表--定义在哪张表上
For update
As
Print str(@@rowcount)+’个记录被更新!’
Declare @counter varchar(30)
Select @counter=str(@@rowcount)+’个记录被更新!’
Print @counter
Return
------------------------------------------
Update 学生表
SET 年龄=年龄+1
[例2]：向学生表中插入一条记录时，打印“学生表中有多少条记录！”。

create trigger trig_insert
on 学生表
for insert --什么时候触发
AS
DECLARE @I INT --触发后做什么
SET @I=(SELECT COUNT(*) FROM 学生表)
PRINT '学生表中有' + STR(@I,2) + '条记录!'
return
----------------------------------------------
insert 学生表(学号,姓名)
V ALUES('609013101','张里')
[例3]：从学生表中删除一条记录时，输出学生的人数。

create trigger trig_delete
on 学生表
for DELETE --什么时候触发
AS
select count(*) from 学生表--触发后做什么
return
------------------------------
delete 学生表
WHERE 学号=’ 609013101’
2．使用Enterprise Manager创建触发器
选中表——右击所有任务——管理触发器
3．创建触发器的注意事项
CREA TE TRIGGER必须是批处理的第一个语句。

表的所有者才能创建。

触发器的命名必须符合命名规则。

P51
触发器只能创建在当前数据库，但可参照其它数据库的对象。

不能在视图上创建触发器，但可参照。

（也不能在视图上创建索引）
一个触发器只能对应一张表。

创建触发器时必须指定在哪一个表上定义的。

多个修改语句可触发同一个触发器。

如insert和update可同时激活一个触发器。

三、触发器的工作原理
触发器的两个特殊表：
·插入表(inserted)
·删除表(deleted)
注：
* 两个表是逻辑表，且由sql server管理
* 这两个表存储在内存中，用户不能对其直接修改
* 与触发器作用的表结构相同
* 动态驻留在内存中，当触发器完成时，这两个表自动删除
* 主要保存了因用户操作而被影响到的原数据值或新数据值
* 只读的，用户不能向这两个表写入内容，但可引用表中数据
1．插入表[inserted]表的功能
若某个表定义了插入类型的触发器，则所有对该表的插入操作，除了向该表插入记录外，还要将一个副本存放到插入表中，既插入表保存了一份向原表插入的记录内容。

2．删除表[deleted]表的功能
如果某个表定义了删除类型的触发器，若对该表执行了删除操作，则所有被删除的记录都存放到删除表中，这样做的目的是：一旦触发器遇到了强迫它中止的语句被执行时，那些被删除的记录可以从删除表中得以恢复。

[例4]：演示inserted和deleted表的内容示例。

Create trigger ins_del_sample
On 学生表
For insert, update,delete
AS
Print 'inserted表'
Select 学号,姓名,性别,年龄,所在院系
From inserted
Print 'deleted表'
Select 学号,姓名,性别,年龄,所在院系
From deleted
Rollback transaction
Return
------------------------
Update 学生表
Set 年龄=年龄+1
Where 性别='女'
3．UPDA TE操作的实质
更新操作是一个复合操作。

对一个定义了更新类型的触发器的表，删除表(deleted)中存放了旧值，插入表（inserted）中存放了新值。

4．[@@rowcount]表的功能
当执行插入、删除和更新操作时，触发器将自动执行。

由于一个SQL语句只能激活触发器一次，而一个SQL语句的执行可能影响多条记录，所以使用全局变量@@rowcount统计一个SQL语句执行后所影响的记录数。

四、修改和删除触发器
1．修改触发器
语法：alter trigger trigger_name
On table
For insert,delete,update
As
语句集合
[例5]：将[例4]修改为update类型的触发器。

Alter trigger ins_del_sample
On 学生表
For update
AS
Print 'inserted表'
Select 学号,姓名,性别,年龄,所在院系
From inserted
Print 'deleted表'
Select 学号,姓名,性别,年龄,所在院系
From deleted
Rollback transaction
Return
2．删除触发器
语法：drop trigger trigger_name
五、查看触发器
1．使用企业管理器显示触发器
选中表——右击所有任务——管理触发器
2．使用系统存储过程查看触发器
1）查看表中的触发器信息
Sp_helptrigger <表名>
2）查看触发器的定义
Sp_helptext <触发器名>
3）查看一个表中的各种依赖关系
Sp_depends <表名>
4) 查看一个触发器所依赖的表
Sp_depends<触发器名>
六、触发器与事务
1．事务与加锁
为保证数据的完整性，SQL Server在一个事务期间，凡是涉及到修改(update)的页面（page）都加了排它锁（X锁）。

当SQL Server执行查询时，修改的表被加锁，一直锁到遇到了一个COMMIT TRANSACTION或者ROLLBACK TRANSACTION 语句为止，这时全局变量@@trancount 的值为0。

2．全局变量@@trancount
当进入一个触发器时，@@trancount的值增1。

3．触发器与事务
在一个数据库应用程序中，利用触发器主要是进行复杂的检验的。

* 若触发器能够判断数据修改语句是无效的，则它就能回滚事务
* 要实现回滚事务，可在触发器内执行rollback transaction 语句
使用rollback transaction 语句，需注意：
由触发器所执行的所有sql都被回滚
触发器将继续执行rollback transaction语句之后的其余语句，若有修改数据的update 语句，修改操作将不会回滚。

批处理中激发触发器以后的sql语句将不再被执行。