1.基本概念
定义:触发器(Trigger)是一种特殊的存储过程,它不能被显式地调用,而是在往表或视图中插入记录、更新记录或者删除记录时被自动地激活。 所以触发器可以用来实现对表实施复杂的完整性约束。常见的触发器有三种:分别应用于Insert , Update , Delete 事件。触发器分为DML触发器和 DLL触发器,另外还有CLR触发器。触发器基于一个表创建,但是可以针对多个表进行操作。主要 1.强化约束2.跟踪变化3.级联运行4.调用存储过程。
执行环境:可以看作是创建在内存中、在语句执行过程中保存语句进行的空间。每当调用触发器时,就创建一个触发器的执行环境。如果调用多个触发器,则会分别为每个触发器创建执行环境。然而,在任何时候,一个会话中只有唯一的一个执行环境是活动的,另外,一个表的触发器可能引起第二个表的触发器激活。
一个触发器执行环境包含了触发器正确的执行所必须的信息,这些信息包括有关触发器本身的细节和触发器所定义的表,即目标表,另外环境包括一个或两个迁移表(虚表),存在高速缓存中。跟新数据,创建迁移deleted、inserted;插入数据,创建inserted;删除数据deleted。
2.DML触发器
create trigger trigger_name
on{table/view}
{for/after/instead of}
{delete/insert/update}
as
sql_statement
go
注:当触发器执行时deleted、inserted处于激活状态,是可以直接在 sql_statement调用。
- AFTER 只有执行了操作,才会被触发,只能定义在表上,可以定义多个。
- INSTEAD OF 就仅执行他本身,可定义在表上,也可以定义在视图上,同一操作只能定义一个
INSERT OF触发器:用 INSERT OF触发器可以指定执行触发器而不是执行触发SQL语句,从而屏蔽原来的SQL语句,转向执行触发器内部的SQL语句。对于每一个触发动作,每一个表或视图只能有一个INSERT OF触发器。对于简单视图,可以直接执行INSERT,UPDATE和DELETE操作
但是对于复杂视图,不允许直接执行INSERT,UPDATE和DELETE操作。
为了在具有以上情况的复杂视图上执行DML操作需要征用触发器来完成。
INSTEAD OF触发器可以实现更新视图时多个数据表一起更新的问题:
CREATE [OR REPLACE] TRIGGER 触发器名称
INSTEAD OF [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] ON 视图名称
[FOR EACH ROW]
[WHEN 触发条件]
[DECLARE]
[程序声明部分 ;]
BEGIN
程序代码部分 ;
END [触发器名称] ;
替代触发器创建时不需要使用BEFORE或者AFTER,而将其替换为INSTEAD OF,同时操作的对象也有表替换为视图。
create or replace trigger view20emp_trigger
on v_emp20
instead of INSERT OR UPDATE OR DELETE
for each row
declare
v_empCount NUMBER;
v_deptCount NUMBER;
BEGIN
IF inserting THEN
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:NEW.empno;
SELECT COUNT(deptno) INTO v_deptCount FROM dept WHERE deptno=:new.deptno;
IF v_empCount=0 THEN
INSERT INTO emp(empno,ename,job,sal,deptno) VALUES(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);
END IF;
IF v_deptCount=0 THEN
INSERT INTO dept(deptno,dname,loc)VALUES(:new.deptno,:new.dname,:new.loc);
END IF;
ELSIF updating THEN
UPDATE emp SET ename=:new.ename,job=:new.job,sal=:new.sal WHERE empno=:NEW.empno;
UPDATE dept SET dname=:new.dname,loc=:new.loc WHERE deptno=:new.deptno;
ELSIF deleting THEN
SELECT COUNT(empno) INTO v_empCount FROM emp WHERE empno=:old.empno;
IF v_empCount>0 THEN
DELETE FROM emp WHERE empno=:old.empno;
END IF;
ELSE
NULL;
END IF;
end view20emp_trigger;
INSERT INTO v_emp20(empno,ename,job,sal,deptno,dname,loc)
VALUES(7777,'张三丰','CLERK',800,77,'活动部','深圳');
SELECT * FROM v_emp20;
UPDATE v_emp20 SET ename='任我行',sal=2000,dname='魔教' WHERE empno=7777;
COMMIT;
SELECT * FROM v_emp20;
DELETE FROM v_emp20 WHERE empno=7777;
COMMIT;
SELECT * FROM v_emp20;SELECT * FROM emp;
3.DDL触发器
一、创建数据库作用域DDL触发器
create trigger tri_name
on database
for drop_table,delete_table
as
begin
print '不能删除'
rollback transaction
end
二、创建服务器作用域DDL触发器
create trigger tri_name
on all server
for create_database,alter_database
as
print '不能创建'
rollback transaction
go
4.嵌套触发器
定义:如果一个触发器在执行操作时引发另一个触发器,而这个触发器又接着引发下一个触发器,那么这些触发器就是嵌套触发器。同一个触发事务中,一个嵌套触发器不能被触发两次。
学生信息表触发器:
create trigger tri_su
on 学生信息
after delete
as
delete from 学生信息 where 学号='112'
go
成绩信息表触发器:
create trigger tri_sq
on 成绩信息
after delete
as
delete from 学生信息 where 学号='111'
go
执行语句:
delete from 成绩信息 where 学号='001'
禁用嵌套语句:
exec sp_configure 'nested triggers',0
启用嵌套语句:
exec sp_configure 'nested triggers',1
5.递归触发器
定义:递归触发器包括两种:直接递归和间接递归。直接递归:触发器被触发并执行一个操作,而该操作又使得同一个触发器再次被触发。间接递归:触发器被触发并执行一个操作,而该操作又使另一表中的某一个触发器被触发;第二个触发器使得原始表得以更新,从而再次触发第一个触发器。
默认情况下递归触发器选项禁用,但是可以使用alter database语句来启动它。触发器最多只能递归16层,换句话说,如果第16层触发器激活了17层触发器,则所有数据将被擦除。
6.修改触发器
定义:第一种是删除以前的触发器再新建一个同名的触发器;第二种是直接修改现有的触发的定义,可以使用alter trigger。
alter trigger tri_sq
on 成绩信息
after delete
as
--------
go
alter trigger tri_name
on database
for drop_table,delete_table
as
begin
---------
end
7.禁用触发器
定义:对于暂时不需要的触发器,可以将其禁用。触发器禁用后,它将仍然作为对象存储在当前数据库中,但是不能被触发。
disable trigger trig_班级 on 学生信息
alter table 学生信息
disable trigger trig_班级
8.启用触发器
定义:对于已经被禁用的触发器,需要重新启动。
enable trigger trig_班级 on 学生信息
alter table 学生信息
enable trigger trig_班级
9.删除触发器
定义:当不再需要某一触发器时,可以将其删除。删除触发器时,触发器所在表中的数据不会改变。当某一个表被删除时,该表上的所有触发器也自动被删除。
drop trigger trig_班级 on 学生信息
10.查询触发器
查询所有触发器: SELECT * FROM Sysobjects WHERE xtype = 'TR'
查询触发器代码: exec sp_helptext 'name'