Trigger

1. What is an Oracle trigger?

An Oracle trigger is a stored database object that automatically executes (fires) in response to a specific event on a table, view, schema, or database.

Triggers are commonly used for:

·        Auditing changes

·        Enforcing business rules

·        Maintaining derived data

·        Preventing invalid transactions

2. When does a trigger fire?

A trigger can fire:

·        Before an event

·        After an event

·        Instead of an event (for views)

Events include:

·        INSERT

·        UPDATE

·        DELETE

·        Database events (logon, startup, shutdown, etc.)

3. What are the types of triggers in Oracle?

You requested no types, so here’s a simplified view:

Triggers are defined based on:

·        Timing (before/after)

·        Event (insert/update/delete)

·        Scope (row-level or statement-level)

·        Object (table, view, schema, database)

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

·        Row-level trigger: Executes once for every row affected.

·        Statement-level trigger: Executes once per SQL statement, regardless of how many rows are affected.

5. What are :NEW and :OLD values?

In row-level triggers:

·        :NEW represents the new value of a row.

·        :OLD represents the previous value of a row.

Used mainly for auditing or validation.

6. What is a mutating table error?

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

Oracle prevents this to maintain data consistency.

7. Can triggers commit or rollback transactions?

No.
Triggers cannot directly perform COMMIT or ROLLBACK because they execute within the transaction that fired them.

8. What is a compound trigger?

A compound trigger allows you to combine multiple timing points (before statement, before row, after row, after statement) into a single trigger body.

It helps:

·        Avoid mutating table errors

·        Improve performance

·        Share variables across timing sections

9. Can a trigger call a stored procedure?

Yes.
Triggers commonly call stored procedures or packages to keep logic modular and maintainable.

10. How can triggers impact performance?

Triggers can:

·        Slow down DML operations

·        Increase locking

·        Cause cascading effects if not designed properly

Best practice: Keep trigger logic minimal and efficient.

11. Can triggers be disabled?

Yes.
Triggers can be enabled or disabled without dropping them.

This is useful during:

·        Bulk loads

·        Maintenance

·        Data migration

12. What is an INSTEAD OF trigger?

It is used on views.
It allows DML operations on complex views that normally would not allow direct insert/update/delete.

13. Are triggers a good place for business logic?

Generally:

·        Simple validations → Yes

·        Complex business workflows → Better handled in application or stored procedures

Overusing triggers can make systems harder to debug.

14. How are triggers different from constraints?

Constraint	Trigger
Declarative	Procedural
Automatically optimized	Custom logic
Simpler	More flexible

Use constraints whenever possible before using triggers.

15. What are common use cases?

·        Audit trail tables

·        Auto-populating columns (created_date, updated_by)

·        Enforcing complex validation rules

·        Preventing unauthorized changes

·        Synchronizing tables