PLSQL Trigger

Trigger is a PL/SQL block that is automatically executed in response to specific events.

Features:

1. Automatic Execution: Triggers are automatically executed when the event that they are defined on occurs, without user intervention.
2. Event-Driven: Triggers can be fired by various events like INSERT, UPDATE, or DELETE on a table or view.
3. Before and After Triggers: Triggers can be executed either before the triggering event (BEFORE TRIGGER) or after the event (AFTER TRIGGER).
4. Row-level triggers execute once for each row affected by the DML statement.
5. Statement-level triggers execute only once per statement, regardless of how many rows are affected.
6. Conditional Logic: Triggers can include conditional logic to control when they fire based on the data in the row being affected.
7. Database Events: Triggers can respond to DDL statements (like CREATE, ALTER, DROP) and database system events (e.g., logon, logoff).

Trigger Events:

Database manipulation (DML)

Statement Level
Before insert, before update, before delete, after insert, after update, after delete
Row Level (on each ROW)
Before insert, before update, before delete, after insert, after update, after delete

Database definition (DDL)

Create, alter, drop, truncate, drop, rename, grant, revoke, analyze, audit, noaudit, comment, etc.

Database System operation

After startup, after login, before logoff, after suspend, before shutdown, after db_role_change, after servererror.

Examples:

CREATE TABLE employees (
employee_id  NUMBER GENERATED BY DEFAULT AS IDENTITY,
first_name   VARCHAR2(50),
last_name    VARCHAR2(50),
department_id NUMBER,
salary       NUMBER(10, 2),
hire_date    DATE,
CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

CREATE TABLE departments (
department_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
department_name VARCHAR2(100),
location VARCHAR2(100),
CONSTRAINT pk_departments PRIMARY KEY (department_id)
);

CREATE TABLE salary_log (
log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
employee_id NUMBER,       
old_salary  NUMBER(10, 2),
new_salary  NUMBER(10, 2),
change_date DATE,
CONSTRAINT pk_salary_log PRIMARY KEY (log_id)    
);

CREATE TABLE audit_log (
log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
action      VARCHAR2(50),
table_name  VARCHAR2(50),
action_date DATE,  
CONSTRAINT pk_audit_log PRIMARY KEY (log_id)     
);

CREATE TABLE user_logon_audit (
log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
username    VARCHAR2(50),
logon_time  DATE,
CONSTRAINT pk_user_logon_audit PRIMARY KEY (log_id)         
);

Statement-Level Trigger:

CREATE OR REPLACE TRIGGER trg_departments_update
AFTER UPDATE ON departments
BEGIN
    -- Record the update in an audit table
    INSERT INTO audit_log (action, table_name, action_date)
    VALUES ('UPDATE', 'DEPARTMENTS', SYSDATE);
END;
/

Row-Level Trigger:

CREATE OR REPLACE TRIGGER trg_employee_salary_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    -- Log the change in salary
    INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

Database System-Level Trigger:

CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
    -- Log the login event
    INSERT INTO user_logon_audit (username, logon_time)
    VALUES (USER, SYSDATE);
END;
/

Enable and Disable  the triggers:

alter trigger trigger_name disable;
alter trigger trigger_name  enable;

Enable and Disable  the All triggers:

alter table orders enable all triggers;
alter table orders disable all triggers;

Drop a Trigger:

drop trigger trigger_name;

List Out the Triggers:

SELECT trigger_name, status, trigger_type, table_name FROM user_triggers;
SELECT owner, trigger_name, status, trigger_type, table_name FROM all_triggers;
select * from dba_triggers;

Trigger Restrictions:

1. Mutating Tables: Cannot query or modify the table that is being modified in the triggering statement.
2. No COMMIT/ROLLBACK: Triggers cannot contain explicit COMMIT or ROLLBACK statements.
3. Limited SQL Operations: DDL statements (e.g., CREATE, ALTER, DROP) and modifications to the triggering table within row-level triggers are not allowed.
4. Timing Restrictions: Cannot have both BEFORE and AFTER triggers for the same event on a table.
5. Execution Order: The order of execution for multiple triggers on the same event is not guaranteed.
6. Performance Considerations: Triggers can impact performance, especially with complex logic.
7. Compound Triggers: Can help avoid mutating table issues and consolidate logic, but still subject to limits.
8. Number of Triggers: Up to 12 triggers per DML operation type on a single table.