AFTER Trigger - (Oracle Advance)

An AFTER trigger in Oracle is a type of trigger that is executed after the specified event occurs. This type of trigger is commonly used for auditing changes to data in a table.

Syntax

The syntax for creating an AFTER trigger in Oracle is as follows:

CREATE OR REPLACE TRIGGER trigger_name
AFTER event
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic goes here
END;

Here, trigger_name is the name of the trigger, event is the event that triggers the execution of the trigger, table_name is the name of the table on which the trigger is defined, and FOR EACH ROW specifies that the trigger is executed for each row affected by the triggering event.

Example

Here is an example of an AFTER trigger in Oracle that is used for auditing changes to data in a table called employees:

CREATE OR REPLACE TRIGGER audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO employee_audit (employee_id, action, audit_date)
        VALUES (:NEW.employee_id, 'INSERT', SYSDATE);
    ELSIF UPDATING THEN
        INSERT INTO employee_audit (employee_id, action, audit_date)
        VALUES (:OLD.employee_id, 'UPDATE', SYSDATE);
    ELSE
        INSERT INTO employee_audit (employee_id, action, audit_date)
        VALUES (:OLD.employee_id, 'DELETE', SYSDATE);
    END IF;
END;

In this example, we have defined an AFTER trigger called audit_employee_changes that is executed after an INSERT, UPDATE, or DELETE event on the employees table. For each row affected by the triggering event, the trigger inserts a row into an audit table called employee_audit. The audit table stores the employee ID, the action taken (INSERT, UPDATE, or DELETE), and the date and time of the change.

Output

The output of an AFTER trigger is the execution of the trigger logic. In the example above, the execution of the trigger logic results in the insertion of a row into the employee_audit table.

Explanation

In the example above, we have created an AFTER trigger called audit_employee_changes that is used for auditing changes to data in a table called employees. The trigger is executed after an INSERT, UPDATE, or DELETE event on the employees table, and for each row affected by the event, the trigger inserts a row into the employee_audit table with the relevant information about the change. The IF statement is used to determine the action taken on the data and insert the appropriate row into the audit table.

Use

AFTER triggers are commonly used for auditing changes to data in a table. They provide a way to automatically record changes made to data, which can be useful for detecting and resolving issues that may arise in the application.

Important Points

• An AFTER trigger in Oracle is executed after a specified event occurs.
• AFTER triggers are commonly used for auditing changes to data in a table.
• The :NEW and :OLD constructs can be used to access the old and new values of a row affected by an event.

Summary

In summary, an AFTER trigger in Oracle is a type of trigger that is executed after the specified event occurs. This type of trigger is commonly used for auditing changes to data in a table. The trigger logic is executed after the event occurs, and the :NEW and :OLD constructs can be used to access the old and new values of a row affected by the event.