PL/SQL TRIGGER

In PL/SQL, a trigger is a named PL/SQL block associated with a table, view, schema, or database event that automatically fires (executes) when a specific event occurs.

Triggers can be used to enforce business rules, enforce data integrity, log changes, or perform other actions in response to database events.

 

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | {ALL}} ON table_name

[FOR EACH ROW]

DECLARE

   -- Declaration section (optional)

BEGIN

   -- Trigger body (implementation)

END;

 

Example:

CREATE OR REPLACE TRIGGER set_creation_timestamp

BEFORE INSERT ON my_table

FOR EACH ROW

BEGIN

   :NEW.created_at := SYSDATE;

END;

This trigger sets the created_at column of the my_table table to the current date and time (SYSDATE) before each new row is inserted.

 

DML TRIGGER    INSERT, UPDATE, DELETE

DML triggers are fired in response to Data Manipulation Language (DML) statements, such as INSERT, UPDATE, and DELETE operations performed on a table.

They are used to enforce business rules, audit changes, maintain data integrity, and implement other application-specific logic.

INSERT Triggers: Fired when one or more rows are inserted into a table.

UPDATE Triggers: Fired when one or more rows are updated in a table.

DELETE Triggers: Fired when one or more rows are deleted from a table.

CREATE OR REPLACE TRIGGER audit_emp_changes

BEFORE INSERT OR UPDATE OR DELETE ON employees

FOR EACH ROW

BEGIN

   -- Audit employee changes

   INSERT INTO emp_audit_log (emp_id, action_type, action_timestamp)

   VALUES (:OLD.emp_id, 'UPDATE', SYSTIMESTAMP);

END;

DDL TRIGGER    ALTER, DROP, TRUNCARE, GRANT, REVOKE, RENAME

DDL triggers are fired in response to Data Definition Language (DDL) statements, which are used to define, modify, or drop database objects such as tables, views, indexes, or triggers themselves.

DDL triggers are commonly used for enforcing security policies, monitoring database changes, and performing administrative tasks.

CREATE OR REPLACE TRIGGER ddl_audit_trigger

CREATE OR ALTER OR DROP ON DATABASE

DECLARE

   v_sql_text VARCHAR2(4000);

BEGIN

   -- Log DDL statements

   v_sql_text := ora_sql_txt;

   INSERT INTO ddl_audit_log (sql_text, execution_timestamp)

   VALUES (v_sql_text, SYSTIMESTAMP);

END;

 

SYSTEM TRIGGER     LOGON, LOGOFF, STARTUP, SHUTDOWN

A system trigger is a type of trigger in PL/SQL that is automatically fired in response to certain system events, such as database startup, shutdown, or DDL statements.

CREATE OR REPLACE TRIGGER log_startup

AFTER STARTUP ON DATABASE

BEGIN

   INSERT INTO startup_log (startup_timestamp)

   VALUES (SYSTIMESTAMP);

END;

This trigger will automatically log the timestamp of each database startup event into the startup_log table.

 

INSTED OF TRIGGER     TRIGGER ON VIEWS

These triggers are associated with views and fire instead of the triggering DML (Data Manipulation Language) statement (e.g., INSERT, UPDATE, DELETE). They are often used to implement complex view updates or enforce specific business rules.

CREATE OR REPLACE TRIGGER prevent_salary

updates

INSTEAD OF UPDATE OF salary ON employee_view

FOR EACH ROW

BEGIN

   -- Check if the salary column is being updated

   IF :OLD.salary != :NEW.salary THEN

      -- Raise an error to prevent the update

      RAISE_APPLICATION_ERROR(-20001, 'Updates to salary are not allowed.');

   END IF;

END;

With this Instead-of trigger in place, any attempt to update the salary column of the employee_view view will result in an error, ensuring that the salary column remains read-only through the view.

 

COMPOUND TRIGGER TWO OR MORE DML TRIGGER IN SIGLE BLOCK

A compound trigger in PL/SQL allows you to define multiple trigger timing points (BEFORE, AFTER) and sections (BEFORE EACH ROW, AFTER EACH ROW) within a single trigger definition.

This is useful when you need to perform complex actions that involve multiple stages of processing.

CREATE OR REPLACE TRIGGER salary_change_trigger

FOR UPDATE OF salary ON employee_salary

COMPOUND TRIGGER

    -- Global variables to store the old and new values

    old_salary employee_salary.salary%TYPE;

    new_salary employee_salary.salary%TYPE;

    BEFORE EACH ROW IS

    BEGIN

        -- Save the old and new salary values before each row is updated

        old_salary := :OLD.salary;

        new_salary := :NEW.salary;

    END BEFORE EACH ROW;

    AFTER EACH ROW IS

    BEGIN

        -- Insert a record into salary_history for each row updated

        INSERT INTO salary_history (change_id, employee_id, old_salary, new_salary, change_date)

        VALUES (seq_salary_history.nextval, :NEW.employee_id, old_salary, new_salary, SYSDATE);

    END AFTER EACH ROW;

END salary_change_trigger;

/

With this compound trigger in place, every time a row is updated in the employee_salary table, the trigger will log the change in the salary_history table, recording the old and new salary values along with the change date.