Interview Questions / PLSQL

What are the Triggers in PLSQL? How do they work?

by

what is Trigger in PLSQL?

  • A trigger is a database object that automatically executes in response to specific events on a table or view.
  • Triggers in PLSQL are implicitly executed when the defined event occurs, such as INSERT, UPDATE, or DELETE operations. Although triggers must be explicitly created, they operate implicitly based on the event conditions specified.
  • Triggers cannot be manually invoked; they are always fired automatically by the database system when the triggering event happens.

Types of Triggers in PLSQL

Triggers are classified into 3 types

  1. DDL Trigger
  2. DML Trigger
    • Statement level
    • Row level trigger
    • Instead of trigger
  3. Database Level Trigger

DDL Trigger :

  • A DDL trigger can be created on any object or the entire schema. After creating this trigger, if any DDL action (such as CREATE, ALTER, DROP, or TRUNCATE) is performed on the object or schema, the trigger will fire, and a raise_application_error will be raised.
  • DDL triggers are useful for enforcing rules or restrictions on DDL operations and for tracking who is performing these operations on the schema. They automatically fire in response to the specified DDL events.

Example: 

CREATE OR REPLACE TRIGGER TRG_DDL
BEFORE DDL ON SCHEMA
BEGIN
    RAISE_APPLICATION_ERROR(-20345, 'YOU CANNOT CREATE/ALTER/TRUNCATE/DROP IN THIS SCHEMA');
END;
Explanation:
  • Trigger Name: TRG_DDL
  • Trigger Timing: BEFORE any DDL operation (CREATE, ALTER, TRUNCATE, DROP)
  • Scope: ON SCHEMA – applies to the entire schema
  • Trigger Body: Raises an application error with the message ‘YOU CANNOT CREATE/ALTER/TRUNCATE/DROP IN THIS SCHEMA’ whenever a DDL operation is attempted.

This trigger will fire before any DDL operation on the schema and will prevent the operation by raising an error with a custom message.If you want to create a trigger for only one or more DDL commands on only one particular object in the schema. It is possible by using pseudo-columns.


1. ora_dict_obj_name
2. ora_dict_obj_type
3. ora_sysevent
4. ora_login_user
5. sysdate

JOINS
Mastering Oracle SQL Joins: The Ultimate Guide to Data Retrieval and Performance


These five are system defined key columns and these are useful when we want to trace
who is doing DDL operations, which action they are doing and on which object they are doing in
the schema .

Example :

 CREATE OR REPLACE TRIGGER TRG_EMP BEFORE DDL ON SCHEMA
BEGIN
IF ORA_DICT_OBJ_NAME='EMP' AND ORA_DICT_OBJ_TYPE='TABLE' AND
ORA_SYSEVENT IN ('DROP','ALTER') THEN RAISE_APPLICATION_ERROR
(-20400,'YOU CANNOT DROP/ALTER ON THIS TABLE');
END IF;
END;

Table of Contents

DML Trigger :

  • A DML trigger is a special type of stored procedure in a database that automatically executes in response to Data Manipulation Language (DML) events such as INSERT, UPDATE, or DELETE operations on a table or view.
  • These triggers are used to enforce business rules, maintain data integrity, and perform complex operations that cannot be achieved with standard constraints.
  • When a DML event occurs, the trigger fires and executes its defined actions, which can include querying other tables, modifying data, or raising custom error messages.
  • DML triggers operate within the transaction that caused the event, allowing for the entire transaction to be rolled back if necessary

Types of DML Triggers in PLSQL

1. Statement Level trigger:

  • A statement-level trigger is used to enforce conditions on DML operations at the statement level, rather than on individual rows.
  • For example, you can use a statement-level trigger to prevent INSERT or DELETE operations on weekends, or to restrict UPDATE operations on Fridays.
  • These triggers are particularly useful for applying rules that affect the entire transaction, ensuring that certain actions are not performed under specified conditions.

Example :

CREATE OR REPLACE TRIGGER TRG_EMP_3 
BEFORE INSERT OR DELETE ON DEPT 
BEGIN 
    IF TO_CHAR(SYSDATE, 'D') = 1 OR TO_CHAR(SYSDATE, 'DD') = 1 THEN 
        RAISE_APPLICATION_ERROR(-20545, 'THESE DAYS YOU CANNOT DELETE/INSERT ON DEPT'); 
    END IF; 
END;

Explanation :

ORACLE
Mastering Oracle Database Architecture: A Comprehensive Overview
  • Trigger Name: TRG_EMP_3
  • Trigger Timing: BEFORE any INSERT or DELETE operation on the DEPT table.
  • Scope: Applies to the entire DEPT table.
  • Trigger Body:
  • Condition: The trigger checks the current date using SYSDATE.
  • TO_CHAR(SYSDATE, 'D') = 1: Checks if the current day is Sunday (assuming the first day of the week is Sunday).
  • TO_CHAR(SYSDATE, 'DD') = 1: Checks if the current day is the 1st of the month.
  • Action: If either condition is true, the trigger raises an application error with the message ‘THESE DAYS YOU CANNOT DELETE/INSERT ON DEPT’.
  • Purpose: This trigger is designed to prevent INSERT or DELETE operations on the DEPT table on Sundays and the 1st day of any month. It enforces a rule that restricts these operations on specific days to maintain data integrity or adhere to business rules.

2.Row Level trigger :

  • A row-level trigger is a type of database trigger that executes automatically for each row affected by a DML operation such as INSERT, UPDATE, or DELETE.
  • Row-level triggers are primarily used for maintaining audits, enforcing rules, or applying restrictions on individual rows.
  • They allow you to capture and log changes to specific rows, making them ideal for detailed auditing and data validation tasks.
Example 1: Rule or Restriction on Each Row
CREATE OR REPLACE TRIGGER TRG_DEPT  
BEFORE DELETE OR INSERT ON DEPT  
FOR EACH ROW 
BEGIN 
    IF :OLD.DEPTNO = 30 THEN  
        DELETE FROM EMP WHERE DEPTNO = :OLD.DEPTNO; 
    ELSIF :OLD.DEPTNO <> 30 THEN 
        RAISE_APPLICATION_ERROR(-20345, 'THIS DEPT TABLE DATA YOU CANNOT DELETE'); 
    ELSIF INSERTING AND TO_CHAR(SYSDATE, 'D') IN (1, 7) THEN 
        RAISE_APPLICATION_ERROR(-20600, 'WEEKENDS YOU CANNOT INSERT ON DEPT'); 
    END IF; 
END;
Explanation :
  • Trigger Name: TRG_DEPT
  • Trigger Timing: BEFORE any DELETE or INSERT operation on the DEPT table.
  • Scope: Applies to each row in the DEPT table.
  • Trigger Body:
  • Condition 1: If the department number (DEPTNO) is 30, delete corresponding rows in the EMP table.
  • Condition 2: If the department number is not 30, raise an application error preventing the delete operation.
  • Condition 3: If an insert operation is attempted on weekends (days 1 and 7), raise an application error preventing the insert.
  • Purpose: This trigger enforces specific rules and restrictions on each row of the DEPT table, ensuring data integrity and compliance with business rules.
Example 2: Trigger for Audit Table Maintenance for DML Operations
CREATE TABLE AUDIT_EMP (
    EMPNO NUMBER, 
    OLD_SAL NUMBER, 
    NEW_SAL NUMBER, 
    OLD_COMM NUMBER, 
    NEW_COMM NUMBER, 
    OLD_JOB VARCHAR2(30), 
    NEW_JOB VARCHAR2(30), 
    ACTION_DATE DATE, 
    ACTION_TYPE VARCHAR2(30)
);
CREATE OR REPLACE TRIGGER TRG_AUDIT_EMP 
AFTER DELETE OR INSERT OR UPDATE ON EMP 
FOR EACH ROW  
DECLARE 
    X NUMBER; 
    Y VARCHAR2(30); 
BEGIN 
    X := CASE WHEN :NEW.EMPNO IS NULL THEN :OLD.EMPNO ELSE :NEW.EMPNO END; 
    Y := CASE WHEN INSERTING THEN 'INSERT' WHEN UPDATING THEN 'UPDATE' ELSE 'DELETE' END; 
    INSERT INTO AUDIT_EMP VALUES (
        X, 
        :OLD.SAL, 
        :NEW.SAL, 
        :OLD.COMM, 
        :NEW.COMM, 
        :OLD.JOB, 
        :NEW.JOB, 
        SYSDATE, 
        Y
    );  
END;
Explanation:
  • Audit Table: AUDIT_EMP is created to store audit information for the EMP table.
  • Trigger Name: TRG_AUDIT_EMP
  • Trigger Timing: AFTER any DELETE, INSERT, or UPDATE operation on the EMP table.
  • Scope: Applies to each row in the EMP table.
  • Trigger Body:
  • Variables:
    • X stores the employee number (EMPNO), using the new value if available, otherwise the old value.
    • Y stores the type of DML operation (INSERT, UPDATE, or DELETE).
    • Action: Inserts a new record into the AUDIT_EMP table with the old and new values of salary (SAL), commission (COMM), and job (JOB), along with the action date and type.
  • Purpose: This trigger maintains an audit trail for each DML operation on the EMP table, capturing changes to key fields and recording the type of operation performed.

3. Instead of Trigger

  • An INSTEAD OF trigger is a type of database trigger that allows you to perform DML operations on complex views that do not normally support such operations.
  • When a DML statement (such as INSERT, UPDATE, or DELETE) is issued on a view with an INSTEAD OF trigger, the trigger executes the specified actions instead of the original DML statement.
  • This type of trigger is particularly useful for enabling DML operations on non-updatable views and avoiding mutating or recursive errors.
Key Points:
  • Purpose: Allows DML operations on complex views.
  • Trigger Timing: Only INSTEAD OF condition, no BEFORE or AFTER.
  • Error Handling: Prevents mutating and recursive errors.
Example 1: Enabling DML Operations on a Complex View
CREATE VIEW VW_EMP_DEPT AS 
SELECT EMPNO, ENAME, JOB, SAL, E.DEPTNO, DNAME, LOC 
FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO;
INSTEAD OF Trigger:
CREATE OR REPLACE TRIGGER TRG_COMPLEX 
INSTEAD OF UPDATE ON VW_EMP_DEPT 
FOR EACH ROW 
BEGIN 
    UPDATE EMP 
    SET ENAME = :NEW.ENAME, EMPNO = :NEW.EMPNO, JOB = :NEW.JOB, SAL = :NEW.SAL, DEPTNO = :NEW.DEPTNO 
    WHERE EMPNO = :OLD.EMPNO;
    UPDATE DEPT 
    SET DNAME = :NEW.DNAME, DEPTNO = :NEW.DEPTNO, LOC = :NEW.LOC  
    WHERE DEPTNO = :OLD.DEPTNO; 
END;
Explanation:
  • View Name: VW_EMP_DEPT
    • Combines data from EMP and DEPT tables.
  • Trigger Name: TRG_COMPLEX
  • Trigger Timing: INSTEAD OF UPDATE on the view VW_EMP_DEPT.
  • Scope: Applies to each row in the view.
  • Trigger Body:
  • First Update: Updates the EMP table with new values for ENAME, EMPNO, JOB, SAL, and DEPTNO where the EMPNO matches the old value.
  • Second Update: Updates the DEPT table with new values for DNAME, DEPTNO, and LOC where the DEPTNO matches the old value.
  • Purpose: This trigger allows updates to be performed on the complex view VW_EMP_DEPT by updating the underlying EMP and DEPT tables accordingly.

Database Level Trigger:

  • A database-level trigger is a type of trigger that is executed automatically in response to specific events that affect the entire database.
  • These triggers are typically used by database administrators (DBAs) to enforce rules, maintain security, and audit activities across the database.
  • For example, database-level triggers can prevent unauthorized schema changes, such as DROP or CREATE operations, and can log user activities, such as login attempts.

Key Points:

  • Purpose: Enforce rules and maintain security across the entire database.
  • Scope: Affects the entire database, not just individual tables or views.
  • Use Cases: Prevent unauthorized schema changes, audit user activities, and enforce database-wide policies.

Example: Auditing User Logins

CREATE TABLE LOGIN_AUDIT (
    USERNAME VARCHAR2(30),
    LOGIN_TIME TIMESTAMP,
    ACTION VARCHAR2(30)
);
CREATE OR REPLACE TRIGGER TRG_LOGIN_AUDIT
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO LOGIN_AUDIT (USERNAME, LOGIN_TIME, ACTION)
    VALUES (USER, SYSTIMESTAMP, 'LOGON');
END;
Explanation:
  • Audit Table: LOGIN_AUDIT is created to store login audit information.
  • Columns:
    • USERNAME: Stores the username of the person logging in.
    • LOGIN_TIME: Records the timestamp of the login.
    • ACTION: Indicates the action performed, in this case, ‘LOGON’.
  • Trigger Name: TRG_LOGIN_AUDIT
  • Trigger Timing: AFTER LOGON on the entire database.
  • Trigger Body:
    • Action: Inserts a new record into the LOGIN_AUDIT table whenever a user logs into the database.
    • Values: Captures the username, current timestamp, and the action ‘LOGON’.
  • Purpose: This trigger audits user logins by recording each login attempt in the LOGIN_AUDIT table, helping DBAs monitor and track access to the database.

Written by

PT Ganesh

An experienced PostgreSQL DBA/SQL developer with years of expertise in designing and implementing robust database solutions. Proficient in writing complex queries, developing stored procedures, and conducting performance tuning to deliver highly efficient data-driven applications that meet business requirements. If any queries feel free to contact us at ganeshpudi12@gmail.com

Read more about PT Ganesh

Leave a Reply

Home
SQL
PLSQL
Interview Q&A