Oracle中触发器（2）

2025-04-16 技术教程

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

before触发器

[oracle@test ~]$ sqlplus / as sysdba

SQL>createusertrigger_testidentifiedby123456;SQL>grantcreatesessiontotrigger_test;SQL>grantcreatetabletotrigger_test;SQL>grantdbatotrigger_test;SQL>grantresourcetotrigger_test；SQL>grantcreatesequencetotrigger_test;

[oracle@test ~]$ sqlplus trigger_test/123456

创建测试表student

SQL>createtablestudent(STUDENT_IDNUMBER(8),namevarchar2(15),CREATED_BYVARCHAR2(30)notnull,CREATED_DATEDATEnotnull,MODIFIED_BYVARCHAR2(30)notnull,MODIFIED_DATEDATEnotnull)

创建序列student_id_seq

createsequencestudent_id_seqminvalue1maxvalue9999999999999999999999999999startwith100incrementby1;

SQL>select*fromstudent;norowsselectedSQL>select*fromcat;TABLE_NAMETABLE_TYPE-----------------------------------------STUDENTTABLESTUDENT_ID_SEQSEQUENCE

createorreplacetriggerstudent_before_insertbeforeinsertonstudentforeachrowdeclarev_student_idstudent.student_id%type;beginselectstudent_id_seq.nextvalintov_student_idfromdual;:new.student_id:=v_student_id;:new.created_by:=user;:new.created_date:=sysdate;:new.modified_by:=user;:new.modified_date:=sysdate;end;

SQL>insertintostudent(name)values('mjt');SQL>commit;SQL>select*fromstudent;STUDENT_IDNAMECREATED_BYCREATED_DATE--------------------------------------------------------------------------MODIFIED_BYMODIFIED_DATE-------------------------------------------------100mjtTRIGGER_TEST2015-07-2920:51:19TRIGGER_TEST2015-07-2920:51:19SQL>insertintostudent(name)values('cxq');1rowcreated.SQL>select*fromstudent;STUDENT_IDNAMECREATED_BYCREATED_DATE--------------------------------------------------------------------------MODIFIED_BYMODIFIED_DATE-------------------------------------------------100mjtTRIGGER_TEST2015-07-2920:51:19TRIGGER_TEST2015-07-2920:51:19101cxqTRIGGER_TEST2015-07-2921:00:54TRIGGER_TEST2015-07-2921:00:54

after触发器

SQL>createtablerecord(table_namevarchar2(30),transaction_namevarchar2(10),transaction_uservarchar2(30),transaction_datedate);

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

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

createorreplacetriggerstudent_audafterupdateordeleteonstudentdeclarev_typevarchar2(10);beginifupdatingthenv_type:='UPDATE';elsifdeletingthenv_type:='DELETE';endif;updatetrigger_test.recordsettransaction_user=user,transaction_date=sysdatewheretable_name='student'andtransaction_name=v_type;ifsql%notfoundtheninsertintotrigger_test.recordvalues('student',v_type,user,sysdate);endif;end;

SQL>select*fromrecord;norowsselectedSQL>select*fromstudent;STUDENT_IDNAMECREATED_BYCREATED_DATE--------------------------------------------------------------------------MODIFIED_BYMODIFIED_DATE-------------------------------------------------100mjtTRIGGER_TEST2015-07-2920:51:19TRIGGER_TEST2015-07-2920:51:19101cxqTRIGGER_TEST2015-07-2921:00:54TRIGGER_TEST2015-07-2921:00:54SQL>updatestudentsetname='somebody'wherename='mjt';1rowupdated.SQL>commit;Commitcomplete.SQL>select*fromstudent;STUDENT_IDNAMECREATED_BYCREATED_DATE--------------------------------------------------------------------------MODIFIED_BYMODIFIED_DATE-------------------------------------------------100somebodyTRIGGER_TEST2015-07-2920:51:19TRIGGER_TEST2015-07-2920:51:19101cxqTRIGGER_TEST2015-07-2921:00:54TRIGGER_TEST2015-07-2921:00:54SQL>select*fromrecord;TABLE_NAMETRANSACTIOTRANSACTION_USER----------------------------------------------------------------------TRANSACTION_DATE-------------------studentUPDATETRIGGER_TEST2015-07-2921:50:46SQL>deletestudentwherename='somebody';1rowdeleted.SQL>commit;Commitcomplete.SQL>select*fromstudent;STUDENT_IDNAMECREATED_BYCREATED_DATE--------------------------------------------------------------------------MODIFIED_BYMODIFIED_DATE-------------------------------------------------101cxqTRIGGER_TEST2015-07-2921:00:54TRIGGER_TEST2015-07-2921:00:54SQL>select*fromrecord;TABLE_NAMETRANSACTIOTRANSACTION_USER----------------------------------------------------------------------TRANSACTION_DATE-------------------studentUPDATETRIGGER_TEST2015-07-2921:50:46studentDELETETRIGGER_TEST2015-07-2921:56:08SQL>altertriggerstudent_auddisable;Triggeraltered.SQL>updatestudentsetname='mjt'wherename='cxq';1rowupdated.SQL>commit;Commitcomplete.SQL>select*fromstudent;STUDENT_IDNAMECREATED_BYCREATED_DATE--------------------------------------------------------------------------MODIFIED_BYMODIFIED_DATE-------------------------------------------------101mjtTRIGGER_TEST2015-07-2921:00:54TRIGGER_TEST2015-07-2921:00:54SQL>select*fromrecord;TABLE_NAMETRANSACTIOTRANSACTION_USER----------------------------------------------------------------------TRANSACTION_DATE-------------------studentUPDATETRIGGER_TEST2015-07-2921:50:46studentDELETETRIGGER_TEST2015-07-2921:56:08

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

对应启用

SQL>altertriggerstudent_audenable;Triggeraltered.

3.自治事务

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

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

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

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

declarepragmaautonomous_transactioncommit;createorreplacetriggerstudent_audafterupdateordeleteonstudentdeclarev_typevarchar2(10);pragmaautonomous_transaction;beginifupdatingthenv_type:='UPDATE';elsifdeletingthenv_type:='DELETE';endif;updatetrigger_test.recordsettransaction_user=user,transaction_date=sysdatewheretable_name='student'andtransaction_name=v_type;ifsql%notfoundtheninsertintotrigger_test.recordvalues('student',v_type,user,sysdate);endif;commit;end;

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