Shulou(Shulou.com)06/01 Report--

The previous article had an understanding of some basic knowledge of triggers and verified them in this article.

Before trigger

[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

Create a test table 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)

Create sequence student_id_seq

Create sequence student_id_seqminvalue 1maxvalue 9999999999999999999999999999start with 100increment by 1 is SQL > select * from student; no rows selectedSQL > select * from cat TABLE_NAME TABLE_TYPE---STUDENT TABLESTUDENT_ID_SEQ SEQUENCEcreate 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 trigger

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

This table is used to record information about different tables in the database, such as who deleted or updated the data from the student table, and when.

The following triggers are triggered after an update or delete to the student table

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 = vroomtype; 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

When triggers are disabled, update operations on the student table no longer trigger the generation of records to the record table

Correspond to enable

SQL > alter trigger student_aud enable; Trigger altered.

3. Autonomous affairs

Autonomous transactions are independent transactions initiated by other transactions (often referred to as the main transaction). Autonomous transactions may execute multiple dml statements and commit or roll back rather than commit or roll back the dml statements executed by the main transaction.

If you want to be able to record audit data even if the main transaction fails, in which case the main transaction is oriented to

The update or delete of the table needs to define autonomous transactions that can be committed independently of the main transaction.

To define the main transaction, you need to use the autonomous_transaction compilation instruction in the declaration section of the statement block

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

If you currently update or delete the contents of the table student, regardless of success or failure, the current action is recorded in the record table. The disadvantage is that a maximum of two pieces of data can be recorded in the record table, only the current time of the latest operation and the operator.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.