oracle trigger (触发器)

Sql代码 

--触发器trigger 

--1.DML触发器  DML statements (DELETE, INSERT, UPDATE) 

/* 

    对于一条dml sql，可能作用与多行，也可能只有一行。语句触发器对每条触发sql，触发器只执行一次；行级触发器是每作用一行就触发一次 

    触发器。   

    DML触发器定义： 

    CREATE [OR REPLACE] TRIGGER trigger_name 

    {BEFORE |AFTER} --触发时机 ，dml sql语句前或后 

    {INSERT | UPDATE | DELETE]--触发事件，增，删，改或3者的任意组合 

    ON table_name --说作用的表，一个触发器只能作用与一个表，一个表可以有多个触发器，但是触发器越多，对dml效率有影响 

    [REFERENCIING OLD AS old NEW AS new] --更新数据，对新旧数据引用变量的设置，使用较少 

    [FOR EACH ROW] --是否指定为行级触发器 

    [WHEN ...]--指定满足特定条件时，触发器body才会执行 

    [DECLARE...]--声明块 

    BEGIN 

      --可执行块 

      --这里不要有针对上面on的表的DML SQL，这样会造成递归触发，无限循环下去      

     ...executable statements... 

    END [trigger_name]; 

*/ 

--语句触发器statement-level trigger 在表上针对某种DML操作建立了语句触发器 目标是整个表 

CREATE OR REPLACE TRIGGER emp_t_1 

BEFORE INSERT OR UPDATE OR DELETE ON EMP 

BEGIN 

    CASE--判断什么类型的触发器 

       WHEN INSERTING THEN 

           dbms_output.put_line('emp_t_1 insert triggerd'); 

       WHEN UPDATING THEN 

           dbms_output.put_line('emp_t_1 update triggerd'); 

       WHEN DELETING THEN 

           dbms_output.put_line('emp_t_1 delete triggerd'); 

    END CASE; 

END; 

--执行下面的update语句，上面的触发器会被处罚 

update emp  set sal = sal*1 where empno=7788; 

--after 语句触发器 

/* 

 exp：统计一个表的DML操作次数，DML操作发生后，after触发器将次数+1 

*/ 

--创建统计表 

CREATE TABLE count_dml( 

    id int,table_name varchar2(30),nums int,dt date 

); 

CREATE OR REPLACE TRIGGER emp_t_2 

AFTER INSERT OR UPDATE OR DELETE ON EMP 

DECLARE 

    v_count int;--声明一个记录上次的变量 

BEGIN 

    select nums into v_count from count_dml where lower(table_name)='emp';--会有NO_DATA_FOUND异常 

    IF v_count = 0 THEN--如果这个表中还没有这个表所对应的记录，就新添加一个 

       insert into count_dml values(1,'emp',0,sysdate); 

    END IF; 

    update count_dml set nums=(v_count+1) where lower(table_name)='emp'; 

    dbms_output.put_line(v_count+1); 

END; 

update emp  set sal = sal*1 where empno=7788; 

commit; 

--行级触发器,创建语句中加入FOR EACH ROW 

CREATE OR REPLACE TRIGGER emp_t_3 

BEFORE UPDATE ON emp FOR EACH ROW --for each row 定义为行级触发器，dml sql语句有多少行受影响该触发器就执行多少次 

BEGIN 

   /* 

       在行级触发器块中 可以用:new.columName 引用新数据的列值 

       :old.columName 引用旧数据的列值 

       对于不同类型的触发器new和old可能代替不同的内容 

       insert：只有new，没有old .old is null 

       update：old和new就都有了 

       delete：只有old 没有new 

   */ 

   dbms_output.put_line(:new.sal||'--'||:old.sal); 

END; 

--执行下面的sql，上面定义的触发器会触发3次 

update emp set sal = sal*1.1 where emp.deptno=10; --3 rows affects 

--符合一定条件才执行触发器语句 ，使用WHEN 语句限定 

CREATE OR REPLACE TRIGGER emp_t_4 

BEFORE UPDATE ON emp 

-- old和new是引用新旧数据的默认值，这里可以明确指定 

REFERENCING OLD AS emp_old NEW AS emp_new  

FOR EACH ROW  

--WHEN 语句限制只有empno=7788，的才执行该触发器，在when语句中引用列值时 前面不用加':'; 

WHEN (emp_new.empno=7788) 

BEGIN 

    dbms_output.put_line(:emp_new.ename||'--'||:emp_old.empno); 

END; 

--触发触发器，只有7788的执行上面的语句 

update emp set sal = sal*1.1 where emp.deptno=20; 

/**new和old可以使用于before和after触发器。before触发器可以修改new中的列值，但是在after中不行， 

因为trigger在中的sql已经执行完毕，生效了。如果before中修改了new中的列值，在after中是可以看到的。 

语句触发器 （statement-level trigger）和 行级触发器（row-level trigger）的执行顺序： 

当然顺序整体上是先BEFORE类型的触发器执行，后AFTER类型的执行。 

如果一个表中每种触发器都出现 执行顺序 

BEFORE 语句触发器 

    BEFORE 行级触发器 

    AFTER 行级触发器 

    ...多个行受影响，行级触发器执行多次 

AFTER 语句触发器 

*/ 

--触发器内代码尽量要简单，如果比较复杂的话可以将复杂 的语句存放到过程中，触发器只调用过程 

--2 DDL触发器  DDL statements (CREATE, ALTER, DROP) 

/* 

    DDL触发器所作用的对象就不是某个具体的表了，而是针对某个SCHEMA|DATABASE 

    DDL触发器目的主要是为了记录DDL操作， 

    当然也可在触发器中生成异常，事务回滚，DDL操作失败 

     oracle 数据库系统事件属性函数: 

     ora_client_ip_address:客户端ip地址 

     ora_database_name:当前数据库名 

     ora_dict_obj_name:DDL操作所对应的数据库对象名 

     ora_dict_obj_owner:DDL操作对象的所有者 

     ora_dict_obj_type:DDL操作所对应的数据库对象的类型 

     ora_login_user:登录用户名 

     ora_sysevent:触发器的系统事件名称 

     ... 

     用这些属性可以描述DDL操作 

*/ 

--创建记录ddl操作的table 

CREATE TABLE ddl_records( 

   event varchar2(30), 

   username varchar2(30), 

   owner varchar2(30), 

   objname varchar2(20), 

   objtype varchar2(10), 

   d_date date 

) 

CREATE OR REPLACE TRIGGER ddl_t 

AFTER DDL ON SCHEMA --DLL也可以是CREATE,DROP,ALTER的任意组合，作用对象可以是当前SCHEMA ,也可以是DATABASE 

BEGIN 

     insert into ddl_records values( 

            ora_sysevent,ora_login_user,ora_dict_obj_owner, 

            ora_dict_obj_name,ora_dict_obj_type,sysdate); 

END; 

--执行一个ddl操作 

create table test_ddl_triger(id int); 

--ddl_records表中插入一条数据  

/* 

    event:create,username:scott,owner:scott 

    objname:test_ddl_triger,objtype:table 

*/ 

drop table test_ddl_triger;--又插入一条数据 

commit; 

--3. 系统事件触发器Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN) 

/* 

 系统事件触发器和DML触发器的功能差不多，都是为了记录跟踪数据库的变化。 

 不过这些触发器有的对BEFORE后AFTER有限制： 

     STARTUP事件只能有AFTER触发器 

     SHUTDOWN只能有BEFORE触发器 

     LOGON只能有AFTER触发器 

     LOGOFF只能有BEFORE触发器 

     SERVERERROR只能有AFTER触发器 

*/ 

--AFTER SERVERERROR trigger 

/* 

     以下error 不会触发 

     ORA-00600 oracle内部错误 

     ORA-01034 oracle无法使用 

     ORA-01403 没有查询到数据 

     ORA-01422 返回多行数据 

     ORA-01423  

     ORA-04030 

     --触发器不会修复错误 

     --内建函数获取异常信息 

     ora_server_error(index)  返回error number，找不到返回0 

     ora_is_servererror(number) error number是否在异常stack中，也就是判断当前异常是否包含指定异常 

     ora_server_error_depth 异常中error的数量 

     ora_server_error_msg(index) 错误信息 

     ... 

*/ 

CREATE OR REPLACE TRIGGER error_echo 

AFTER SERVERERROR ON SCHEMA 

DECLARE 

    num number := SQLCODE; 

BEGIN 

    FOR i IN 1..ora_server_error_depth LOOP 

        dbms_output.put_line('错误码：'||ora_server_error(i)); 

        dbms_output.put_line('错误信息：'||ora_server_error_msg(i)); 

    END LOOP; 

END; 

/* 

CREATE OR REPLACE TRIGGER ddl_echo 

AFTER DDL ON SCHEMA 

BEGIN 

    dbms_output.put_line(SQLCODE); 

    IF ora_is_servererror(SQLCODE) THEN 

       dbms_output.put_line('error:'||sqlerrm); 

    ELSE  

       dbms_output.put_line('OK:'||sqlerrm); 

    END IF; 

END; 

*/ 

--检验error_echo触发器功能 

create table t_t(t number); 

drop table t_t; 

commit; 

insert into t_t values('xx');--会有异常，被打印出 

--维护触发器 

--查看触发器信息 

--user_triggers 数据字典视图,基本上囊括了触发器定义是所用信息 

select * from user_triggers; 

--当前用户有权限查看到的所有触发器 

select * from all_triggers; 

--使触发器失效 

ALTER TRIGGER trigger_name DISABLE; 

--从新激活触发器 

ALTER TRIGGER trigger_name ENABLE; 

--针对某个表 

--禁止所有的触发器 

ALTER TABLE table_name DISABLE ALL TRIGGERS; 

--激活所有 

ALTER TABLE table_name ENABLE ALL TRIGGERS； 

--从新编译触发器 

ALTER TRIGGER trigger_name COMPILE; 

--删除触发器 

DROP TRIGGER trigger_name; 

/*

reference:

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96590/adg13trg.htm

http://psoug.org/reference/system_events.html

*/