ORACLE PL/SQL 触发器

今天药忘吃喽~ 2024-02-17 16:40 37阅读 0赞

一、触发器的概念：

触发器 是特定事件发生时，自动执行的代码块。类似于存储过程和函数，但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。 触发器也是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，存储器由语句块去调用；

二、触发器的分类：

1.DML触发器： 创建在表上，由DML事件引发

2.instead of触发器： 创建在视图上并且只能在行级上触发，用于替代insert，delete等操作(由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法)

3.DDL触发器： 触发事件时数据库对象的创建和修改

4.数据库事件触发器：定义在数据库或者模式（shema）上，由数据库事件触发

三、触发器的组成：

1.触发事件：引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句(如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象)、数据库系统事件　(如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)。

2.触发时间：即该触发器是在触发事件发生之前(BEFORE)还是之后(AFTER)触发

3.触发操作：触发器触发后要完成的事情

4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。

5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。

6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。(比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次)

四、触发器的语法：

CREATE\[OR REPLACE\]TRIGGER trigger\_name  
\{BEFORE | AFTER \}  
\{ INSERT|DELETE|UPDATE\[OF column \[, column …\]\]\}  
\[OR \{INSERT | DELETE | UPDATE \[OF column \[, column …\]\]\}...\]  
ON\[schema.\]table\_name |\[schema.\]view\_name  
\[REFERENCING \{OLD \[AS\] old | NEW \[AS\] new| PARENT as parent\}\]  
\[FOR EACH ROW \]  
\[WHEN condition\]  
PL/SQL\_BLOCK | CALL procedure\_name;

说明：trigger\_name 是触发器的名称；

BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式;

FOR EACH ROW选项说明触发器为行触发器;当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。

REFERENCING 子句说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW;

触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。

WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL 函数;

WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中，不能用在INSTEAD OF 行触发器和其它类型的触发器中。

五、触发器的限制

1.   CREATE TRIGGER语句文本的字符长度不能超过32KB；
2.  触发器体内的SELECT 语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT 语句。
3.  在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE)，不能使用DDL语句(CREATE、ALTER、DROP)。
4.  一个表上的触发器越多，对于表的DML操作性能影响越大。  
    
5.  触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句；
6.  由触发器所调用的过程或函数也不能使用数据库事务控制语句；
7.  触发器中不能使用LONG, LONG RAW 类型；
8.  触发器内可以参照LOB 类型列的列值，但不能通过 :NEW 修改LOB列中的数据

六、触发的谓词

在触发器中包含多个触发事件（INSERT、UPDATE、DELETE）的组合时，为了分别针对不同的事件进行不同的处理，需要使用ORACLE提供的如下条件谓词。

1）**INSERTING：**当触发事件是INSERT时，取值为TRUE，否则为FALSE。

2）**UPDATING \[（column\_1,column\_2,…,column\_x）\]：**当触发事件是UPDATE时，如果修改了column\_x列，则取值为TRUE，否则为FALSE。其中column\_x是可选的。

3）**DELETING：**当触发事件是DELETE时，则取值为TRUE，否则为FALSE。

当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值.

![Center][]

七、触发器如何使用

在SCOTT用户下的EMPS表，DEPTS表来操作数据.

（一）、DML触发器
      案例1：删除员工表一条信息，将删除的记录添加的备份表中。 
    create or replace trigger tr_DelEmps
    before delete  --指定触发的时间，事件
    on emps     --指定触发的表(对象)
    for each row  --行级触发
    begin
       --触发目的 (删除记录写到职工表删除日志表中去。)
        insert into logEmps values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
    end;
     触发事件：  delete from emps where empno=1111;

案例2：限制（周六，周日）是不能对员工信息进行修改或删除。
    create or replace trigger tr_modEmps
    before insert or delete or update
    on emps
    begin
       --判断
        if(to_char(sysdate,'DAY') in ('星期六','星期日') and to_char(sysdate,'HH24:mi') between '08:30' and '17:30') then
            RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改emps表');
        end if;
    end;

触发事件：   delete from emps where empno=7900;
                   或 update emps set sal=2000 where empno=7788;

案例3：限定员工部门号进行修改或删除。

create or replace trigger tr_delDepts
 before update of sal,comm or delete
 on emps
 for each row
 when (old.deptno=50) --触发条件
 begin
 case 
 when updating('sal') then
 if(:new.sal<:old.sal) then
 RAISE_APPLICATION_ERROR(-20001, '部门50的人员的工资不能降');
 end if; 
 when updating('comm') then
 if(:new.comm<:old.comm) then
 RAISE_APPLICATION_ERROR(-20001, '部门50的人员的津贴不能降');
 end if;
 when deleting then
 RAISE_APPLICATION_ERROR(-20001, '部门50的人员不能删除!');
 end case;
 end;
 触发事件： 
 	update emps set sal=2000 where empno=7902;
 	update emps set comm=2 where empno=7902;
 	delete from emps where empno=7902;

案例4：级联更新数据,也就是更新主表DEPTS表的deptno,随之子表EMPS表的deptno也相应更新！
    create or replace trigger tr_update_depts_emps
     after update of deptno
     on depts
     for each row
     begin
         --更新子表
         update emps set deptno=:new.deptno where deptno=:old.deptno;
     end;
     after update of deptno
     on depts
     for each row
     begin
         --更新子表
         update emps set deptno=:new.deptno where deptno=:old.deptno;
     end;
     触发事件：  
          update depts set deptno=1 where deptno=10;

案例5：在触发的时候调用存储过程,对DEPTS表操作，添加的MYDEPTS表中
    --存储过程
    create or replace procedure proc_depts(v_deptno depts.deptno%type)
    as
    begin
      insert into mydepts(deptno,dname) values(v_deptno,'IT');
    end;
    --触发器
    create or replace trigger tr_procDepts
    before insert or update or delete
    on depts
    for each row
    begin
      --调用存储过程
       proc_depts(:old.deptno);
    end;
    触发事件：  
          update depts set dname='AA' where deptno=10;

（二）替代触发器
    案例1：创建视图查询每一个部门的人数以及工资总和，并根据条件删除视图中的信息。
    CREATE OR REPLACE VIEW emp_view 
    AS 
    SELECT deptno, count(*) counts, sum(sal) total FROM emps GROUP BY deptno;
    --查看视图
    select * from emp_view;
    --此视图中直接删除是非法？ 如何来解决这个问题？

delete from emp_view where deptno=50; 替代触发器，它是专门为进行视图操作的一种处理方法：如下：
    create or replace trigger tr_delView
    INSTEAD  of delete   --替代触发器
    on emp_view     --触发的对象 （视图）
    for each row    --行级触发(必须写)
    begin
        --触发的目的
        delete from emps where deptno=:old.deptno;
    end;
    触发事件：  
      delete from emp_view where deptno=50;

案例2：创建视图查询员工的编号，姓名，职位，雇佣日期，工资，部门编号，部门名称信息，并向视图中添加相应的信息
 --创建视图
 create or replace view vw_Two
 as
 select empno, ename,job,hiredate,sal,depts.dname ,depts.deptno
 from emps,depts where emps.deptno =depts.deptno;
 --创建触发器
 create or replace trigger tr_two
 instead of insert --替代触发器
 on vw_Two
 for each row
 declare
 --声明变量保持个数
 v_num number;
 begin
 --主表
 select count(1) into v_num from depts where deptno=:new.deptno;
 if(v_num=0) then 
 insert into depts(deptno,dname) values(:new.deptno,:new.dname);
 end if; 
 --字表
 select count(1) into v_num from emps where empno=:new.empno;
 if(v_num=0) then 
 insert into emps(empno,ename,job,hiredate,sal,deptno) values(:new.empno,:new.ename,:new.job,:new.hiredate,:new.sal,:new.deptno);
 end if;
 end;

触发事件：   insert into vw_Two values('1111','BOY','CLERK','05-12月-2015',1000,'IT',1);

(三）、DDL触发器
     ORACLE10G提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE 、ALTER及DROP 等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。
     创建系统触发器的语法如下： 
    CREATE OR REPLACE TRIGGER [sachema.]trigger_name
    {BEFORE|AFTER} 
    {ddl_event_list | database_event_list}
    ON { DATABASE | [schema.]SCHEMA }
    [WHEN condition]
    PL/SQL_block | CALL procedure_name;

其中: ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；        database_event_list：一个或多个数据库事件，事件间用 OR 分开；--创建表
 CREATE TABLE log_event
 (user_name VARCHAR2(10),
 address VARCHAR2(20), 
 logon_date timestamp,
 logoff_date timestamp
 ); 
 select * from log_event;--创建登录触发器
 CREATE OR REPLACE TRIGGER tr_logon
 AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO log_event (user_name, address, logon_date)
 VALUES (ora_login_user, ora_client_ip_address, systimestamp);
 END tr_logon;
 --创建退出触发器
 CREATE OR REPLACE TRIGGER tr_logoff
 BEFORE LOGOFF ON DATABASE
 BEGIN
 INSERT INTO log_event (user_name, address, logoff_date)
 VALUES (ora_login_user, ora_client_ip_address, systimestamp);
 END tr_logoff;

触发事件：SQL> conn wy/wyaaa;

八、删除触发器

语法：DROPTRIGGER trigger\_name;

drop trigger tr\_logon;

九、如何启用和禁用触发器？

有效状态(ENABLE)：当触发事件发生时，处于有效状态的数据库触发器TRIGGER 将被触发。

无效状态(DISABLE)：当触发事件发生时，处于无效状态的数据库触发器TRIGGER 将不会被触发，此时就跟没有这个数据库触发器(TRIGGER) 一样。

语法：ALTER TIGGER trigger\_name \[DISABLE | ENABLE \];

alter trigger tr\_logondisable; --启用

alter trigger tr\_logon enable; --禁用

ALTER TRIGGER语句一次只能改变一个触发器的状态，而ALTER TABLE语句则一次能够改变与指定表相关的所有触发器的使用状态

语法：ALTERTABLE\[schema.\]table\_name \{ENABLE|DISABLE\} ALL TRIGGERS;