Trigger in PL/SQL with Example - Part 2 | AFTER Trigger

#sql #fullstack #backend #database

AFTER Trigger in PLSQL

Here’s a simple example of a trigger in PL/SQL. Let's say we have a table employees and we want to create a trigger that logs changes when an employee is added.

Step 1: Create the tables

We will create two tables:

employees to store employee information.
employee_audit to store the logs of any employee insertions.

Employees table


-- Create employees table
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    position VARCHAR2(100),
    salary NUMBER
);

Employee_audit table


-- Create employee_audit table for logging changes
CREATE TABLE employee_audit (
    audit_id NUMBER PRIMARY KEY,
    employee_id NUMBER,
    action VARCHAR2(50),
    action_time TIMESTAMP
);

Step 2: Create a PL/SQL Trigger

We want to create a trigger that automatically logs into employee_audit whenever a new employee is inserted into the employees table.


-- Create the trigger
CREATE OR REPLACE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (audit_id,employee_id,action,action_time)
    VALUES (employee_audit_seq.NEXTVAL,:NEW.employee_id,
'INSERT',SYSTIMESTAMP);
END;
/

Explanation:

1. Trigger Name: The trigger is named log_employee_insert.

2. Trigger Type: It’s an AFTER INSERT trigger, meaning it will run after the insertion of a new employee.

3. FOR EACH ROW: This means the trigger will execute once for each row that is inserted.

4. :NEW: Refers to the new data being inserted into the employees table. In this case, :NEW.employee_id accesses the employee_id of the new row being inserted.

5. SYSTIMESTAMP: Logs the current timestamp of when the insertion happens.

6. employee_audit_seq: A sequence to generate unique IDs for audit_id in the employee_audit table. You would need to create this sequence beforehand like this:


-- Create sequence for audit_id
CREATE SEQUENCE employee_audit_seq START WITH 1 INCREMENT BY 1;

Step 3: Test the Trigger

Let’s insert some data into the employees table and see if the trigger works.

-- Insert a new employee
INSERT INTO employees (employee_id, name, position, salary)
VALUES (1, 'John Doe', 'Software Developer', 70000);

-- Check the audit log

SELECT * FROM employee_audit;

Conclusion:

This is a basic example of a PL/SQL AFTER INSERT trigger. The trigger automatically logs the insertion of new employees into the employee_audit table whenever a new record is added to the employees table.