Lesson 7

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:

1. Define a trigger
2. Distinguish between a trigger and a procedure
3. Describe the syntax and use of special record variables
4. Create an insert, update, or delete trigger
5. 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

Example 4 marks a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements such as

1. COMMIT and
2. ROLLBACK.

Example 4: Declaring an Autonomous Trigger
A key declaration that makes a trigger truly autonomous is the `PRAGMA AUTONOMOUS_TRANSACTION` statement.
This pragma must be included in the trigger's `DECLARE` section to enable autonomous transaction behavior.
Trigger Code: Here’s the version of the trigger with `PRAGMA AUTONOMOUS_TRANSACTION`:

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
  PRAGMA AUTONOMOUS_TRANSACTION; -- Declares the trigger as autonomous
BEGIN
  -- Bind variables are used here for values
  INSERT INTO emp_audit VALUES(:old.employee_id, SYSDATE, :new.salary, :old.salary);
  COMMIT; -- Autonomous triggers allow transaction control
END;
/

Key Points for Autonomous Triggers:

1. Autonomous Transaction Declaration:
   • The PRAGMA AUTONOMOUS_TRANSACTION statement in the DECLARE section makes the trigger autonomous.
   • Without this pragma, the trigger is part of the main transaction and cannot perform COMMIT or ROLLBACK.
2. Transaction Control:
   • Autonomous triggers can perform transaction control (COMMIT or ROLLBACK) independently of the main transaction.
   • This is useful for auditing, logging, or other operations that should not impact the main transaction.
3. Bind Variables:
   • The :old and :new bind variables are correctly used to access the old and new values of the salary column for the row being updated.
4. Use Case:
   • Autonomous triggers are often used for logging or auditing purposes, as shown in this example.
5. Testing:
   • Ensure that the employees table exists and contains the employee_id and salary columns.
   • Test the trigger by updating the salary column in the employees table and verify that the changes are recorded in the emp_audit table.

Example Test: To verify the trigger, execute the following commands:

-- Insert a sample employee
INSERT INTO employees (employee_id, salary) VALUES (1001, 5000);

-- Update the salary to trigger the audit_sal trigger
UPDATE employees SET salary = 6000 WHERE employee_id = 1001;

-- Check the emp_audit table for audit data
SELECT * FROM emp_audit;

If the trigger works correctly, you should see a record in the `emp_audit` table containing the `employee_id`, `up_date`, `new_sal`, and `old_sal`.
Conclusion: With the addition of the `PRAGMA AUTONOMOUS_TRANSACTION`, the trigger becomes valid as an autonomous database trigger and performs as intended.

Although an autonomous transaction is started by another transaction, it is not a nested transaction:

1. It does not share transactional resources (such as locks) with the main transaction.
2. 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.
3. 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.)
4. 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;
/
--

The statements and example about autonomous transactions and triggers hold true for Oracle 11g, as the functionality of autonomous transactions is consistent in that version. Here's a breakdown:

1. Autonomous Transactions vs. Nested Transactions
   • Transactional Independence: Autonomous transactions do not share transactional resources like locks with the main transaction. This is true in Oracle 11g.
   • Rollback Independence: If the main transaction rolls back, the autonomous transaction does not roll back. This also holds true.
   • Visibility of Changes: Autonomous transactions commit independently, and their changes are visible immediately, even if the main transaction is uncommitted. This is a distinguishing feature of autonomous transactions.
   • Exception Handling: Exceptions in an autonomous transaction cause a transaction-level rollback for that transaction only, not just the statement. This is the correct behavior in Oracle 11g.
2. Using Autonomous Triggers
   • Autonomous triggers can be used to log actions like inserts, updates, or deletes into audit tables. This works independently of whether the main transaction commits or rolls back.
   • The example provided demonstrates a common use case for auditing in Oracle 11g. The PRAGMA AUTONOMOUS_TRANSACTION directive ensures the trigger operates independently of the main transaction.

Example 5: Validity The example trigger definition is valid in Oracle 11g:

• The PRAGMA AUTONOMOUS_TRANSACTION declaration specifies the independence of the transaction within the trigger.
• The INSERT INTO emp_audit statement logs the change before the salary update.
• The COMMIT ensures that the change to emp_audit is finalized regardless of the status of the main transaction.

Key Notes

1. The behavior of autonomous transactions described aligns with Oracle's documentation for Oracle 11g.
2. Autonomous transactions are particularly useful for logging, as demonstrated in the provided example, ensuring that audit trails are maintained regardless of the success or failure of the main transaction.