Compound Trigger FAQS

1. What is a compound trigger in Oracle?

A compound trigger is a single trigger that can contain multiple timing sections for the same table and DML event.

It allows you to define logic for:

·        Before statement

·        Before each row

·        After each row

·        After statement

All within one trigger body.

2. Why were compound triggers introduced?

Compound triggers were introduced to:

·        Avoid mutating table errors

·        Improve performance in bulk DML

·        Share data between row-level and statement-level logic

·        Reduce multiple trigger definitions on the same table

3. On which objects can compound triggers be created?

Compound triggers can be created only on:

·        Tables

·        Views (with limitations)

They are commonly used on tables.

4. What problem does a compound trigger solve?

The main problem it solves is:

Mutating table error

In regular row-level triggers, querying the same table causes errors.
Compound triggers allow collecting row data and processing it later in the statement-level section.

5. How is a compound trigger different from normal triggers?

Normal Trigger	Compound Trigger
Single timing point	Multiple timing points
Separate triggers for each timing	All sections in one trigger
No shared variables across triggers	Shared variables across sections

6. Can compound triggers share variables?

Yes.

Variables declared in the declaration section are shared across:

·        Before statement

·        Before each row

·        After each row

·        After statement

This makes bulk processing efficient.

7. When should you use a compound trigger?

Use compound triggers when:

·        You need both row-level and statement-level logic

·        You want to prevent mutating table errors

·        You need to collect row data and process it once

·        You want cleaner and centralized trigger logic

8. Do compound triggers improve performance?

Yes, especially for bulk operations.

Instead of executing heavy logic per row:

·        Collect data per row

·        Process it once in the after statement section

This reduces context switching and improves efficiency.

9. Can compound triggers access :NEW and :OLD?

Yes.

In row-level sections:

·        :NEW and :OLD are available

In statement-level sections:

·        They are not available

10. Can compound triggers perform COMMIT or ROLLBACK?

No.

Like all triggers, they execute within the same transaction.

11. Do compound triggers eliminate mutating table errors completely?

They significantly reduce the risk when used properly.

However:

·        Poor design can still cause logical issues

·        Proper separation of row and statement logic is required

12. Can multiple compound triggers exist on the same table?

Yes.

But generally, it is better to consolidate logic to avoid confusion.

13. Are compound triggers mandatory for avoiding mutating table errors?

No.

Other alternatives include:

·        Using statement-level triggers

·        Using temporary tables

·        Using package variables

But compound triggers are the cleanest modern solution.

14. What are common real-world use cases?

·        Auditing bulk salary updates

·        Maintaining summary tables

·        Enforcing cross-row validation

·        Collecting affected row IDs and processing them once

·        Preventing duplicate logic across triggers

15. What are best practices for compound triggers?

·        Keep row-level logic lightweight

·        Use collections to store row data

·        Process heavy logic in after statement section

·        Document each timing section clearly

·        Avoid unnecessary complexity

16. When should you avoid compound triggers?

Avoid them when:

·        Only simple row validation is needed

·        A simple BEFORE trigger is sufficient

·        Business logic belongs in application layer