oracle trigger

原创

潘麟 2013-12-13 20:38:34 博主文章分类：数据库 ©著作权

©著作权归作者所有：来自51CTO博客作者潘麟的原创作品，请联系作者获取转载授权，否则将追究法律责任

触发器 trigger
触发器是一种特殊的过程,它的执行是由一系列事件触发的.
这些事件有用户登录注销事件,DML语句执行事件等等
我们常用的就是DML触发器
事件的精确粒度可以是表级或行级
trigger的实际应用非常广泛,例如A表有数据DML操作,就可以在A表上做个触发器将数据协同更新到B表
我们也经常用触发器来做审计

DML触发器
  创建触发器语法
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE|AFTER|INSTEAD OF} EVENTs
ON TABLE_NAME
[for each row]
declare
  ...
begin
  ...
end;

{BEFORE|AFTER|INSTEAD OF} 触发器在事件执行之前或之后触发还有一种替代触发器
EVENTs 不通类型的触发器有不同的事件例如DML触发器 update or delete or insert
ON TABLE_NAME 触发器依附的表
[for each row] 如果有这个关键字就代表是行级触发器没有就是表级触发器

  检测的谓词可以直接用于 if 条件
updating  如果是update操作此值为true 否则false
inserting 如果是insert操作此值为true 否则false
deleting  如果是delete操作此值为true 否则false

  查看触发器的数据字典
user_source
user_triggers
user_trigger_cols

例1:
禁止DML操作T1表触发器直接抛出错误中断DML操作

SQL> create table t1 as select * from emp;

Table created.

SQL>
create or replace trigger tr_no_dml
before update or delete or insert
on t1
begin
        if updating
        then
                raise_application_error(-20001,'不允许做UPDATE操作!');
        elsif inserting
        then
                raise_application_error(-20002,'不允许做insert操作!');
        else
                raise_application_error(-20003,'不允许做delete操作!');
        end if;
end;

SQL> /

Trigger created.

SQL> update t1 set sal=sal+1 where empno=7788;
update t1 set sal=sal+1 where empno=7788
*
ERROR at line 1:
ORA-20001: 不允许做UPDATE操作!
ORA-06512: at "SCOTT.TR_NO_DML", line 4
ORA-04088: error during execution of trigger 'SCOTT.TR_NO_DML'

SQL> delete t1 where empno=7788;
delete t1 where empno=7788
*
ERROR at line 1:
ORA-20003: 不允许做delete操作!
ORA-06512: at "SCOTT.TR_NO_DML", line 9
ORA-04088: error during execution of trigger 'SCOTT.TR_NO_DML'

SQL> insert into t1 select * from emp where empno=7788;
insert into t1 select * from emp where empno=7788
*
ERROR at line 1:
ORA-20002: 不允许做insert操作!
ORA-06512: at "SCOTT.TR_NO_DML", line 7
ORA-04088: error during execution of trigger 'SCOTT.TR_NO_DML'

SQL>

例2:
触发器实现审计功能：更新t2表做更新记录在aud_tmp表

SQL> create table t2 as select * from emp;

Table created.

SQL>

SQL> create table aud_tmp(exec_time date,exec_type varchar2(10),exec_user varchar2(20));

Table created.

SQL>
获取用户和时间的方法:
SQL> select sys_context('USERENV','CURRENT_USER'),sysdate from dual;

SYS_CONTEXT('USERENV','CURRENT_USER') SYSDATE
------------------------------------------- -------------------
SCOTT 2011-03-11 05:37:06

SQL>

SQL> create or replace trigger tri_t2_after_row
after update or insert or delete
on t2 for each row
begin
      if updating then
       insert into aud_tmp values(sysdate,'update',SYS_context('USERENV','CURRENT_USER'));
      elsif inserting then
insert into aud_tmp values(sysdate,'insert',SYS_context('USERENV','CURRENT_USER'));
      else
insert into aud_tmp values(sysdate,'delete',SYS_context('USERENV','CURRENT_USER'));
      end if;
end;
/

Trigger created.
----------------------->select text from user_source where name='tri_t2_after_row' //大写
SQL> select * from aud_tmp;

no rows selected

SQL> update t2 set sal=sal+1 where empno=7788;

1 row updated.

SQL> select * from aud_tmp;

EXEC_TIME EXEC_TYPE EXEC_USER
------------------- ---------- --------------------
2011-03-11 05:39:32 SCOTT update

SQL>

语句级触发器和行级触发器的区别
行级建立的时带有 for each row 关键字
语句级只触发一次
行级每一行都会触发一次
SQL> create table t3 as select * from emp;

Table created.

SQL>

create or replace trigger tr_tab_t3
before update
on t3
begin
dbms_output.put_line('run tab tr');
end;

create or replace trigger tr_row_t3
before update
on t3
for each row
begin
dbms_output.put_line('run row tr');
end;
/

SQL> update t3 set sal=sal+1 where empno=7788;
run tab tr
run row tr
/*单行操作表级触发一次行级触发一次*/
1 row updated.

SQL>
SQL> update t3 set sal=sal+1 where deptno=10;
run tab tr
run row tr
run row tr
run row tr
/*多行操作表级触发一次行级触发次数由操作行数来决定*/
3 rows updated.

SQL>

触发器触发顺序:
---------------------------------------------------------------------------------
before statement trigger(on table)
->before row trigger (on table for each row)
->after row trigger (on table for each row)
->after statement trigger (on table)

drop table e purge;

create table e as select * from emp;

create or replace trigger e_update1
before update on e
begin
  if updating then
    dbms_output.put_line('table before');
  end if;
end;
/

create or replace trigger e_update2
before update on e for each row
begin
  if updating then
    dbms_output.put_line('row before');
  end if;
end;
/

create or replace trigger e_update3
after update on e for each row
begin
  if updating then
    dbms_output.put_line('row after');
  end if;
end;
/

create or replace trigger e_update4
after update on e
begin
  if updating then
    dbms_output.put_line('table after');
  end if;
end;
/

update e set sal=sal+1 where empno=7369;

--验证触发器的状态
select trigger_name,status from user_triggers;
--禁用某个触发器
ALTER TRIGGER e_update3 disable;
--启动某个触发器
ALTER TRIGGER e_update3 enable;
--禁用某个表上的所有触发器
alter table e disable all triggers;
--删除触发器
DROP TRIGGER e_update3;

行级触发器的协同更新
行级触发器增加了更新动作的旧值和新值的存储
表级不可以,/*行级触发器才有 :old.COL 和 :new.COL 的定义*/
create table t4 as select * from emp;

SQL>  create or replace trigger tri_t4
    after update or delete or insert
    on t4
    for each row
    begin
      if updating then
          dbms_output.put_line('old_value: '||:old.sal||' '||'new_value: '||:new.sal);
      elsif inserting then
          dbms_output.put_line('old_value: '||:old.sal||' '||'new_value: '||:new.sal);
      else
         dbms_output.put_line('old_value: '||:old.sal||' '||'new_value: '||:new.sal);
     end if;
  end;
SQL> /

Trigger created.

SQL> update t4 set sal=sal+1 where ename='SCOTT';
old_value: 3000 new_value: 3001

1 row updated.

SQL> delete t4 where ename='SCOTT';
old_value: 3001 new_value:

1 row deleted.

SQL> insert into t4 select * from emp where ename='SCOTT';
old_value: new_value: 3000

1 row created.

SQL>

练习：前提是d和e之间有主外键关联关系，并且没有设置on delete cascade 和 on delete set null
1.删除d表数据时将e表deptno置空
2.更新d表deptno时自动更新e表deptno
3.删除d表数据时将e表同部门员工删除

自治事务

DML（delete/update/insert）触发器中不能使用DDL（CREATE，DROP，ALTER）语句，
也不能使用事务控制语句（ROLLBACK, COMMIT,SAVEPOINT）。
特别注意的是，在触发器的主体中引用的函数（function）/过程（procedure）中也不能有事物控制语句
会报错误:ORA-04092: cannot COMMIT in a trigger
trigger不能含有事物处理的语句!需要使用自制事务！
自制事务可以将触发器内的DML独立成事务
自制事务在declare中声明
PRAGMA autonomous_transaction;
切记:如果触发器中引用的过程也带有DDL DCL也要定义成自制事务

不使用自制事务

拿上面做过的T2表触发器做演示
create or replace trigger tr_aud_t2
after update
on t2
begin
insert into aud_tmp values(sysdate,sys_context('USERENV','CURRENT_USER'),'update');
commit;
end;
SQL> /

Trigger created.

SQL> select ename,sal from t2 where empno=7788;

ENAME SAL
---------- ----------
SCOTT 102

SQL> update t2 set sal=sal+1 where empno=7788;
update t2 set sal=sal+1 where empno=7788
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TR_AUD_T2", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR_AUD_T2'

SQL>

改成自治事务
create or replace trigger tr_aud_t2 after update on t2
declare
   PRAGMA autonomous_transaction;
begin
   insert into aud_tmp values(sysdate,sys_context('USERENV','CURRENT_USER'),'update');
   commit;
end;
SQL> /

SQL> select ename,sal from t2 where empno=7788;

ENAME SAL
---------- ----------
SCOTT 102
SQL> select * from aud_tmp;

EXEC_TIME EXEC_TYPE EXEC_USER
------------------- ---------- --------------------
2011-03-11 05:39:32 SCOTT update

SQL> update t2 set sal=sal+1 where empno=7788;

1 row updated.

SQL> select ename,sal from t2 where empno=7788;

ENAME SAL
---------- ----------
SCOTT 103

SQL> select * from aud_tmp;

EXEC_TIME     EXEC_TYPE  EXEC_USER
------------------- ---------- --------------------
2011-03-11 05:39:32 SCOTT      update
2011-03-11 06:02:04 SCOTT      update

SQL> roll
Rollback complete.
SQL> select ename,sal from t2 where empno=7788;

ENAME SAL
---------- ----------
SCOTT 102

SQL> select * from aud_tmp;

EXEC_TIME     EXEC_TYPE  EXEC_USER
------------------- ---------- --------------------
2011-03-11 05:39:32 SCOTT      update
2011-03-11 06:02:04 SCOTT      update

SQL>
这样触发器提交的只有触发器内的事务(AUD_tmp表的插入)
而T2表的更新被回滚了

*************************
替代触发器 instead of
/*做触发器在试图上把单条的DML分解成多条DML到多个表就可以插入了
但带有聚集函数的还是不能操作*/

***********************************
关闭d e表之前我们做的所有触发器以免影响此次测试
SQL> alter table d disable all triggers;

Table altered.

SQL> alter table e disable all triggers;

Table altered.
************************************
SCOTT> create table d as select * from dept;
SCOTT> create table e as select * from emp;
SCOTT> alter talbe d add primary key (deptno);
SCOTT> alter table e add foreign key (deptno) references d(deptno);

创建个视图
CREATE OR REPLACE VIEW V1 AS
  SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC
  FROM e ,d
  WHERE E.DEPTNO=D.DEPTNO;

View created.

SQL> select * from v1;

     EMPNO ENAME   DEPTNO DNAME  LOC
---------- ---------- ---------- -------------- -------------
      7369 SMITH       20 RESEARCH DALLAS
      7499 ALLEN       30 SALES  CHICAGO
      7521 WARD        30 SALES  CHICAGO
      7566 JONES       20 RESEARCH DALLAS
      7654 MARTIN       30 SALES  CHICAGO
      7698 BLAKE       30 SALES  CHICAGO
      7782 CLARK       10 ACCOUNTING NEW YORK
      7788 SCOTT       20 RESEARCH DALLAS
      7839 KING        10 ACCOUNTING NEW YORK
      7844 TURNER       30 SALES  CHICAGO
      7876 ADAMS       20 RESEARCH DALLAS
      7900 JAMES       30 SALES  CHICAGO
      7902 FORD        20 RESEARCH DALLAS
      7934 MILLER       10 ACCOUNTING NEW YORK

14 rows selected.

SQL>

无法插入因为这个视图涉及多个表连接是复杂视图
SQL> insert into v1 values(8000,'SEKER',50,'JS','BJ');
insert into v1 values(8000,'SEKER',50,'JS','BJ')
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

SQL>

利用替代触发器分解插入语句实现插入

insert into v1 values(8000,'SEKER',50,'JS','BJ');
分解这个语句到每个表
insert into d(DEPTNO,DNAME,LOC) values(50,'JS','BJ');
insert into e(EMPNO,ENAME，deptno) values(8000,'SEKER'，50);
但在程序中我们应该使用 :new.XXX的值

create or replace trigger tr_DML_v1
instead of insert
on v1
begin
insert into d(DEPTNO,DNAME,LOC) values(:new.DEPTNO,:new.DNAME,:new.LOC);
insert into e(EMPNO,ENAME) values(:new.EMPNO,:new.ENAME);
end;
/

Trigger created.

SQL> insert into v1 values(8000,'SEKER',50,'JS','BJ');

1 row created.

SQL> select * from d where deptno=50;

DEPTNO DNAME LOC
---------- -------------- -------------
50 JS BJ

SQL> select * from e where empno=8000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
8000 SEKER

SQL>

*****
create table d as select * from departments;
create table e as select * from employees;
select constraint_name,constraint_type ,table_name from user_constraints
where table_name in ('D','E');
//删除其中的非空约束

create or replace view v_d_e
as
select employee_id,last_name,department_id,department_name
from d natural join e;

insert into v_d_e values(777,'mary',280,'uplooking');

select * from e;
select * from d;

练习 1:DML触发器
---------------------------------------------------------------------------------
create or replace trigger e_update
before update of sal on e
for each row
begin
  if updating then
    raise_application_error(-20001,'salary do not modify!');
  end if;
end;
/

练习 2:DML触发器限制数据修改
---------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER secure_emp
before INSERT ON e
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '09'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'insert into table E！');
  END IF;
END;
/

练习 3:更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER UPDATE OF deptno ON d FOR EACH ROW
BEGIN
  IF (UPDATING AND :old.deptno != :new.deptno) THEN --关联标识（保留结构）
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
/

练习 4:删除d表数据时自动删除e表数据
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER DELETE ON d FOR EACH ROW
BEGIN
  IF DELETING THEN
    delete e
    WHERE deptno = :old.deptno;
  END IF;
END;
/

练习 5:删除d表数据时将e表deptno置空
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER DELETE ON d FOR EACH ROW
BEGIN
  IF DELETING THEN
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
/

练习 6:删除d表数据时将e表deptno置空，更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER delete or UPDATE OF deptno ON d FOR EACH ROW
BEGIN
  IF deleting or (UPDATING AND :old.deptno != :new.deptno) THEN
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  END IF;
END;
/

练习 7:删除d表数据时自动删除e表数据，更新d表deptno时自动更新e表deptno
---------------------------------------------------------------------------------
CREATE or replace TRIGGER vdate
AFTER delete or UPDATE OF deptno ON d FOR EACH ROW
BEGIN
  IF (UPDATING AND :old.deptno != :new.deptno) THEN
    UPDATE e
    SET deptno = :new.deptno
    WHERE deptno = :old.deptno;
  elsif deleting then
    delete e
    WHERE deptno = :old.deptno;
  END IF;
END;
/
create table log_table(sid number,
                       serial# number,
                       username varchar2(30),
                       action varchar2(8),
                       log_time varchar2(19));
练习 8:trigger不能含有事物处理的语句!需要使用自制事务！
---------------------------------------------------------------------------------
create or replace procedure Autonomous
(v_sal e.sal%type,v_empno e.empno%type)
is
PRAGMA Autonomous_transaction; --声明为自治事物
begin
    update e set sal=v_sal where empno=v_empno;
    commit;
end Autonomous;
/

--如果emp表的sal被更新那么自动更新e表的sal
create or replace trigger cascade_update_e
before update of sal on emp for each row
declare
  v_sal e.sal%type;
  v_empno e.empno%type;
begin
  v_sal := :new.sal;
  v_empno := :new.empno;
  Autonomous(v_sal,v_empno);
end;
/

练习 9:系统触发器
---------------------------------------------------------------------------------
conn scott/tiger
drop table log_table;
create table log_table(sid number,
                       serial# number,
                       username varchar2(30),
                       action varchar2(8),
                       log_time varchar2(19));

grant select on scott.log_table to public;
grant insert on scott.log_table to public;

sys@prod>
create or replace trigger scott_logon
  after logon on database
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end scott_logon;
/

create or replace trigger scott_logof
  before logoff on database
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
  v_status v$session.status%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'logoff',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end scott_logof;
/

--通过用户级别触发器修改排序方法:（也可以修改语言选项或日期格式）
CONN SCOTT/TIGER
CREATE OR REPLACE TRIGGER test_tri
AFTER LOGON ON schema //局部的
DECLARE
sqlstr VARCHAR2(200) := 'alter session set nls_sort = SCHINESE_STROKE_M';
BEGIN
execute immediate sqlstr;
END test_tri;
/

--通过数据库级别触发器修改排序方法:
CONN / AS SYSDBA
CREATE OR REPLACE TRIGGER test_tri
AFTER LOGON ON DATABASE //全局的
DECLARE
sqlstr VARCHAR2(200) := 'alter session set nls_sort = SCHINESE_STROKE_M';
BEGIN
  IF (USER = 'SCOTT') THEN
    execute immediate sqlstr;
  END IF;
END test_tri;
/

--数据库级别：得具有访问v$的权限!
conn sys/oracle as sysdba

create or replace trigger LogSchemaConnects
  after logon on database
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'logon',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end LogSchemaConnects;
/

create or replace trigger dbshutdown
  before shutdown on database
declare
  v_sid v$mystat.sid%type;
  v_serial# v$session.serial#%type;
  v_username v$session.username%type;
begin
  select sid into v_sid from v$mystat where rownum=1;
  select serial#,username
  into   v_serial#,v_username
  from v$session
  where sid=v_sid;
  insert into scott.log_table
  values (v_sid,v_serial#,v_username,'ShutDown',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end dbshutdown;
/

--限制指定用户从指定IP登录:
CREATE OR REPLACE TRIGGER TRIGGER_RESTRICT_LOGON
AFTER LOGON ON DATABASE
DECLARE
RESTRICTED_USER VARCHAR2(32) := 'SCOTT';
ALLOWED_IP      VARCHAR2(16) := '10.1.1.157';
LOGON_USER      VARCHAR2(32);
CLIENT_IP       VARCHAR2(16);
BEGIN
LOGON_USER := SYS_CONTEXT('USERENV','SESSION_USER');
CLIENT_IP  := NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), 'NULL');
  IF LOGON_USER = RESTRICTED_USER AND CLIENT_IP <> ALLOWED_IP THEN
   RAISE_APPLICATION_ERROR(-20001, RESTRICTED_USER || ' is not allowed to connect from ' || CLIENT_IP);
END IF;
END;
/

验证：
登录方式：
尝试从其他的客户端登录：
ORACLE$ sqlplus scott/tiger@192.168.3.253:1521/orcl11g.neves.com -->服务名--> prod.uplooking.com

练习 10:替代触发器(由视图的dml操作所触发)
-------------------------------------------------------------------------------------------
CREATE OR REPLACE VIEW VVV AS
  SELECT E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME,D.LOC
  FROM EMP E,DEPT D
  WHERE E.DEPTNO=D.DEPTNO;

INSERT INTO VVV VALUES (7777,'zjz',50,'SALES','Beijing');

CREATE OR REPLACE TRIGGER insert_EMP_AND_DEPT
   INSTEAD OF INSERT ON VVV
DECLARE
   duplicate_info EXCEPTION;
   PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
IF INSERTING THEN
INSERT INTO DEPT (DEPTNO, DNAME, LOC)
VALUES (
   :new.DEPTNO,
   :new.DNAME,
   :new.LOC);
   INSERT INTO EMP
     (EMPNO, ENAME, DEPTNO)
   VALUES (
   :new.EMPNO,
   :new.ENAME,
   :new.DEPTNO);
ELSIF DELETING THEN
NULL;
ELSE
NULL;
END IF;
EXCEPTION
   WHEN duplicate_info THEN
     RAISE_APPLICATION_ERROR (
       num=> -20107,
       msg=> 'Duplicate EMP or DEPT ID');
END insert_EMP_AND_DEPT;
/