触发器

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'