Row-Level Trigger FAQS

1. What is a row-level trigger in Oracle?

A row-level trigger is a trigger that executes once for each row affected by a DML statement (INSERT, UPDATE, or DELETE).

If an UPDATE statement modifies 100 rows, the row-level trigger fires 100 times.

2. How is a row-level trigger different from a statement-level trigger?

·        Row-level trigger → Fires once per affected row

·        Statement-level trigger → Fires once per SQL statement

Row-level triggers allow access to individual row values.

3. When should you use a row-level trigger?

Use row-level triggers when you need to:

·        Validate individual row data

·        Compare old and new values

·        Maintain audit logs

·        Automatically modify column values

·        Enforce complex per-row business rules

4. What are :NEW and :OLD in row-level triggers?

They are pseudo-records available only in row-level triggers:

·        :NEW → The new column values (for INSERT and UPDATE)

·        :OLD → The old column values (for UPDATE and DELETE)

Availability:

Operation	:NEW	:OLD
INSERT	Yes	No
UPDATE	Yes	Yes
DELETE	No	Yes

5. Can a row-level trigger modify :NEW values?

Yes, but only in a BEFORE trigger.

You cannot modify :NEW values in an AFTER trigger.

6. What is a mutating table error in row-level triggers?

A mutating table error occurs when the trigger tries to:

·        Query the same table that fired the trigger

·        Modify the same table

Oracle prevents this to avoid data inconsistency during row processing.

7. Why do mutating table errors occur mostly in row-level triggers?

Because row-level triggers fire while the table is still being modified row by row.

At that moment, the table is considered unstable for querying.

8. How can mutating table errors be avoided?

Common approaches:

·        Use compound triggers

·        Use statement-level triggers

·        Store values in collections and process them later

·        Move logic to a package

9. Do row-level triggers affect performance?

Yes.

Since they execute once per row:

·        Large updates can cause thousands of trigger executions

·        Poorly written logic can slow down DML operations significantly

Best practice: Keep logic lightweight.

10. Can a row-level trigger perform COMMIT or ROLLBACK?

No.
Triggers run inside the same transaction as the triggering statement.

11. Can row-level triggers call procedures?

Yes.

It is recommended to:

·        Keep trigger small

·        Call a stored procedure for complex logic

12. What happens if a row-level trigger raises an exception?

·        The triggering DML statement fails

·        The entire transaction is rolled back (unless handled)

13. Can multiple row-level triggers exist on the same table?

Yes.

Oracle allows multiple triggers for:

·        The same event

·        Different events

Execution order is not guaranteed unless explicitly defined.

14. Can row-level triggers be disabled?

Yes.

They can be enabled or disabled without dropping them, which is useful during bulk operations.

15. What are common real-world use cases?

·        Audit logging (who changed what and when)

·        Preventing salary reduction below threshold

·        Auto-updating timestamps

·        Enforcing cross-column validation

·        Maintaining history tables

16. What are best practices for row-level triggers?

·        Keep logic minimal

·        Avoid querying the same table

·        Use BEFORE triggers for validation

·        Avoid complex business workflows

·        Document trigger purpose clearly