Oracle触发器详细介绍(二)

lozzjtdilp

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;  
  

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