Oracle中触发器（2）

上一篇对触发器的一些基本知识有了了解，在这一篇操作进行验证

before触发器

[oracle@test ~]$ sqlplus / as sysdba

SQL> create user trigger_test identified by 123456 ;SQL> grant create session to trigger_test ;SQL> grant create table to trigger_test ;SQL> grant dba to trigger_test ;SQL> grant resource to trigger_test ；SQL> grant create sequence to trigger_test ;

[oracle@test ~]$ sqlplus trigger_test/123456

创建测试表student

SQL> create table student(STUDENT_ID NUMBER(8),name varchar2(15),CREATED_BY VARCHAR2(30) not null,CREATED_DATE DATE not null,MODIFIED_BY VARCHAR2(30) not null,MODIFIED_DATE DATE not null)

创建序列student_id_seq

create sequence student_id_seqminvalue 1maxvalue 9999999999999999999999999999start with 100increment by 1;

SQL> select * from student ;no rows selectedSQL> select * from cat ;TABLE_NAME                     TABLE_TYPE------------------------------ -----------STUDENT                   TABLESTUDENT_ID_SEQ                 SEQUENCE

create or replace trigger student_before_insertbefore insert on studentfor each row declare  v_student_id student.student_id%type ;begin  select student_id_seq.nextval  into v_student_id  from dual ;    :new.student_id := v_student_id ;  :new.created_by := user ;  :new.created_date := sysdate ;  :new.modified_by := user ;  :new.modified_date := sysdate ;  end;

SQL> insert into student (name) values ('mjt');SQL> commit ;SQL> select * from student ;STUDENT_ID NAME          CREATED_BY                      CREATED_DATE---------- --------------- ------------------------------ -------------------MODIFIED_BY                    MODIFIED_DATE------------------------------ -------------------       100 mjt             TRIGGER_TEST               2015-07-29 20:51:19TRIGGER_TEST                   2015-07-29 20:51:19SQL> insert into student (name) values ('cxq') ;1 row created.SQL> select * from student ;STUDENT_ID NAME          CREATED_BY                      CREATED_DATE---------- --------------- ------------------------------ -------------------MODIFIED_BY                    MODIFIED_DATE------------------------------ -------------------       100 mjt             TRIGGER_TEST               2015-07-29 20:51:19TRIGGER_TEST                   2015-07-29 20:51:19       101 cxq             TRIGGER_TEST               2015-07-29 21:00:54TRIGGER_TEST                   2015-07-29 21:00:54

after触发器

SQL> create table record(table_name varchar2(30),transaction_name varchar2(10),transaction_user varchar2(30),transaction_date date);

这个表被用来记录数据库中不同表的信息，如，可以记录谁从student表中删除或者更新数据，以及记录时间。

下面的触发器针对对student表的更新或者删除操作，在此之后进行触发

create or replace trigger student_aud  after update or delete on studentdeclare  v_type varchar2(10);begin  if updating then    v_type := 'UPDATE';    elsif deleting then    v_type := 'DELETE';  end if;  update trigger_test.record     set transaction_user = user,      transaction_date = sysdate   where table_name = 'student'     and transaction_name = v_type;  if sql%notfound then    insert into trigger_test.record values ('student', v_type, user, sysdate);  end if;end;

SQL> select * from record ;no rows selectedSQL> select * from student ;STUDENT_ID NAME          CREATED_BY                      CREATED_DATE---------- --------------- ------------------------------ -------------------MODIFIED_BY                    MODIFIED_DATE------------------------------ -------------------       100 mjt             TRIGGER_TEST               2015-07-29 20:51:19TRIGGER_TEST                   2015-07-29 20:51:19       101 cxq             TRIGGER_TEST               2015-07-29 21:00:54TRIGGER_TEST                   2015-07-29 21:00:54SQL> update student set name = 'somebody' where name = 'mjt' ;1 row updated.SQL> commit ;Commit complete.SQL> select * from student ;STUDENT_ID NAME          CREATED_BY                      CREATED_DATE---------- --------------- ------------------------------ -------------------MODIFIED_BY                    MODIFIED_DATE------------------------------ -------------------       100 somebody        TRIGGER_TEST               2015-07-29 20:51:19TRIGGER_TEST                   2015-07-29 20:51:19       101 cxq             TRIGGER_TEST               2015-07-29 21:00:54TRIGGER_TEST                   2015-07-29 21:00:54SQL> select * from record ;TABLE_NAME                     TRANSACTIO TRANSACTION_USER------------------------------ ---------- ------------------------------TRANSACTION_DATE-------------------student                   UPDATE          TRIGGER_TEST2015-07-29 21:50:46SQL> delete student where name = 'somebody' ;1 row deleted.SQL> commit ;Commit complete.SQL> select * from student ;STUDENT_ID NAME          CREATED_BY                      CREATED_DATE---------- --------------- ------------------------------ -------------------MODIFIED_BY                    MODIFIED_DATE------------------------------ -------------------       101 cxq             TRIGGER_TEST               2015-07-29 21:00:54TRIGGER_TEST                   2015-07-29 21:00:54SQL> select * from record ;TABLE_NAME                     TRANSACTIO TRANSACTION_USER------------------------------ ---------- ------------------------------TRANSACTION_DATE-------------------student                   UPDATE          TRIGGER_TEST2015-07-29 21:50:46student                   DELETE          TRIGGER_TEST2015-07-29 21:56:08SQL> alter trigger student_aud disable ;Trigger altered.SQL> update student set name = 'mjt' where name = 'cxq';1 row updated.SQL> commit ;Commit complete.SQL> select * from student ;STUDENT_ID NAME          CREATED_BY                      CREATED_DATE---------- --------------- ------------------------------ -------------------MODIFIED_BY                    MODIFIED_DATE------------------------------ -------------------       101 mjt             TRIGGER_TEST               2015-07-29 21:00:54TRIGGER_TEST                   2015-07-29 21:00:54SQL> select * from record ;TABLE_NAME                     TRANSACTIO TRANSACTION_USER------------------------------ ---------- ------------------------------TRANSACTION_DATE-------------------student                   UPDATE          TRIGGER_TEST2015-07-29 21:50:46student                   DELETE          TRIGGER_TEST2015-07-29 21:56:08

禁用触发器之后，在student表上的update操作不再触发产生记录到record表

对应启用

SQL> alter trigger student_aud enable ;Trigger altered.

3.自治事务

自治事务是由其他事务(通常被称为主事务)发起的独立事务，自治事务也许会执行多个dml语句，并且提交或者回滚，而不会提交或者回滚主事务执行的dml语句。

假如希望即使主事务失败，仍旧能够记录审计数据，这种情况下，主事务是面向

表的update或者delete，需要定义可以独立于主事务进行提交的自治事务。

定义主事务，需要使用autonomous_transaction编译指令在语句块的声明部分

declare pragma autonomous_transactioncommit ;create or replace trigger student_aud  after update or delete on student  declare  v_type varchar2(10);  pragma autonomous_transaction ;begin  if updating then    v_type := 'UPDATE';    elsif deleting then    v_type := 'DELETE';  end if;  update trigger_test.record     set transaction_user = user,      transaction_date = sysdate   where table_name = 'student'     and transaction_name = v_type;  if sql%notfound then    insert into trigger_test.record values ('student', v_type, user, sysdate);  end if;  commit ;end;

如果当前更新或者删除表student中的内容，无论成功或者失败，都会在record表中记录当前的操作。不足的是，record表中最多只能记录两条数据，只是当前最新操作的时间以及操作者。