频道栏目
首页 > 数据库 > Oracle > 正文
oracle trigger (触发器)
2011-08-09 08:40:23           
收藏   我要投稿

 

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:

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

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

*/

点击复制链接 与好友分享!回本站首页
相关TAG标签 触发器
上一篇:Oracle中分区表的使用
下一篇:oracle sql语句笔记
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站