oracle触发器

第九章 触发器

触发器是有名字的PL/SQL块，它不能够接收参数，也不能够被调用，是针对特定的事件触发执行的

一、触发器的构成
  1.触发事件：引发触发器的执行的事件，如SQL语句，数据库事件或用户事件
    -对某个表或视图的DML操作；
    -在某个用户上执行的DDL操作
    -数据库实例的启动或关闭
    -用户的登陆和注销等等。。。
  2.触发条件：由用户在创建触发器的时候，由用户设定的一个Boolean表达式，触发条件是可选的
  3.触发操作：触发器执行的动作，通常是PL/SQL块
      --限制
        1--代码不能超过32K长度。如果代码超过了32K,就要把代码写在存储过程中，
         --在触发器中调用存储过程
         --call 存储过程(参数列表)
       
        2--触发器只能包含select,insert,update,delete,不能包含DDL，DCL，TCL语句(commit rollback)
         --因为他们会提交，对事务造成影响
       
二、触发器的分类
  1.Dml 触发器：由某个表的DML操作引发的触发器
  2.Ddl 触发器：由某个用户上的DDL操作引发的触发器
  3.系统事件触发器：由数据库事件或用户事件触发的触发器
 
三、 Dml 触发器
  1.触发器的创建语法
    Create [Or Replace] Trigger 触发器的名字
    Before|After  --触发时机，
                  --before ：DML操作前触发；
                  --after：DML操作后触发
                 
    Insert [Or Update [Or Delete]] --触发事件，可以是单个的事件，也可以是多事件的组合
   
    On 表名          --设定表名
   
    [For Each Row]   --行级别的触发器，如果不加是语句级的触发器 
   
                     --触发类型（根本的区别：触发操作的执行次数）
                     --1.语句级触发器：对于一个DML语句，触发操作只执行一次，不管DML操作影响了多少行数据
                     --2.行触发器：对于一个DML语句所影响的每一行都会执行一次触发操作
                    
    [When(条件)]     --触发条件：只有行级的触发器可以设定触发条件
   
    [Declare]        --触发操作
   
    Begin
      --PL/SQL块
    End;
   
  2.语句级的before触发器
   
练习：写一个触发器，禁止用户在非工作日操作emp表
Select to_char(Sysdate,'D') From dual;

Create Or Replace Trigger tr_emp_1
Before Insert Or Update Or Delete
On emp
--语句级不用写 for each row
Begin
 If To_char(Sysdate,'D') In('1','7') Then
    raise_application_error(-20001,'禁止用户在非工作日操作emp表');
 End If;
End;

--测试
Update emp Set sal=sal+100 Where empno=7788;

练习：写一个触发器，禁止用户在非工作时间操作emp表(8:30-下午5:30)
Select to_char(Sysdate,'HH24:MI') From dual;
Select to_date('15.30','fmHH24:MI') From dual;

Create Or Replace Trigger Tr_Emp_2
 Before Insert Or Update Or Delete On Emp
Begin
 If To_Char(Sysdate, 'HH24:MI') Not Between '08:30' And '17:30' Then
  Raise_Application_Error(-20002, '禁止用户在非工作时间操作emp表');
 End If;
End;

--测试
Update emp Set sal=sal+100 Where empno=7788;

   3.语句级的after触发器

练习：写一个触发器，统计emp表insert，update和delete次数
Create Table tongji_emp_dml
(
 ins Number,
 upd Number,
 del Number
);
Insert Into tongji_emp_dml Values(0,0,0);
Select * From tongji_emp_dml;

--条件谓词
  a.inserting:当触发事件是insert语句的时候，值为true，否则为false；
  b.updating：当触发事件是update语句的时候，值为true，否则为false；
  c.deleting: 当触发事件是delete语句的时候，值为true，否则为false；

Create Or Replace Trigger row_trigger_num
After Insert Or Update Or Delete
On emp
Begin
     If inserting Then
        Update tongji_emp_dml Set ins=ins+1;
     Elsif updating Then
        Update tongji_emp_dml Set upd=upd+1;
     Elsif deleting Then
        Update tongji_emp_dml Set del=del+1;
     End If;
End;

--测试
Insert Into emp(empno,ename) Values(1002,'sss');
Update emp Set ename='rrr' Where empno=1001;
Delete From emp Where empno=1001;

   4.行级的before触发器
  
   --行级触发器可以记录数据的变化
   --new关键字(只能使用在行级触发器中)：当前行的DML操作之后的状态
   --old关键字(只能使用在行级触发器中)：当前行在DML操作之前的状态
  
   --相当于 表名%RowType
   --:new.sal
   --old或者new关键字在触发操作中引用的时候前面需要加 :
  
   --           insert   update   delete
   --   new       有       有     没有(null)
   --   old    没有(null)  有      有
  
练习：写一个触发器，确保emp表员工的工资只升不降
Create Or Replace Trigger tr_emp_4
Before Update Of sal --更新这一列
On emp
For Each Row
Begin
    If :New.sal<:Old.sal Then
       raise_application_error(-20003,'emp表员工的工资只升不降');
    End If;
End;

--测试
Select * From emp Where empno=1000;
Update emp Set sal=sal+100 Where empno=7788;
Update emp Set sal=sal-100 Where empno=7788;

练习：写一个触发器，确保emp表员工工资的涨幅不能超过原工资的20%
Create Or Replace Trigger tr_emp_5
Before Update Of sal
On emp
For Each Row
Begin
     If :New.sal>(:Old.sal+:Old.sal*0.2 ) Then
        raise_application_error(-20004,'emp表员工工资的涨幅不能超过原工资的20%');
     End If;
End;

Update emp Set sal=sal+500 Where empno=1000;
Delete From emp Where empno=1000;
Insert Into emp Values(1000,'liwen','M_CLERK',7902,to_date('2011-2-14','YYYY-MM-DD'),800,0,10);

   5.行级别的after触发器

练习：写一个触发器，记录emp表员工工资的变化
Create Table tongji_emp_sal
(
 ename Varchar2(30),
 oldsal Number,
 newsal Number,
 changedate Date
);

Create Or Replace Trigger tr_emp_6
After Update Of sal
On emp
For Each Row
Begin
    Insert Into tongji_emp_sal Values(:old.ename,:old.sal,:new.sal,Sysdate);
End;

--测试
Update emp Set sal=sal+20 Where empno=1000;
Select * From emp Where empno=1000;
Select * From tongji_emp_sal;
Update emp Set sal=sal+20 Where deptno=10;

   6.触发条件：只有行级别的触发器能使用触发条件

练习：写一个触发器，记录emp中职位是SALESMAN的员工工资的变化
Create Or Replace Trigger tr_emp_7
After Update Of sal
On emp
For Each Row
Begin
    If :Old.job='SALESMAN' Then
       Insert Into tongji_emp_sal Values(:old.ename,:old.sal,:new.sal,Sysdate);
    End If;
End;
-----建议使用如下
Create Or Replace Trigger tr_emp_8
After Update Of sal
On emp
For Each Row
When (Old.job='SALESMAN')--触发条件当中不用old或者new 前不用加：
                         --Boolean一定要写在()中
Begin
       Insert Into tongji_emp_sal Values(:old.ename,:old.sal,:new.sal,Sysdate);
End;    

   7.变化中的表
     行级别的处罚操作中，不能够从基表中查询数据。
    
三、DML触发器的应用
   1.控制数据的安全
   2.实现数据的审计
   3.保证数据的完整性
   4.实现参照完整性
 
练习：更新部门操作（参照完整性）
   Update dept Set deptno=10 Where deptno=90;
  
   Create Or Replace Trigger tr_dept_1
   Before Update Of deptno On dept
   For Each Row
   Begin
        Update emp Set deptno=:New.deptno Where deptno=:Old.deptno;
   End;
  
   Select * From dept;
   Select * From emp Order By deptno;
  
练习：确保插入到dept表的数据不会发生主键冲突（参照完整性）
   --创建序列
   Create Sequence seq_dept
   Start With 61
   Increment By 1
   Maxvalue 99
   Cache 5;
   --创建触发器
   Create Or Replace Trigger tr_dept_2
   Before Insert On dept
   For Each Row
   Begin
        Select seq_dept.Nextval
        Into :New.deptno
        From dual;
   End;
   --测试
    Insert Into dept Values(10,'abc','abc');
    Insert Into dept Values(Null,'abc','abc');
   
四、管理触发器
   1.禁用/激活触发器
     Alter Trigger tr_name Disable|Enable;
    
   2.禁用/激活某一个表上的所有的触发器
     Alter Table table_name Disable|Enable All Triggers;
    
   3.删除触发器
     Drop Trigger tr_name;