Oracle DDL Triggers Faqs With Examples

1. What is a DDL trigger?

A DDL trigger executes when a DDL statement is issued.

For example:

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
);

A DDL trigger can automatically record that the table was created.

2. What are common DDL events?

Common events include:

CREATE
ALTER
DROP
TRUNCATE
RENAME

You can also use the broader event category:

DDL

This can cover multiple DDL operations.

3. What is the basic syntax?

Schema-level DDL trigger:

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
BEGIN
    -- trigger logic
END;
/

This trigger fires when the specified DDL operations occur in the schema.

4. What is a schema-level trigger?

A schema-level trigger belongs to a particular user's schema.

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE OR DROP
ON SCHEMA
BEGIN
    DBMS_OUTPUT.PUT_LINE('DDL operation occurred');
END;
/

For example:

CREATE TABLE test_table (
    id NUMBER
);

The trigger fires automatically.

Important:
The trigger does not mean "Fire only for this particular table."

It means "Fire for DDL operations occurring in this schema that match the event."

5. What is a database-level DDL trigger?

A database-level trigger can respond to DDL events across the database, subject to Oracle privileges and trigger configuration.

CREATE OR REPLACE TRIGGER trg_database_ddl
AFTER CREATE OR ALTER OR DROP
ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE('Database DDL occurred');
END;
/

Typically, creating database-level triggers requires elevated privileges.

6. Schema-level vs database-level DDL trigger

Feature Schema Level Database Level
Scope One schema Database-wide
Syntax ON SCHEMA ON DATABASE
Typical use Schema auditing Centralized database auditing
Privileges Lower than database-level Higher privileges generally required

7. How do I audit CREATE, ALTER and DROP?

Suppose we have an audit table:

CREATE TABLE ddl_audit (
    username       VARCHAR2(100),
    event_type     VARCHAR2(30),
    object_name    VARCHAR2(128),
    object_type    VARCHAR2(30),
    event_date     DATE
);

Create the trigger:

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
BEGIN
    INSERT INTO ddl_audit
    (
        username,
        event_type,
        object_name,
        object_type,
        event_date
    )
    VALUES
    (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        ORA_SYSEVENT,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE,
        SYSDATE
    );
END;
/

Now:

CREATE TABLE employees (
    emp_id NUMBER
);

will cause an audit record to be inserted.

8. What are ORA_SYSEVENT, ORA_DICT_OBJ_NAME and ORA_DICT_OBJ_TYPE?

These are very useful in DDL triggers.

ORA_SYSEVENT

Returns the event that caused the trigger.

CREATE
ALTER
DROP

ORA_DICT_OBJ_NAME

Returns the name of the object affected.

EMPLOYEES

ORA_DICT_OBJ_TYPE

Returns the object type.

TABLE
INDEX
VIEW
PROCEDURE

So this:

ORA_SYSEVENT

might return:

ALTER

while:

ORA_DICT_OBJ_NAME

returns:

EMPLOYEES

9. How do I find the current user?

You can use:

USER

or:

SYS_CONTEXT('USERENV', 'SESSION_USER')

Example:

CREATE OR REPLACE TRIGGER trg_ddl
AFTER CREATE ON SCHEMA
BEGIN
    DBMS_OUTPUT.PUT_LINE('Created by: ' || USER);
END;
/

For auditing, SYS_CONTEXT is often preferable because it gives access to additional session information.

10. How do I prevent DROP TABLE?

This is a common interview question.

You can use a DDL trigger to prevent certain DDL operations.

CREATE OR REPLACE TRIGGER trg_prevent_drop
BEFORE DROP ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Dropping tables is not allowed'
        );
    END IF;
END;
/

Now:

DROP TABLE employees;

will be rejected by the trigger.

11. Why use BEFORE DROP instead of AFTER DROP?

Because if you want to prevent the operation, the trigger must execute before the operation takes place.

For example:

BEFORE DROP ON SCHEMA

allows you to reject the operation.

An AFTER DROP trigger runs after the DDL operation has occurred.

Rule to remember:

BEFORE → validate / prevent
AFTER → audit / react

12. Can I restrict ALTER TABLE?

Yes.

CREATE OR REPLACE TRIGGER trg_no_alter
BEFORE ALTER ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        RAISE_APPLICATION_ERROR(
            -20002,
            'ALTER TABLE is not allowed'
        );
    END IF;
END;
/

Now an operation such as:

ALTER TABLE employees ADD salary NUMBER;

will be blocked.

13. Can I prevent TRUNCATE?

Yes.

CREATE OR REPLACE TRIGGER trg_no_truncate
BEFORE TRUNCATE ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        RAISE_APPLICATION_ERROR(
            -20003,
            'TRUNCATE is not allowed'
        );
    END IF;
END;
/

Then:

TRUNCATE TABLE employees;

will fail.

14. Can I create a trigger for only one type of DDL?

Yes. For example, only CREATE:

CREATE OR REPLACE TRIGGER trg_create_audit
AFTER CREATE ON SCHEMA
BEGIN
    INSERT INTO ddl_audit
    (
        username,
        event_type,
        object_name,
        object_type,
        event_date
    )
    VALUES
    (
        USER,
        ORA_SYSEVENT,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE,
        SYSDATE
    );
END;
/

15. Can one DDL trigger handle multiple events?

Yes.

CREATE OR REPLACE TRIGGER trg_ddl
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Event: ' || ORA_SYSEVENT
    );
END;
/

16. Can I use IF INSERTING, UPDATING, DELETING?

No.

Those conditions belong to DML triggers.

For DDL triggers, use:

ORA_SYSEVENT

For example:

IF ORA_SYSEVENT = 'DROP' THEN
    ...
END IF;

17. Can a DDL trigger use :OLD and :NEW?

No.

:OLD and :NEW are associated with row-level DML triggers.

DDL triggers instead use Oracle event attributes such as:

ORA_SYSEVENT
ORA_DICT_OBJ_NAME
ORA_DICT_OBJ_TYPE
ORA_DICT_OBJ_OWNER

18. What is ORA_DICT_OBJ_OWNER?

It returns the owner of the dictionary object affected by the DDL event.

CREATE OR REPLACE TRIGGER trg_ddl
AFTER CREATE ON SCHEMA
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Owner: ' || ORA_DICT_OBJ_OWNER
    );

    DBMS_OUTPUT.PUT_LINE(
        'Object: ' || ORA_DICT_OBJ_NAME
    );
END;
/

19. Can I audit DDL statements?

Yes.

For example:

CREATE TABLE ddl_audit (
    username       VARCHAR2(100),
    event_type     VARCHAR2(30),
    object_name    VARCHAR2(128),
    object_type    VARCHAR2(30),
    object_owner   VARCHAR2(128),
    event_date     DATE
);

Trigger:

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
BEGIN
    INSERT INTO ddl_audit
    (
        username,
        event_type,
        object_name,
        object_type,
        object_owner,
        event_date
    )
    VALUES
    (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        ORA_SYSEVENT,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_OWNER,
        SYSDATE
    );
END;
/

Then query:

SELECT *
FROM ddl_audit
ORDER BY event_date DESC;

20. Can I get the actual SQL statement that caused the trigger?

Yes. Oracle provides event attributes that can be used for DDL auditing, including SQL text information in appropriate trigger contexts.

A commonly used approach is ORA_SQL_TXT.

Conceptually:

CREATE OR REPLACE TRIGGER trg_ddl_sql
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
DECLARE
    n PLS_INTEGER;
BEGIN
    n := ORA_SQL_TXT(NULL);

    DBMS_OUTPUT.PUT_LINE(
        'DDL event: ' || ORA_SYSEVENT
    );
END;
/

ORA_SQL_TXT is more advanced and is worth learning when building a complete DDL auditing solution.

21. What happens if a DDL trigger raises an exception?

The DDL operation can fail.

Example:

CREATE OR REPLACE TRIGGER trg_block_drop
BEFORE DROP ON SCHEMA
BEGIN
    RAISE_APPLICATION_ERROR(
        -20010,
        'DROP operation is prohibited'
    );
END;
/

Then:

DROP TABLE employees;

fails because the trigger raises an error.

22. Does DDL automatically commit?

Yes. Oracle DDL has implicit transaction-control behavior, including commits around DDL operations.

This is an important difference from normal DML.

For example:

INSERT INTO employees VALUES (1, 'John');

CREATE TABLE test_table (id NUMBER);

The DDL has implicit commit implications for the surrounding transaction.

Interview point:
Don't treat DDL like INSERT, UPDATE, or DELETE because DDL has different transaction semantics.

23. Can I use COMMIT inside a DDL trigger?

You should not explicitly issue normal transaction-control statements such as:

COMMIT;

inside a trigger.

The DDL statement itself has Oracle's own transaction semantics.

For audit designs, let the DDL operation and trigger participate in Oracle's handling of the DDL transaction rather than trying to commit manually inside the trigger.

24. What is a database event trigger?

A trigger can also respond to database events, for example:

LOGON
LOGOFF
STARTUP
SHUTDOWN
SERVERERROR

Example:

CREATE OR REPLACE TRIGGER trg_logon
AFTER LOGON ON DATABASE
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'User logged in: ' || USER
    );
END;
/

This is a database event trigger, not a DDL trigger.

25. What is the difference between DML, DDL and database event triggers?

Trigger Type Example Events Typical Scope
DML INSERT, UPDATE, DELETE Table/View
DDL CREATE, ALTER, DROP, TRUNCATE Schema/Database
Database Event LOGON, STARTUP, SERVERERROR Database/Schema

26. Can I create a DDL trigger for a specific object?

A DDL trigger is not normally defined in the same object-specific manner as a DML trigger such as:

ON employees

Instead, you create a schema/database DDL trigger and inspect the affected object:

IF ORA_DICT_OBJ_NAME = 'EMPLOYEES'
   AND ORA_DICT_OBJ_TYPE = 'TABLE'
THEN
    ...
END IF;

Example:

CREATE OR REPLACE TRIGGER trg_emp_drop
BEFORE DROP ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_NAME = 'EMPLOYEES'
       AND ORA_DICT_OBJ_TYPE = 'TABLE'
    THEN
        RAISE_APPLICATION_ERROR(
            -20020,
            'EMPLOYEES table cannot be dropped'
        );
    END IF;
END;
/

27. Can DDL triggers cause performance problems?

Yes.

A DDL trigger executes whenever its event occurs.

For example:

AFTER CREATE OR ALTER OR DROP ON SCHEMA

could fire frequently in a development environment.

If the trigger performs expensive processing, DDL operations can become slower.

Keep DDL-trigger logic:

  • Simple
  • Reliable
  • Efficient
  • Focused on auditing or enforcement

28. What are the most common uses of DDL triggers?

Auditing

Who created/altered/dropped an object?
When?
What object?
What type?

Security

Prevent unauthorized:

  • DROP
  • ALTER
  • TRUNCATE

Change tracking

Track schema changes.

Governance

Enforce rules such as:

Certain tables cannot be dropped.
Certain objects cannot be altered.

⭐ Most Important Interview Questions

Q1. What is a DDL trigger?

A trigger that automatically fires in response to DDL events such as CREATE, ALTER, DROP, or TRUNCATE.

Q2. What is the difference between DML and DDL triggers?

DML → INSERT / UPDATE / DELETE
DDL → CREATE / ALTER / DROP / TRUNCATE

Q3. What is ORA_SYSEVENT?

It returns the DDL/database event that caused the trigger.

Example:

ORA_SYSEVENT

could return:

CREATE

Q4. What is ORA_DICT_OBJ_NAME?

It returns the name of the dictionary object affected by the event.

Q5. What is ORA_DICT_OBJ_TYPE?

It returns the type of the affected object, such as:

TABLE
INDEX
VIEW
PROCEDURE

Q6. Can DDL triggers use :OLD and :NEW?

No. Those are for row-level DML triggers.

Q7. How do you prevent DROP TABLE?

CREATE OR REPLACE TRIGGER trg_no_drop
BEFORE DROP ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'DROP TABLE is not allowed'
        );
    END IF;
END;
/

Q8. Schema-level vs database-level?

ON SCHEMA

means the trigger operates at schema scope.

ON DATABASE

means database-level scope and generally requires appropriate privileges.

Q9. What is the most common real-world use?

DDL auditing — recording who created, altered, or dropped database objects.

Q10. What is the difference between BEFORE and AFTER DDL triggers?

BEFORE CREATE/ALTER/DROP
    ↓
Can be used to validate/block the operation

AFTER CREATE/ALTER/DROP
    ↓
Useful for auditing/reacting after the operation

🔥 One Interview Scenario Worth Practicing

Requirement:

Nobody should be allowed to drop a table called CUSTOMERS, but all other tables can be dropped. Also, every CREATE/ALTER/DROP operation should be audited.

You could implement the restriction with:

CREATE OR REPLACE TRIGGER trg_protect_customers
BEFORE DROP ON SCHEMA
BEGIN
    IF ORA_DICT_OBJ_TYPE = 'TABLE'
       AND ORA_DICT_OBJ_NAME = 'CUSTOMERS'
    THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'CUSTOMERS table cannot be dropped'
        );
    END IF;
END;
/

And separately implement a general audit trigger:

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP
ON SCHEMA
BEGIN
    INSERT INTO ddl_audit
    (
        username,
        event_type,
        object_name,
        object_type,
        object_owner,
        event_date
    )
    VALUES
    (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        ORA_SYSEVENT,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_OWNER,
        SYSDATE
    );
END;
/
Key Interview Takeaway:

A DDL trigger works at schema/database scope, uses event attributes such as ORA_SYSEVENT and ORA_DICT_OBJ_NAME rather than :OLD/:NEW, and is commonly used for DDL auditing and preventing unauthorized schema changes.
```

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)