Lesson 7
Database Triggers Conclusion in Oracle
This module covered how to use PL/SQL to create database triggers.
You learned about the actions that fire triggers and the special variables that allow you to work with the old and new values in a row that is being updated, inserted, or deleted.
You created triggers on your own and then learned to combine several triggers into a single trigger.
In this module, you learned how to:
- Define a trigger
- Distinguish between a trigger and a procedure
- Describe the syntax and use of special record variables
- Create an insert, update, or delete trigger
- Build and execute a trigger that covers insert, update, and delete
In the next module, you will learn about the different parameters you can use in triggers, procedures, and functions. You will see how they are referenced within cursors and within PL/SQL blocks
Autonomous Database Trigger
Example 4 marks a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK.
Example 4: Declaring an Autonomous Trigger
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;
END;
/
Comparison of Autonomous Transactions and Nested Transactions
Although an autonomous transaction is started by another transaction, it is not a nested transaction:
- It does not share transactional resources (such as locks) with the main transaction.
- It does not depend on the main transaction. For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions do not.
- It is committed changes are visible to other transactions immediately. (A nested transaction's committed changes are not visible to other transactions until the main transaction commits.)
- Exceptions raised in an autonomous transaction cause a transaction-level rollback, not a statement-level rollback.
Using Autonomous Triggers
Among other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back.
In Example 5, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit changes to the shadow table whether or not you commit changes to the main table.
Example 5: Using Autonomous Triggers
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6),
up_date DATE,
new_sal NUMBER(8,2),
old_sal NUMBER(8,2) );
-- create an autonomous trigger that inserts
-- into the audit table before
-- each update of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
BEFORE UPDATE OF salary ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp_audit
VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;
END;
/
--
