Oracle触发器详细介绍(二)

--触发器 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;

--触发器 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;