触发器是一种特殊类型的存储过程,它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。
什么是触发器?
触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是:触发器是当对某一个表进行操作。诸如:update、insert、delete这些操作的时候,系统会自动调用执行性该表上对应的触发器。SqlServer2005中触发器可以分为两类:DML触发器和DDL触发器,其中DDL触发器它们会影响多种数据定义语言语句而激发,这些语句有create、alter、drop语句。
DML触发器分为:
1、after触发器(之后触发)
a、insert触发器
b、update触发器
c、delete触发器
2、instead of 触发器 (之前触发)
其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发,且只能定义在表上。而instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。既可以在表上定义instead of触发器,也可以在视图上定义。
触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)。这两张是逻辑表也是虚表。有系统在内存中创建者两张表,不会存储在数据库中。而且两张表的都是只读的,只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。Inserted表的数据是插入或是修改后的数据,而deleted表的数据是更新前的或是删除的数据。
对表的操作 |
Inserted逻辑表 |
Deleted逻辑表 |
增加记录(insert) |
存放增加的记录 |
无 |
删除记录(delete) |
无 |
存放被删除的记录 |
修改记录(update) |
存放更新后的记录 |
存放更新前的记录 |
update数据的时候就是先删除表记录,然后增加一跳记录。这样在inserted和deleted表就都有update后的数据记录了。
注意的是:触发器本身就是一个事务,所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚,撤销操作。
创建触发器
语法:
tgr_name table_name encrypion –加密触发器 ... Transact-SQL
--创建insert插入类型触发器 (object_id(, ) ) tgr_classes_insert tgr_classes_insert classes insert --插入触发
# 创建insert类型触发器
1 --定义变量 2 @id , @name (20), @temp ; 3 --在inserted表中查询已经插入记录信息 4 @id = id, @name = name inserted; 5 @name = @name + (, @id); 6 @temp = @id / 2; 7 insert student (@name, 18 + @id, @temp, @id); 8 ; 9 10 --插入数据 11 insert classes (, getDate()); 12 --查询数据 13 * classes; 14 * student id;
insert触发器,会在inserted表中添加一条刚插入的记录。
# 创建delete类型触发器
--delete删除类型触发器 (object_id(, ) ) tgr_classes_delete tgr_classes_delete classes --删除触发 ; (object_id(, ) ) --存在classesBackup,直接插入数据 insert classesBackup name, createDate deleted; --不存在classesBackup创建再插入 * classesBackup deleted; ; -- nocount ; classes name = ; --查询数据 * classes; * classesBackup;
# 创建update类型触发器
--update更新类型触发器 (object_id(, ) ) tgr_classes_update tgr_classes_update classes @oldName (20), @newName (20); --更新前的数据 @oldName = name deleted; ( ( * student name + @oldName + )) --更新后的数据 @newName = name inserted; student name = replace(name, @oldName, @newName) name + @oldName + ; ; ; --查询数据 * student id; * classes; classes name = name = ;
update触发器会在更新数据后,将更新前的数据保存在deleted表中,更新后的数据保存在inserted表中。
# update更新列级触发器
1 (object_id(, ) ) 2 tgr_classes_update_column 3 4 tgr_classes_update_column 5 classes 6 7 8 --列级触发器:是否更新了班级创建时间 9 ((createDate)) 10 11 (, 16, 11); 12 ; 13 14 15 --测试 16 * student id; 17 * classes; 18 classes createDate = getDate() id = 3; 19 classes name = id = 7;
更新列级触发器可以用update是否判断更新列记录;
# instead of类型触发器
nstead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身的内容。
创建语法
tgr_name table_name encryption instead ... T-SQL
# 创建instead of触发器
(object_id(, ) ) tgr_classes_inteadOf tgr_classes_inteadOf classes instead @id , @name (20); --查询被删除的信息,病赋值 @id = id, @name = name deleted; + (, @id) + + @name; --先删除student的信息 student cid = @id; --再删除classes的信息 classes id = @id; + (, @id) + + @name + ; --test * student id; * classes; classes id = 7;
# 显示自定义消息raiserror
(object_id(, ) ) tgr_message tgr_message student insert, (, 16, 10); --test insert student (, 22, 1, 7); student sex = 0 name = ; * student id;
# 修改触发器
tgr_message student (, 16, 10); --test student name = ;
# 启用、禁用触发器
--禁用触发器 disable tgr_message student; --启用触发器 enable tgr_message student;
# 查询创建的触发器信息
--查询已存在的触发器 * sys.triggers; * sys.objects type = ; --查看触发器触发事件 te.* sys.trigger_events te sys.triggers t t.object_id = te.object_id t.parent_class = 0 t.name = ; --查看创建触发器语句 sp_helptext ;
# 示例,验证插入数据
((object_id(, ) )) tgr_valid_data tgr_valid_data student insert @age , @name (20); @name = s.name, @age = s.age inserted s; (@age < 18) (, 16, 1); ; --test insert student (, 2, 0, 7); insert student (, 22, 0, 7); * student id;
# 示例,操作日志
(object_id(, ) ) log log( id (1, 1) , (20), createDate datetime getDate() ) ( ( * sys.objects name = )) tgr_student_log tgr_student_log student insert, , (( ( 1 inserted)) ( ( 1 deleted))) insert log() (); ( ( 1 inserted) ( 1 deleted)) insert log() (); ( ( 1 inserted) ( 1 deleted)) insert log() (); --test insert student (, 22, 1, 7); student sex = 0 name = ; student name = ; * log; * student id;