Oracle Triggers FAQs with Examples

1. What is a trigger in Oracle?

A trigger is a PL/SQL program that automatically executes when a specified event occurs on a table, view, schema, or database.

Common events:

  • INSERT
  • UPDATE
  • DELETE
  • DDL events such as CREATE, ALTER, DROP
  • Database events such as LOGON, STARTUP

2. What is the basic syntax?

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- PL/SQL statements
END;
/

Example:

CREATE OR REPLACE TRIGGER trg_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee inserted');
END;
/

3. What is the difference between BEFORE and AFTER triggers?

BEFORE triggers execute before the DML operation.

CREATE OR REPLACE TRIGGER trg_emp_before
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_date := SYSDATE;
END;
/

AFTER triggers execute after the DML operation.

CREATE OR REPLACE TRIGGER trg_emp_after
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log(emp_id, log_date)
    VALUES (:NEW.employee_id, SYSDATE);
END;
/

A common rule of thumb:

  • Use BEFORE when you need to validate or modify :NEW values.
  • Use AFTER when you want to perform an action after the row has been successfully changed.

4. What are :NEW and :OLD?

They are correlation variables used inside row-level triggers.

Operation :OLD :NEW
INSERT ❌ ✅
UPDATE ✅ ✅
DELETE ✅ ❌

Example:

CREATE OR REPLACE TRIGGER trg_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Old salary: ' || :OLD.salary ||
        ', New salary: ' || :NEW.salary
    );
END;
/

5. Can a trigger modify :NEW?

Yes, in a BEFORE row-level trigger.

CREATE OR REPLACE TRIGGER trg_upper_name
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.employee_name := UPPER(:NEW.employee_name);
END;
/

If you execute:

INSERT INTO employees(employee_id, employee_name)
VALUES (101, 'john');

The stored name becomes:

JOHN

You generally cannot assign to :NEW in an AFTER trigger.

6. What is a row-level trigger?

A row-level trigger executes once for every affected row.

It is identified by:

FOR EACH ROW

Example:

CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log
    VALUES (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        SYSDATE
    );
END;
/

If an update affects 100 employees, the trigger executes 100 times.

7. What is a statement-level trigger?

A statement-level trigger executes once for the entire SQL statement, regardless of how many rows are affected.

CREATE OR REPLACE TRIGGER trg_emp_statement
AFTER UPDATE ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee table updated');
END;
/

For:

UPDATE employees
SET salary = salary * 1.10;

Even if 1,000 rows are updated, the trigger executes once.

8. What is the difference between row-level and statement-level triggers?

Feature Row-level Statement-level
Syntax FOR EACH ROW No FOR EACH ROW
Execution Once per row Once per statement
:OLD / :NEW Available Not available
Best for Row-specific logic Statement-level actions

9. Can one trigger handle INSERT, UPDATE, and DELETE?

Yes.

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN

    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('INSERT');

    ELSIF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('UPDATE');

    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('DELETE');
    END IF;

END;
/

Oracle provides the following conditional predicates:

  • INSERTING
  • UPDATING
  • DELETING

These determine which event fired the trigger.

10. How do I prevent an invalid salary?

CREATE OR REPLACE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary cannot be negative'
        );
    END IF;
END;
/

Now:

INSERT INTO employees(employee_id, salary)
VALUES (101, -5000);

produces an application error.

11. How do I automatically set a creation date?

CREATE OR REPLACE TRIGGER trg_created_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_date := SYSDATE;
END;
/

This is a classic trigger use case.

However, for simple default values, a column DEFAULT is often preferable:

created_date DATE DEFAULT SYSDATE

12. How can I automatically update a modification date?

CREATE OR REPLACE TRIGGER trg_modified_date
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.modified_date := SYSDATE;
END;
/

Every update automatically changes modified_date.

13. How do I create an audit trigger?

Suppose:

CREATE TABLE employee_audit (
    employee_id NUMBER,
    old_salary  NUMBER,
    new_salary  NUMBER,
    changed_by  VARCHAR2(100),
    changed_on  DATE
);

Trigger:

CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        changed_on
    )
    VALUES
    (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        USER,
        SYSDATE
    );
END;
/

Now a salary change automatically creates an audit record.

14. Can a trigger call a procedure?

Yes.

CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    send_employee_notification(:NEW.employee_id);
END;
/

This is often better than putting a large amount of business logic directly inside the trigger.

15. Can a trigger contain a COMMIT?

Normally, no.

This is invalid in a normal trigger:

BEGIN
    INSERT INTO employee_log VALUES (...);
    COMMIT;
END;

You will encounter:

ORA-04092: cannot COMMIT in a trigger

The trigger runs as part of the transaction that fired it.

16. What is a mutating table error?

A common Oracle trigger problem is:

ORA-04091: table ... is mutating

This can happen when a row-level trigger tries to query or modify the same table that caused the trigger to fire.

Example:

CREATE OR REPLACE TRIGGER trg_emp
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees;
END;
/

If the trigger fires because of an update to employees, querying employees from the row-level trigger can cause a mutating-table error.

17. How can I solve a mutating-table problem?

A compound trigger is one modern solution.

Conceptually:

CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN
        -- Collect row information
        NULL;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- Query employees here
        NULL;
    END AFTER STATEMENT;

END;
/

The important idea is to collect information at the row level and perform table-level processing in the AFTER STATEMENT section.

18. What is a compound trigger?

A compound trigger allows multiple timing sections within one trigger.

It can contain sections such as:

  • BEFORE STATEMENT
  • BEFORE EACH ROW
  • AFTER EACH ROW
  • AFTER STATEMENT

It is particularly useful for:

  • Avoiding mutating-table problems
  • Sharing state between timing sections
  • Bulk processing

19. Can I disable a trigger?

Yes.

ALTER TRIGGER trg_emp DISABLE;

Enable it again:

ALTER TRIGGER trg_emp ENABLE;

For all triggers on a table:

ALTER TABLE employees DISABLE ALL TRIGGERS;

And:

ALTER TABLE employees ENABLE ALL TRIGGERS;

20. How do I drop a trigger?

DROP TRIGGER trg_emp;

21. How do I check whether a trigger is enabled?

Query:

SELECT trigger_name,
       status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

Possible status:

  • ENABLED
  • DISABLED

22. Can a trigger be created on a view?

Yes. Oracle supports INSTEAD OF triggers on views.

Example:

CREATE OR REPLACE TRIGGER trg_view_insert
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
BEGIN
    INSERT INTO employees(employee_id, employee_name)
    VALUES (:NEW.employee_id, :NEW.employee_name);
END;
/

Instead of Oracle directly performing the DML against the view, the trigger defines what should happen.

23. What is an INSTEAD OF trigger?

An INSTEAD OF trigger tells Oracle:

"When someone performs this operation on the view, execute this logic instead."

It is primarily useful for making complex or non-updatable views behave as though they can accept DML.

24. Can triggers fire other triggers?

Yes.

For example:

INSERT into EMPLOYEES
      ↓
Trigger A fires
      ↓
Trigger A updates DEPARTMENTS
      ↓
Trigger B fires

This is called cascading trigger execution.

Be careful because excessive trigger chaining can make application behavior difficult to understand and debug.

25. What is a trigger vs a stored procedure?

Trigger Procedure
Executes automatically Called explicitly
Associated with an event/object Independent program
Usually responds to DML/DDL/database events Performs business/application logic
Cannot normally accept parameters like a procedure Can accept parameters
Useful for auditing/validation Useful for reusable business logic

26. Trigger vs constraint — which should I use?

Prefer a constraint when the requirement can be expressed naturally as a constraint.

For example, instead of:

CREATE TRIGGER ...
IF salary < 0 THEN ...

prefer:

ALTER TABLE employees
ADD CONSTRAINT chk_salary
CHECK (salary >= 0);

Constraints are generally clearer and are enforced directly by Oracle.

Use triggers when you need behavior that constraints cannot easily provide, such as:

  • Audit logging
  • Automatically maintaining related information
  • Complex event-based processing
  • INSTEAD OF behavior for views

⭐ Common Oracle Trigger Interview Questions

  1. What is a trigger?
  2. What are BEFORE and AFTER triggers?
  3. What is a row-level trigger?
  4. What is a statement-level trigger?
  5. What are :OLD and :NEW?
  6. Can we modify :NEW in an AFTER trigger?
  7. What is an INSTEAD OF trigger?
  8. What is a mutating-table error?
  9. How do you solve a mutating-table problem?
  10. What is a compound trigger?
  11. Can a trigger contain COMMIT?
  12. Can a trigger call a procedure?
  13. Can one trigger handle INSERT/UPDATE/DELETE?
  14. How do you disable and enable a trigger?
  15. How do you find all triggers on a table?
  16. What is cascading trigger execution?
  17. Trigger vs procedure?
  18. Trigger vs constraint?
  19. When should you avoid triggers?
  20. How do you create an audit trigger?

A Good Interview Example to Remember

CREATE OR REPLACE TRIGGER trg_employee_audit
AFTER UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        changed_on
    )
    VALUES
    (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        USER,
        SYSDATE
    );
END;
/
Interview explanation:
"This is an AFTER row-level trigger. It fires whenever an employee's salary is updated. :OLD gives the previous salary and :NEW gives the new salary, and the trigger stores the change in an audit table."

Key Takeaways

  • BEFORE: Use when you need to validate or modify incoming values.
  • AFTER: Use for auditing and follow-up actions.
  • Row-level: Executes once for every affected row.
  • Statement-level: Executes once for the entire SQL statement.
  • :OLD: Represents the previous value.
  • :NEW: Represents the new value.
  • Compound trigger: Useful for combining row-level and statement-level processing.
  • INSTEAD OF: Commonly used with views.
  • ORA-04091: Indicates a mutating-table problem.
  • Constraints: Prefer them when the rule can naturally be enforced using a constraint.
```

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)