AFTER INSERT Trigger Notes

An AFTER INSERT trigger in Oracle is a type of trigger that is automatically invoked after an INSERT statement has been executed on a table or view. This trigger is primarily used for operations that need to occur once the data has been inserted into the table, such as logging, maintaining audit trails, updating related tables, or performing any other post-insert operations.

Here are the detailed notes about AFTER INSERT triggers in Oracle

1.Definition of AFTER INSERT Trigger

An AFTER INSERT trigger is used to execute a set of actions automatically after a new row has been inserted into a table. These actions can include updating other tables, logging data, or performing complex validations.

2. Trigger Syntax

The syntax for creating an AFTER INSERT trigger is:

CREATE [OR REPLACE] TRIGGER trigger_name

AFTER INSERT

ON table_name

[FOR EACH ROW]

DECLARE

  -- Optional declaration section

BEGIN

  -- Trigger logic (PL/SQL code)

END;

• trigger_name: The name of the trigger.
• AFTER INSERT: Specifies that the trigger will fire after an INSERT operation on the table.
• table_name: The name of the table or view the trigger is attached to.
• FOR EACH ROW: This clause makes the trigger row-level (it will execute once for each row inserted). If omitted, the trigger is statement-level (executed once for the entire statement, regardless of the number of rows affected).

 

3. Trigger Timing and Execution Order

• The AFTER INSERT trigger is invoked after the actual INSERT operation has been completed and the changes have been committed to the database.
• Since the trigger fires after the insert, it means that any changes to the table done by the trigger will not affect the insert operation that fired the trigger.
• This means you cannot use :NEW values to modify the row you just inserted. However, you can use :NEW values to affect other tables.

 

4. :NEW Keyword

• The :NEW qualifier is used to reference the new values being inserted into the table.
• For AFTER INSERT triggers: The :NEW values are available and contain the values that were inserted into the table.

For example:

CREATE OR REPLACE TRIGGER log_employee_insert

AFTER INSERT

ON employees

FOR EACH ROW

BEGIN

  -- Log inserted employee details

  INSERT INTO employee_log (emp_id, emp_name, emp_position)

  VALUES (:NEW.employee_id, :NEW.employee_name, :NEW.position);

END;

In this example:

• The AFTER INSERT trigger fires after a row is inserted into the employees table.
• The :NEW pseudo-record is used to access the new employee_id, employee_name, and position values from the row that was just inserted into employees, and the trigger logs these into an employee_log table.

 

5. Types of Triggers

• Row-level trigger: Fires once for each row inserted.
• Defined with FOR EACH ROW in the trigger.
• It is useful when you want to perform actions based on individual row data.
• Statement-level trigger: Fires once for the entire INSERT statement, regardless of how many rows are affected.
• This is the default behavior if FOR EACH ROW is not specified.

Example of row-level trigger:

CREATE OR REPLACE TRIGGER employee_audit

AFTER INSERT

ON employees

FOR EACH ROW

BEGIN

  -- Log inserted employee data

  INSERT INTO employee_audit_log (audit_type, employee_id, employee_name)

  VALUES ('INSERT', :NEW.employee_id, :NEW.employee_name);

END;

Example of statement-level trigger:

CREATE OR REPLACE TRIGGER statement_level_trigger

AFTER INSERT

ON employees

BEGIN

  -- Log the insertion event in a separate audit table

  INSERT INTO audit_log (action, table_name)

  VALUES ('INSERT', 'employees');

END;

 

6. Trigger Execution Order

• Triggers execution order: If there are multiple triggers of the same type (e.g., AFTER INSERT), Oracle executes them in the order in which they were created unless the WHEN clause or specific ordering conditions are set.
• BEFORE vs AFTER triggers: The AFTER INSERT trigger always fires after the insert, while a BEFORE INSERT trigger fires before the insert operation, allowing modification of the data before it is inserted into the table.

 

7. Trigger Compiling and Validity

• Once a trigger is created or replaced using the CREATE OR REPLACE TRIGGER statement, it is compiled and will be valid unless there are syntax errors or issues with the underlying objects (like tables).
• If the trigger becomes invalid due to any reasons (such as dropping a table), you may need to recompile or recreate the trigger.

 

8. Cascading Triggers

• Cascading triggers refer to situations where one trigger action causes another trigger to fire. For example, an AFTER INSERT trigger might update a related table, which could fire a different trigger.
• You should be cautious of infinite loops in such cases (e.g., AFTER INSERT on one table causing an INSERT on another table, which then triggers the original AFTER INSERT).

To avoid unwanted cascading triggers, use the PRAGMA directives (such as PRAGMA AUTONOMOUS_TRANSACTION) for independent transactions or control the flow of triggers carefully.

9. Use Cases for AFTER INSERT Triggers

• Auditing: Record insertions to a separate log or audit table.
• Enforcing Business Rules: After an insert, calculate and update related data in other tables.
• Validation: Perform validation or checks against other tables after inserting data (although this is better handled by constraints or application logic).
• Data Transformation: Automatically transform the inserted data or copy it to other tables for normalization.
• Synchronizing Data: Ensure the inserted data is reflected across multiple tables (e.g., cascading updates).

 

10. Considerations and Performance Impacts

• Performance: Triggers add overhead to DML operations. An AFTER INSERT trigger may impact the performance of the INSERT operation, especially if the trigger involves complex operations or updates large amounts of data.
• Error Handling: Be sure to handle exceptions within triggers to avoid issues like locking or incomplete operations.
• Atomicity: If an AFTER INSERT trigger raises an error, the entire INSERT operation (and any other triggers associated with it) will be rolled back.

 

Example: AFTER INSERT Trigger to Update a Summary Table

CREATE OR REPLACE TRIGGER update_sales_summary

AFTER INSERT

ON sales

FOR EACH ROW

BEGIN

  -- Update the total sales amount in the summary table

  UPDATE sales_summary

  SET total_sales = total_sales + :NEW.amount

  WHERE store_id = :NEW.store_id;

 

  -- If the summary entry doesn't exist, insert a new row

  IF SQL%ROWCOUNT = 0 THEN

    INSERT INTO sales_summary (store_id, total_sales)

    VALUES (:NEW.store_id, :NEW.amount);

  END IF;

END;

In this example:

• Every time a new sale is inserted into the sales table, the AFTER INSERT trigger updates the total sales for the specific store in the sales_summary table.
• If there is no existing summary for the store, the trigger inserts a new entry into the sales_summary table.