Oracle 触发器练习

Oracle触发器开发与设计

 

        一、　什么是触发器？
              数据库触发器是一个存储的PL/SQL程序块，它与一个基表联系，当在表上执行特定的数据库维护（插入、删除、更新这三种操作）时，隐含地执行一个PL/SQL程序块。

 

        二、触发器的作用：
              。防止非法的数据库操纵、维护数据库安全
              。对数据库的操作进行审计，存储历史数据
              。完成数据库初始化处理
              。控制数据库的数据完整性
              。进行相关数据的修改
              。完成数据复制
              。自动完成数据库统计计算
              。限制数据库操作的时间、权限等，控制实体的安全性

 
         三、触发器的组成：
             1、触发时间：触发器事件的时间次序（before, afer)[2]

             2、触发事件：什么SQL语句会引起触发器触发(Insert, delete, update)[3]

             3、触发子体：触发器触发时要执行的操作(一个完整的PL/SQL程序)

             4、触发类型：触发器被执行的次数(语句级、行级）[2] //语句级只执行一次，行级会执行多次。

                  [*]一个表上最多可以创建12个不同类型的触发器：3*2*2 = 12

 

          四、创建触发器注意事项：
              1、在触发器中可以调用存储过程、包；在存储过程中不得调用触发器。

              2、在触发器中不得使用commit, rollback, savepoint语句。

              3、在触发器中不得间接调用含有commit, rollback, savepoint的语句的存储过程及函数。

 

           五、创建语句级触发器：
                    语句级触发器： 请参考PowerPoint教程：存储过程1.ppt[Page19]　该触发器在数据库操作时只执行一次。
                 说明：
                       。update中的of是可选项，用于指定语句要修改的列
                       。要创建的触发器已经存在时，使用replace选项

1. //例1：before型触发器：    
2. Create or replace trigger DelEmp   
3.   before delete on emp   
4.   Begin  
5.    if (To_Char(sysdate,'dy') in ('星期六','星期日') or  
6.         To_number(To_Char(sysdate,'hh24'))  not between  8   and 18)   
7.    then dbms_output.put_line('现在是非工作时间,请退出!!!');   
8.    end if;   
9.   End;  

[触发器数据字典]
SQL> select table_owner, table_name,trigger_body from user_triggers where trigger_name='DELEMP';

1. //例2：After型触发器：   
2. Create or replace trigger InsertEmp   
3. after insert on emp     // 如果是before，就会比after的结果少一名。   
4. Declare  
5.  v_empcount number(7);   
6. Begin  
7.  select count(*) into v_empcount from emp;   
8.  dbms_output.put_line('目前员工总数已达到：'|| v_empcount|| '名。');   
9. End;  

 

1. //例3：多个触发条件   
2. Create or replace trigger ChangeEmp   
3. before delete or insert or update on emp   
4. Begin  
5.    if (To_Char(sysdate,'dy') in ('星期六','星期日') or  
6.         To_number(To_Char(sysdate,'hh24'))  not between 8 and 18)   
7.    then dbms_output.put_line('现在是非工作时间,请不要修改数据!!!');   
8.    end if;   
9. End;   
10.   
11. // 更完善的写法：   
12. Create or replace trigger ChangeEmp   
13.   before delete or insert or update  on emp   
14.   Begin  
15.    if (DELETING and (To_Char(sysdate,'dy') in ('星期六','星期日') or  
16.         To_number(To_Char(sysdate,'hh24'))  not between  8   and 18))   
17.    then dbms_output.put_line('现在是非工作时间,不要删除数据！');   
18.   
19.    elsif (UPDATING and (To_Char(sysdate,'dy') in ('星期六','星期日') or  
20.         To_number(To_Char(sysdate,'hh24'))  not between  8 and 18))   
21.    then dbms_output.put_line('现在是非工作时间,不要更新数据！');   
22.   
23.    elsif (INSERTING and (To_Char(sysdate,'dy') in ('星期六','星期日') or  
24.         To_number(To_Char(sysdate,'hh24'))  not between  8   and 18))   
25.    then dbms_output.put_line('现在是非工作时间,不要插入数据！');   
26.   
27.    end if;   
28.   End;   

 

        六、创建行级触发器：
            等级触发器：增加选项for each row, 使触发器在每一行上触发。

             1、创建行级触发器注意事项：
                      (1) 在行级触发器中，在列名前增加old表示该列修改前值，增加new表示该列修改后值。
                      (2) 在PL/SQL中引用时，前边增加冒号。
 

1. [例4: 行级触发器] //必须是对所有的行进行操作才行。   
2. Create or Replace trigger UpdateEmp   
3. Before update on emp    
4. for each row    
5. Begin  
6.  dbms_output.put_line(:old.sal||'--------->'||:new.sal);   
7. End;  

 

1. [例5：保存历史数据，这种使用方法很重要，用来保存关键表的历史数据]   
2. CReate or Replace trigger ChangeEmp   
3. Before update or delete on emp   
4. for each row   
5. Begin  
6.  Insert into oldemp(empno, ename,job,hiredate,sal)   
7.  values(:old.empno,:old.ename,:old.job,sysdate,:old.sal);   
8. End;   
9.   
10. SQL> create table oldemp   
11.  as select empno, ename,job,hiredate,sal from emp where 1>2;   

1. [例6：修改外键]   
2. Create or Replace trigger UpdateDept   
3. after update on dept   
4. for each row   
5. Begin  
6.  update emp   
7.   set emp.deptno = :new.deptno   
8.   where emp.deptno = :old.deptno;   
9. End;  

1. [例7：删除外键、删除相关数据]   
2. Create or Replace trigger DeleteDept   
3. before delete on dept   
4. for each row   
5. Begin  
6.  delete from emp where deptno = :old.empno;   
7. End;  

 

        七、触发器管理
             1、使触发器失效：

1. SQL> alter trigger 触发器名称 disable;  // 失效   
2. SQL> Alter Trigger 触发器名称 enable;  // 生效   
3.   
4. SQL> Alter table 表名 DISABLE all triggers; // 一个表上的所有触发器失效   
5. SQL> Alter table 表名 ENABLE all triggers; // 使一个表上的所有触发器生效   
6.   
7. SQL> Drop Trigger 触发器名;  // 删除触发器;   

 

　触发器是一种特殊类型的存储过程，它不同于存储过程。触发器主要是通过事件进行触发而被执行的，触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件，而存储过程可以通过存储过程名字而被直接调用。当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，SQL Server 就会自动执行触发器所定义的SQL 语句，从而确保对数据的处理必须符合由这些SQL 语句所定义的规则。

　　触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。

　　触发器功能

　　1、 允许/限制对表的修改

　　2、 自动生成派生列，比如自增字段

　　3、 强制数据一致性

　　4、 提供审计和日志记录

　　5、 防止无效的事务处理

　　6、 启用复杂的业务逻辑

　　触发器种类

　　触发器的种类可划分为4种：1.数据操纵语言(DML)触发器、2.替代(INSTEAD OF)触发器、3.数据定义语言(DDL)触发器、4.数据库事件触发器。

　　数据操纵语言(DML)触发器：简称DML触发器，是定义在表上的触发器，创建在表上。由DML事件引发的触发器，编写DML触发器时的两点要素是： 1.确定触发的表，即在其上定义触发器的表。2.确定触发的事件，DML触发器的触发事件有INSERT、UPDATE和DELETE三种;替代触发器，简称INSTEAD　OF触发器，创建在视图上，用来替换对视图进行的删除、插入和修改操作; 数据定义语言(DDL)触发器，简称DDL触发器，定义在模式上，触发事件是数据对象的创建和修改;数据库事件触发器，定义在整个数据库或模式上，触发事件是数据库事件.

　　ORACLE产生数据库触发器的语法为：

　　CREATE [OR REPLACE] TRIGGER 触发器名

　　{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]

　　ON 表名

　　WHEN 触发条件

　　[FOR EACH ROW]

　　DECLARE

　　声明部分

　　BEGIN

　　主体部分

　　END;

　　其中：

　　触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。一个触发器可由多个不同的数据操纵语言操作触发。在触发器中，可用INSERTING、DELETING、UPDATING谓词来区别不同的数据操纵语言操作。这些谓词可以在IF分支条件语句中作为判断条件来使用。

　　触发时间：指明触发器何时执行，该值可取, 触发的时间有BEFORE和AFTER两种，分别表示触发动作发生在DML语句执行之前和语句执行之后。确定触发级别，有语句级触发器和行级触发器两种。语句级触发器表示SQL语句只触发一次触发器，行级触发器表示SQL语句影响的每一行都要触发一次。

　　Before：表示在数据库动作之前触发器执行;在SQL语句的执行过程中，如果存在行级BEFORE触发器，则SQL语句在对每一行操作之前，都要先执行一次行级BEFORE触发器，然后才对行进行操作。如果存在行级AFTER触发器，则SQL语句在对每一行操作之后，都要再执行一次行级AFTER触发器。

　　after：表示在数据库动作之后出发器执行。如果存在语句级AFTER触发器，则在SQL语句执行完毕后，要最后执行一次语句级AFTER触发器。

　　触发事件：指明哪些数据库动作会触发此触发器,指INSERT、DELETE或UPDATE事件，事件可以并行出现，中间用OR连接;

　　insert：数据库插入会触发此触发器;

　　update：数据库修改会触发此触发器;

　　delete：数据库删除会触发此触发器。

　　表 名：数据库触发器所在的表。

　　for each row：表示触发器为行级触发器，省略则为语句级触发器,对表的每一行触发器执行一次。

　　触发器的创建者或具有DROP ANY TIRGGER系统权限的人才能删除触发器。删除触发器的语法如下：

　　DROP TIRGGER 触发器名

　　可以通过命令设置触发器的可用状态，使其暂时关闭或重新打开，即当触发器暂时不用时，可以将其置成无效状态，在使用时重新打开。该命令语法如下：

　　ALTER TRIGGER 触发器名 {DISABLE|ENABLE}

　　其中，DISABLE表示使触发器失效，ENABLE表示使触发器生效。

　　同存储过程类似，触发器可以用SHOW ERRORS 检查编译错误。

　　如果有多个触发器被定义成为相同时间、相同事件触发，且最后定义的触发器是有效的，则最后定义的触发器被触发，其他触发器不执行。触发器体内禁止使用COMMIT、ROLLBACK、SAVEPOINT语句，也禁止直接或间接地调用含有上述语句的存储过程。定义一个触发器时要考虑上述多种情况，并根据具体的需要来决定触发器的种类。

　　触发器的作用

　　触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

　　(1) 强化约束(Enforce restriction)

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

　　(2) 跟踪变化Auditing changes

　　触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。

　　(3) 级联运行(Cascaded operation)。

　　触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作(如删除，更新，插入)而该操作又导致该表上触发器被触发。

　　(4) 存储过程的调用(Stored procedure invocation)。

　　为了响应数据库更新触，发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS( 数据库管理系统)本身之外进行操作。

　　由此可见，触发器可以解决高级形式的业务规则或复杂行为限制以及实现定制记录等一些方面的问题。例如，触发器能够找出某一表在数据修改前后状态发生的差异，并根据这种差异执行一定的处理。此外一个表的同一类型(INSERT、 UPDATE、 DELETE)的多个触发器能够对同一种数据操作采取多种不同的处理。

　　总体而言，触发器性能通常比较低。

      当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。