Oracle DML Triggers Faqs With Examples

A DML trigger automatically executes when INSERT, UPDATE, or DELETE occurs on a table or view.

DML triggers are very common in Oracle interviews, especially questions around :OLD, :NEW, row-level vs statement-level, BEFORE vs AFTER, mutating-table errors, and compound triggers.

1. What is a DML trigger?

A DML trigger fires automatically when a DML operation occurs.

DML operations are:

INSERT
UPDATE
DELETE

Example:

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

Whenever:

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

executes, the trigger fires.

2. What is the basic syntax?

Row-level trigger

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

Statement-level trigger

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE
ON table_name
BEGIN
    -- PL/SQL code
END;
/

The key difference is:

FOR EACH ROW

3. What is a row-level trigger?

A row-level trigger fires once for every row affected by the DML statement.

Example:

CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Employee: ' || :NEW.employee_id
    );
END;
/

Suppose:

UPDATE employees
SET salary = salary * 1.10;

If 500 employees are updated, the trigger fires 500 times.

4. What is a statement-level trigger?

A statement-level trigger fires once for the entire SQL statement.

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

If:

UPDATE employees
SET salary = salary * 1.10;

updates 500 rows, the trigger fires only once.

5. Row-level vs statement-level trigger

Feature Row-level Statement-level
FOR EACH ROW Yes No
Fires Once per row Once per statement
:OLD / :NEW Available Not available
Example Audit individual employee Log that an UPDATE occurred
500 rows updated 500 executions 1 execution
Interview shortcut:

FOR EACH ROW = row-level trigger.

6. What are :OLD and :NEW?

They are correlation variables used in row-level DML triggers.

DML :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: ' || :OLD.salary ||
        ' New: ' || :NEW.salary
    );
END;
/

7. What is :NEW used for?

:NEW represents the new value of a column.

For example:

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

Now:

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

stores:

JOHN

instead of:

john

8. Can we modify :NEW?

Yes, in a BEFORE row-level trigger.

Example:

CREATE OR REPLACE TRIGGER trg_default_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary IS NULL THEN
        :NEW.salary := 30000;
    END IF;
END;
/

If the user inserts:

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

the trigger assigns:

salary = 30000

9. Can we modify :OLD?

No.

:OLD represents the value that already exists.

For example, this is invalid:

:OLD.salary := 50000;

You cannot change the old value.

10. Can we modify :NEW in an AFTER trigger?

Generally, no.

If you need to change the incoming value, use a:

BEFORE INSERT
BEFORE UPDATE

row-level trigger.

Example:

CREATE OR REPLACE TRIGGER trg_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.salary := ROUND(:NEW.salary);
END;
/

11. What is a BEFORE trigger?

A BEFORE trigger executes before the DML operation.

Example:

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;
/

This is useful for:

  • Validation
  • Modifying :NEW
  • Setting default values

12. What is an AFTER trigger?

An AFTER trigger executes after the DML operation.

Example:

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

This is commonly used for:

  • Auditing
  • Logging
  • Maintaining related information

13. BEFORE vs AFTER trigger

BEFORE AFTER
Executes before DML Executes after DML
Can modify :NEW Cannot modify :NEW
Good for validation Good for auditing
Good for assigning values Good for logging successful changes
Easy way to remember:

BEFORE → Check / Change
AFTER → Record / React

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

Yes.

CREATE OR REPLACE TRIGGER trg_emp_changes
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('INSERT occurred');

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

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

Oracle provides:

INSERTING
UPDATING
DELETING

to determine which operation caused the trigger.

15. What are INSERTING, UPDATING, and DELETING?

They are conditional predicates used inside DML triggers.

Example:

IF INSERTING THEN
    ...
ELSIF UPDATING THEN
    ...
ELSIF DELETING THEN
    ...
END IF;

You can also check a particular column:

IF UPDATING('SALARY') THEN
    ...
END IF;

16. How do I create an audit trigger?

Suppose:

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

Trigger:

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

    IF INSERTING THEN

        INSERT INTO employee_audit
        VALUES (
            :NEW.employee_id,
            NULL,
            :NEW.salary,
            'INSERT',
            USER,
            SYSDATE
        );

    ELSIF UPDATING THEN

        INSERT INTO employee_audit
        VALUES (
            :NEW.employee_id,
            :OLD.salary,
            :NEW.salary,
            'UPDATE',
            USER,
            SYSDATE
        );

    ELSIF DELETING THEN

        INSERT INTO employee_audit
        VALUES (
            :OLD.employee_id,
            :OLD.salary,
            NULL,
            'DELETE',
            USER,
            SYSDATE
        );

    END IF;

END;
/

This is a very common interview example.

17. How do I prevent deleting an employee?

CREATE OR REPLACE TRIGGER trg_no_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(
        -20001,
        'Employee deletion is not allowed'
    );
END;
/

Now:

DELETE FROM employees
WHERE employee_id = 101;

fails.

18. How do I prevent salary reduction?

CREATE OR REPLACE TRIGGER trg_no_salary_reduction
BEFORE UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(
            -20002,
            'Salary cannot be reduced'
        );
    END IF;
END;
/

Example:

UPDATE employees
SET salary = 40000
WHERE employee_id = 101;

If the old salary was 50000, the trigger raises an error.

19. How do I automatically update modified_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 the modification timestamp.

20. How do I automatically set created_date?

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

However, for a simple default value, a column definition is often preferable:

created_date DATE DEFAULT SYSDATE

21. What is a conditional DML trigger?

A trigger that handles different logic depending on the DML operation.

Example:

CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        -- INSERT logic

    ELSIF UPDATING THEN
        -- UPDATE logic

    ELSIF DELETING THEN
        -- DELETE logic
    END IF;
END;
/

22. Can I create a trigger for a specific column?

Yes, particularly for UPDATE.

CREATE OR REPLACE TRIGGER trg_salary_update
BEFORE UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Salary is being updated');
END;
/

This trigger fires for an UPDATE statement involving the salary column.

You can also use:

IF UPDATING('SALARY') THEN
    ...
END IF;

23. What is the difference between UPDATE OF salary and UPDATING('salary')?

UPDATE OF salary

Specified in the trigger definition:

BEFORE UPDATE OF salary ON employees

It limits the trigger event to updates involving that column.

UPDATING('SALARY')

Used inside a trigger:

IF UPDATING('SALARY') THEN
    ...
END IF;

It lets you conditionally execute code based on the column.

24. What is a mutating-table error?

One of the most important Oracle trigger interview questions.

Suppose:

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;
/

When employees is being modified row-by-row, the trigger attempts to query the same table.

Oracle may raise:

ORA-04091: table EMPLOYEES is mutating

This is called a mutating-table error.

25. Why does a mutating-table error occur?

During a row-level trigger, Oracle is in the middle of modifying the table.

The table is considered to be in a changing state.

Trying to query that same table from the row-level trigger can produce inconsistent results, so Oracle prevents the operation.

Interview answer:

A mutating-table error occurs when a row-level trigger tries to query or modify the table that caused the trigger to fire.

26. How can we solve a mutating-table problem?

Common solutions include:

  • Compound trigger
  • Statement-level processing
  • Package variables with multiple trigger sections — older approach
  • Redesigning the logic
  • Moving business logic outside the trigger where appropriate

A compound trigger is generally the modern approach.

27. What is a compound trigger?

A compound trigger allows multiple timing sections in a single trigger.

Example structure:

CREATE OR REPLACE TRIGGER trg_emp
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER

    BEFORE STATEMENT IS
    BEGIN
        NULL;
    END BEFORE STATEMENT;

    AFTER EACH ROW IS
    BEGIN
        NULL;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        NULL;
    END AFTER STATEMENT;

END;
/

It can maintain state between the different timing sections.

28. What is the difference between row-level and compound triggers?

A compound trigger is not simply another "level" of trigger.

Think of it as a trigger that can contain multiple timing points:

BEFORE STATEMENT
       ↓
BEFORE EACH ROW
       ↓
AFTER EACH ROW
       ↓
AFTER STATEMENT

This makes it especially useful for complex DML processing and mutating-table scenarios.

29. Can a DML trigger contain COMMIT?

Normally, no.

This is invalid:

CREATE OR REPLACE TRIGGER trg_test
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit VALUES (...);

    COMMIT;
END;
/

Oracle can raise:

ORA-04092: cannot COMMIT in a trigger

The trigger is part of the transaction that caused it to execute.

30. Can a trigger call a procedure?

Yes.

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

For complex logic, keeping reusable code in a procedure/package can make the trigger easier to maintain.

31. What happens if the trigger fails?

Suppose:

CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(
        -20001,
        'Insert not allowed'
    );
END;
/

Then:

INSERT INTO employees VALUES (...);

fails.

The DML operation does not successfully complete.

This is why triggers can be used for validation and enforcement.

32. Can a trigger cause another trigger to fire?

Yes.

For example:

UPDATE EMPLOYEES
      ↓
Trigger A
      ↓
UPDATE DEPARTMENTS
      ↓
Trigger B

This is called cascading trigger execution.

Too many cascading triggers can make an application difficult to debug.

33. How do I disable a trigger?

Disable:

ALTER TRIGGER trg_emp DISABLE;

Enable:

ALTER TRIGGER trg_emp ENABLE;

For all triggers on a table:

ALTER TABLE employees DISABLE ALL TRIGGERS;

and:

ALTER TABLE employees ENABLE ALL TRIGGERS;

34. How do I find triggers on a table?

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

Useful columns include:

TRIGGER_NAME
TRIGGER_TYPE
TRIGGERING_EVENT
TABLE_NAME
STATUS

35. How do I drop a trigger?

DROP TRIGGER trg_emp;

36. DML trigger vs constraint

Suppose you want:

Salary should never be negative.

A trigger can do this:

CREATE OR REPLACE TRIGGER trg_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Invalid salary'
        );
    END IF;
END;
/

But a constraint is simpler:

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

If a requirement can be handled cleanly with a constraint, prefer the constraint over a trigger.

37. When should we use DML triggers?

Good use cases include:

  • Auditing changes
  • Maintaining history tables
  • Validating complex business rules
  • Automatically setting or modifying values
  • Recording who changed data and when
  • Maintaining related information
  • Enforcing rules that cannot be handled cleanly by constraints

⭐ Most Important DML Trigger Interview Questions

Q1. What is a DML trigger?

A trigger that automatically fires when INSERT, UPDATE, or DELETE occurs.

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

FOR EACH ROW
      ↓
Row-level trigger
      ↓
Fires once for every affected row

No FOR EACH ROW
      ↓
Statement-level trigger
      ↓
Fires once for the entire statement

Q3. What are :OLD and :NEW?

They are correlation variables available in row-level DML triggers.

INSERT → :NEW
UPDATE → :OLD and :NEW
DELETE → :OLD

Q4. Can we modify :NEW?

Yes, in a BEFORE row-level trigger.

Q5. Can we modify :OLD?

No. :OLD represents the existing value.

Q6. What is a mutating-table error?

It occurs when a row-level trigger tries to query or modify the same table that caused the trigger to fire.

ORA-04091: table EMPLOYEES is mutating

Q7. How can you solve a mutating-table problem?

Common solutions include:

  • Compound trigger
  • Statement-level processing
  • Package variables
  • Redesigning the logic

Q8. Can a trigger contain COMMIT?

No. Normal transaction control such as COMMIT is not allowed inside a trigger.

Q9. What is the difference between BEFORE and AFTER?

BEFORE → Validate / Change
AFTER  → Audit / Record / React

Q10. What are INSERTING, UPDATING and DELETING?

They are conditional predicates used to determine which DML operation caused the trigger.

IF INSERTING THEN
    ...
ELSIF UPDATING THEN
    ...
ELSIF DELETING THEN
    ...
END IF;

🔥 One Interview Scenario Worth Practicing

Requirement:

Every salary change must be audited, salary should never be reduced, and the employee's modified_date should automatically update whenever the employee record changes.

You could implement salary validation with:

CREATE OR REPLACE TRIGGER trg_no_salary_reduction
BEFORE UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(
            -20002,
            'Salary cannot be reduced'
        );
    END IF;
END;
/

And separately implement an audit trigger:

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

    IF INSERTING THEN

        INSERT INTO employee_audit
        VALUES (
            :NEW.employee_id,
            NULL,
            :NEW.salary,
            'INSERT',
            USER,
            SYSDATE
        );

    ELSIF UPDATING THEN

        INSERT INTO employee_audit
        VALUES (
            :NEW.employee_id,
            :OLD.salary,
            :NEW.salary,
            'UPDATE',
            USER,
            SYSDATE
        );

    ELSIF DELETING THEN

        INSERT INTO employee_audit
        VALUES (
            :OLD.employee_id,
            :OLD.salary,
            NULL,
            'DELETE',
            USER,
            SYSDATE
        );

    END IF;

END;
/
Key Interview Takeaway:

A DML trigger automatically fires for INSERT, UPDATE, or DELETE. Row-level triggers use FOR EACH ROW and provide :OLD and :NEW. BEFORE triggers are commonly used for validation and changing :NEW, while AFTER triggers are commonly used for auditing and logging. Always remember the mutating-table problem and the role of compound triggers in solving complex row-level processing.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)