Oracle Row Level Trigger Faqs With Examples

1. What is a row-level trigger?

A row-level trigger executes once for each row affected by an INSERT, UPDATE, or DELETE.

It is created using:

FOR EACH ROW

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

If an UPDATE changes 10 employees, this trigger fires 10 times.

Frequently Asked Questions

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

Feature Statement-level Row-level
FOR EACH ROW No Yes
Fires Once per SQL statement Once per affected row
:OLD, :NEW Not available Available
Example Update 100 rows → 1 firing Update 100 rows → 100 firings

Statement-level

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('Update statement executed');
END;
/

Row-level

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('One employee updated');
END;
/

3. What are :OLD and :NEW?

They represent the values of a row before and after the DML operation.

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

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

    DBMS_OUTPUT.PUT_LINE(
        'New Salary = ' || :NEW.salary
    );
END;
/

4. Can we modify :NEW?

Yes, in a BEFORE row-level trigger.

This is commonly used for defaulting or transforming values.

CREATE OR REPLACE TRIGGER trg_emp_upper
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.first_name := UPPER(:NEW.first_name);
    :NEW.last_name  := UPPER(:NEW.last_name);
END;
/

If you execute:

INSERT INTO employees
       (employee_id, first_name, last_name)
VALUES (101, 'john', 'smith');

The stored values become:

JOHN
SMITH

Important:

You cannot modify :OLD.

:OLD.salary := 50000;   -- ❌ Invalid

5. Can we use :NEW in a DELETE trigger?

No.

For DELETE:

:OLD → available
:NEW → not available

Example:

CREATE OR REPLACE TRIGGER trg_emp_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Deleting employee: ' || :OLD.employee_id
    );
END;
/

6. Can we use :OLD during INSERT?

No.

For INSERT:

:NEW → available
:OLD → not available

Example:

CREATE OR REPLACE TRIGGER trg_emp_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'New employee: ' || :NEW.employee_id
    );
END;
/

7. What are BEFORE and AFTER row-level triggers?

BEFORE trigger

Runs before the row is inserted/updated/deleted.

Useful for:

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

AFTER trigger

Runs after the row operation.

Useful for:

  • Auditing
  • Logging
  • Recording changes
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit
    VALUES (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        SYSDATE
    );
END;
/

8. What does UPDATE OF salary mean?

It means the trigger fires only when the salary column is included in the UPDATE statement.

CREATE OR REPLACE TRIGGER trg_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Salary update detected');
END;
/

This fires for:

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

But it doesn't fire for:

UPDATE employees
SET department_id = 20
WHERE employee_id = 101;
Interview trap:

UPDATE OF salary means the column is referenced in the UPDATE statement. It doesn't necessarily mean the salary value actually changed.

For example:

UPDATE employees
SET salary = salary
WHERE employee_id = 101;

The trigger can still fire.

9. How do I check whether a value actually changed?

Use :OLD and :NEW.

CREATE OR REPLACE TRIGGER trg_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :OLD.salary != :NEW.salary THEN
        DBMS_OUTPUT.PUT_LINE('Salary actually changed');
    END IF;
END;
/

But there's a NULL issue with !=.

A safer comparison is:

IF (:OLD.salary != :NEW.salary)
   OR (:OLD.salary IS NULL AND :NEW.salary IS NOT NULL)
   OR (:OLD.salary IS NOT NULL AND :NEW.salary IS NULL)
THEN
    DBMS_OUTPUT.PUT_LINE('Salary changed');
END IF;

10. How do I prevent an employee's salary from decreasing?

A row-level BEFORE UPDATE trigger is a common example.

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

Now:

UPDATE employees
SET salary = 40000
WHERE employee_id = 101;

will fail if the old salary was 50000.

11. How do I automatically set created_date?

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

Now:

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

automatically gets the current date.

Better modern alternative

If you're designing a new table, a column DEFAULT is often preferable:

created_date DATE DEFAULT SYSDATE

Use triggers when the logic genuinely requires procedural behavior.

12. How do I maintain updated_date?

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

Every updated row gets a new timestamp.

13. Can a trigger call a procedure?

Yes.

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

Where:

CREATE OR REPLACE PROCEDURE audit_employee(
    p_employee_id NUMBER
)
IS
BEGIN
    INSERT INTO employee_log(employee_id, log_date)
    VALUES (p_employee_id, SYSDATE);
END;
/

This is useful when trigger logic becomes large, although you should avoid putting excessive business logic into triggers.

14. What is a mutating-table error?

One of the most important row-trigger interview questions.

Suppose you have:

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

This can cause:

ORA-04091: table EMPLOYEES is mutating,
trigger/function may not see it

Why?

A row-level trigger is executing while Oracle is modifying the EMPLOYEES table.

Trying to query the same table from that row-level trigger can cause the mutating-table problem.

15. How do we solve a mutating-table problem?

Modern Oracle versions provide several approaches, including compound triggers.

Example:

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
        -- safely query EMPLOYEES here
        NULL;
    END AFTER STATEMENT;

END;
/

The important idea is:

Row section
    ↓
collect information

Statement section
    ↓
query/process the table

This separates row-level collection from statement-level processing.

16. What happens if one row fails in a row-level trigger?

Suppose:

UPDATE employees
SET salary = salary + 1000;

affects 100 rows.

If your trigger raises an exception for one of those rows and the exception propagates, the SQL statement fails, and Oracle normally rolls back the statement's changes.

Example:

CREATE OR REPLACE TRIGGER trg_test
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary > 100000 THEN
        RAISE_APPLICATION_ERROR(
            -20003,
            'Salary too high'
        );
    END IF;
END;
/

If the update tries to create an invalid row, the statement fails rather than simply skipping that row.

17. Can a trigger commit?

Normally, no.

You cannot do:

CREATE OR REPLACE TRIGGER trg_test
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    COMMIT;  -- ❌ ORA-04092
END;
/

A trigger executes as part of the transaction that caused it.

18. Can a trigger perform DML on another table?

Yes.

For example:

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_deleted_log
    (
        employee_id,
        deleted_date
    )
    VALUES
    (
        :OLD.employee_id,
        SYSDATE
    );
END;
/

This is a common auditing pattern.

19. Can a row-level trigger be used for auditing?

Yes.

Example:

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

Trigger:

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

20. What is the difference between :NEW and NEW?

Inside PL/SQL trigger code, you normally use:

:NEW.column_name
:OLD.column_name

The colon indicates a correlation variable.

Example:

IF :NEW.salary > 100000 THEN
    ...
END IF;

21. Can we use WHEN with a row-level trigger?

Yes.

For example:

CREATE OR REPLACE TRIGGER trg_high_salary
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (NEW.salary > 100000)
BEGIN
    INSERT INTO high_salary_log(employee_id)
    VALUES (:NEW.employee_id);
END;
/

A useful distinction:

Inside the WHEN clause, you generally reference the correlation name without the colon:

WHEN (NEW.salary > 100000)

Inside PL/SQL:

:NEW.salary

22. What are the valid timing/event combinations?

Common combinations are:

BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE

AFTER INSERT
AFTER UPDATE
AFTER DELETE

You can combine events:

BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW

You can also restrict an update trigger:

BEFORE UPDATE OF salary, department_id ON employees
FOR EACH ROW

23. 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('Inserted');
    ELSIF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('Updated');
    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('Deleted');
    END IF;

END;
/

Oracle provides:

INSERTING
UPDATING
DELETING

These are extremely useful in multi-event triggers.

24. What is a good interview example?

Requirement

Whenever an employee's salary changes, record the old salary, new salary, employee ID, user and timestamp.

Solution

CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN

    IF (:OLD.salary != :NEW.salary)
       OR (:OLD.salary IS NULL AND :NEW.salary IS NOT NULL)
       OR (:OLD.salary IS NOT NULL AND :NEW.salary IS NULL)
    THEN

        INSERT INTO salary_audit
        (
            employee_id,
            old_salary,
            new_salary,
            changed_by,
            changed_on
        )
        VALUES
        (
            :OLD.employee_id,
            :OLD.salary,
            :NEW.salary,
            SYS_CONTEXT('USERENV', 'SESSION_USER'),
            SYSTIMESTAMP
        );

    END IF;

END;
/

This demonstrates several important concepts:

  • Row-level trigger
  • AFTER UPDATE
  • UPDATE OF
  • :OLD
  • :NEW
  • NULL-safe comparison
  • Audit logging
  • Current database user
  • Timestamp

⭐ Quick interview cheat sheet

Remember this table:

Operation :OLD :NEW
INSERT ❌ ✅
UPDATE ✅ ✅
DELETE ✅ ❌ 
FOR EACH ROW
     ↓
Row-level trigger

No FOR EACH ROW
     ↓
Statement-level trigger

BEFORE → validate/modify :NEW

AFTER → audit/log after the operation

:OLD → previous value

:NEW → new value

INSERTING → INSERT event

UPDATING → UPDATE event

DELETING → DELETE event

Mutating table → avoid querying the table currently being changed from its row-level trigger; consider a compound trigger.
If you're preparing for an Oracle interview, the next-level questions are usually around mutating-table errors, compound triggers, autonomous transactions, trigger firing order, recursive triggers, and :OLD/:NEW edge cases.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)