Statement Level Trigger FAQS

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

A statement-level trigger is a trigger that executes once per DML statement, regardless of how many rows are affected.

Example:

·        If an UPDATE modifies 1 row → fires once

·        If an UPDATE modifies 10,000 rows → still fires once

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

Statement-Level Trigger	Row-Level Trigger
Fires once per statement	Fires once per row
Cannot access individual row data	Can access :NEW and :OLD
Better for bulk logic	Better for per-row validation

3. Can statement-level triggers access :NEW and :OLD?

No.

Statement-level triggers do not have access to:

·        :NEW

·        :OLD

Because they are not tied to individual rows.

4. When should you use a statement-level trigger?

Use them when you need to:

·        Log that a DML operation occurred

·        Restrict entire operations

·        Enforce global business rules

·        Perform actions before/after bulk processing

·        Prevent DELETE/UPDATE during certain conditions

5. Do statement-level triggers cause mutating table errors?

No.

Mutating table errors occur mainly in row-level triggers.
Statement-level triggers can safely query the triggering table.

6. Are statement-level triggers better for performance?

Generally yes, because:

·        They execute only once

·        They avoid per-row overhead

They are better suited for bulk operations.

7. Can a statement-level trigger prevent a DML operation?

Yes.

If the trigger raises an exception:

·        The entire statement fails

·        No rows are modified

8. Can statement-level triggers commit or rollback?

No.

Like all triggers, they run within the same transaction as the triggering statement and cannot explicitly commit or rollback.

9. What are common real-world use cases?

·        Logging mass updates

·        Blocking weekend transactions

·        Restricting DELETE on critical tables

·        Auditing bulk data changes

·        Checking system conditions before allowing changes

10. Can multiple statement-level triggers exist on the same table?

Yes.

Oracle allows multiple triggers for the same event.
Execution order is not guaranteed unless explicitly defined.

11. Can statement-level triggers be defined for multiple events?

Yes.

They can fire on:

·        INSERT

·        UPDATE

·        DELETE

·        Or a combination of these

12. Can statement-level triggers be disabled?

Yes.

They can be enabled or disabled without dropping them, useful during:

·        Data migration

·        Batch processing

·        Maintenance

13. Can statement-level triggers call stored procedures?

Yes.

Best practice:

·        Keep the trigger simple

·        Call a procedure or package for complex logic

14. What are the limitations of statement-level triggers?

·        No access to individual row values

·        Cannot modify specific row data

·        Less useful for column-level validation

15. When should you avoid statement-level triggers?

Avoid them when:

·        You need per-row validation

·        You must compare old vs new values

·        You need to update specific column values