DDL Triggers FAQS

1. What is a DDL trigger in Oracle?

A DDL trigger is a trigger that fires automatically in response to Data Definition Language (DDL) statements such as:

·        CREATE

·        ALTER

·        DROP

·        TRUNCATE

·        RENAME

·        GRANT

·        REVOKE

DDL triggers are used mainly for auditing and control purposes.

2. On what levels can DDL triggers be created?

DDL triggers can be created at:

·        Schema level → Fires for DDL statements in a specific schema

·        Database level → Fires for DDL statements across the entire database

3. When does a DDL trigger fire?

A DDL trigger can fire:

·        BEFORE a DDL statement

·        AFTER a DDL statement

Example events:

·        BEFORE CREATE

·        AFTER DROP

·        BEFORE ALTER

4. What are common uses of DDL triggers?

·        Auditing object creation/modification

·        Preventing table drops

·        Tracking schema changes

·        Enforcing naming standards

·        Monitoring structural changes

5. Can DDL triggers prevent an operation?

Yes.

If a DDL trigger raises an exception in a BEFORE section:

·        The DDL operation is blocked

·        The change does not occur

6. Are DDL triggers row-level or statement-level?

DDL triggers are statement-level only.

They fire once per DDL statement.

7. Can DDL triggers access :NEW and :OLD?

No.

DDL triggers do not operate on row data.
They operate on schema objects.

8. What system context information is available in DDL triggers?

DDL triggers can access metadata such as:

·        Object name

·        Object type

·        Owner

·        Login user

·        Host

·        Database name

·        Event type

This information is typically accessed using system context functions.

9. Can DDL triggers commit or rollback?

DDL statements in Oracle automatically perform an implicit commit before and after execution.

DDL triggers run within that context.

However:

·        You should avoid explicit COMMIT or ROLLBACK inside triggers.

10. Do DDL triggers affect performance?

Generally minimal impact.

However:

·        Heavy auditing logic

·        Complex processing

·        Writing to large audit tables

Can affect system performance.

11. Can multiple DDL triggers exist?

Yes.

Multiple DDL triggers can be defined:

·        On the same schema

·        On the database

Execution order is not guaranteed unless specified.

12. What is the difference between DDL trigger and DML trigger?

DDL Trigger	DML Trigger
Fires on structural changes	Fires on data changes
Works on objects	Works on table rows
Statement-level only	Row or statement level
Used for auditing/control	Used for validation/business rules

13. Can DDL triggers track all object changes in the database?

Yes.

A database-level DDL trigger can capture:

·        All CREATE

·        All DROP

·        All ALTER operations

Across all schemas (with proper privileges).

14. What are real-world production use cases?

·        Preventing accidental DROP TABLE

·        Tracking unauthorized schema changes

·        Recording who created or altered objects

·        Blocking DDL during business hours

·        Enforcing compliance policies

15. What are best practices for DDL triggers?

·        Keep logic lightweight

·        Avoid complex operations

·        Use them mainly for auditing or restriction

·        Ensure proper privileges

·        Log errors carefully

16. Are DDL triggers commonly asked in interviews?

Yes.

Common interview topics include:

·        Difference between DDL and DML triggers

·        Schema vs database trigger

·        Preventing object drops

·        Handling implicit commits

·        Use cases in real-time systems