```html

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.

```

Oracle DDL-Level 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.

```

Oracle Triggers FAQs with Examples

1. What is a trigger in Oracle?

A trigger is a PL/SQL program that automatically executes when a specified event occurs on a table, view, schema, or database.

Common events:

• INSERT
• UPDATE
• DELETE
• DDL events such as CREATE, ALTER, DROP
• Database events such as LOGON, STARTUP

2. What is the basic syntax?

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- PL/SQL statements
END;
/

Example:

CREATE OR REPLACE TRIGGER trg_emp
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee inserted');
END;
/

3. What is the difference between BEFORE and AFTER triggers?

BEFORE triggers execute before the DML operation.

CREATE OR REPLACE TRIGGER trg_emp_before
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_date := SYSDATE;
END;
/

AFTER triggers execute after the DML operation.

CREATE OR REPLACE TRIGGER trg_emp_after
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log(emp_id, log_date)
    VALUES (:NEW.employee_id, SYSDATE);
END;
/

A common rule of thumb:

• Use BEFORE when you need to validate or modify :NEW values.
• Use AFTER when you want to perform an action after the row has been successfully changed.

4. What are :NEW and :OLD?

They are correlation variables used inside row-level triggers.

Operation	:OLD	:NEW
INSERT	❌	✅
UPDATE	✅	✅
DELETE	✅	❌

Example:

CREATE OR REPLACE TRIGGER trg_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(
        'Old salary: ' || :OLD.salary ||
        ', New salary: ' || :NEW.salary
    );
END;
/

5. Can a trigger modify :NEW?

Yes, in a BEFORE row-level trigger.

CREATE OR REPLACE TRIGGER trg_upper_name
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.employee_name := UPPER(:NEW.employee_name);
END;
/

If you execute:

INSERT INTO employees(employee_id, employee_name)
VALUES (101, 'john');

The stored name becomes:

JOHN

You generally cannot assign to :NEW in an AFTER trigger.

6. What is a row-level trigger?

A row-level trigger executes once for every affected row.

It is identified by:

FOR EACH ROW

Example:

CREATE OR REPLACE TRIGGER trg_emp_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log
    VALUES (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        SYSDATE
    );
END;
/

If an update affects 100 employees, the trigger executes 100 times.

7. What is a statement-level trigger?

A statement-level trigger executes once for the entire SQL statement, regardless of how many rows are affected.

CREATE OR REPLACE TRIGGER trg_emp_statement
AFTER UPDATE ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee table updated');
END;
/

For:

UPDATE employees
SET salary = salary * 1.10;

Even if 1,000 rows are updated, the trigger executes once.

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

Feature	Row-level	Statement-level
Syntax	FOR EACH ROW	No FOR EACH ROW
Execution	Once per row	Once per statement
:OLD / :NEW	Available	Not available
Best for	Row-specific logic	Statement-level actions

9. Can one trigger handle INSERT, UPDATE, and DELETE?

Yes.

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN

    IF INSERTING THEN
        DBMS_OUTPUT.PUT_LINE('INSERT');

    ELSIF UPDATING THEN
        DBMS_OUTPUT.PUT_LINE('UPDATE');

    ELSIF DELETING THEN
        DBMS_OUTPUT.PUT_LINE('DELETE');
    END IF;

END;
/

Oracle provides the following conditional predicates:

• INSERTING
• UPDATING
• DELETING

These determine which event fired the trigger.

10. How do I prevent an invalid salary?

CREATE OR REPLACE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary cannot be negative'
        );
    END IF;
END;
/

Now:

INSERT INTO employees(employee_id, salary)
VALUES (101, -5000);

produces an application error.

11. How do I automatically set a creation date?

CREATE OR REPLACE TRIGGER trg_created_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_date := SYSDATE;
END;
/

This is a classic trigger use case.

However, for simple default values, a column DEFAULT is often preferable:

created_date DATE DEFAULT SYSDATE

12. How can I automatically update a modification date?

CREATE OR REPLACE TRIGGER trg_modified_date
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    :NEW.modified_date := SYSDATE;
END;
/

Every update automatically changes modified_date.

13. How do I create an audit trigger?

Suppose:

CREATE TABLE employee_audit (
    employee_id NUMBER,
    old_salary  NUMBER,
    new_salary  NUMBER,
    changed_by  VARCHAR2(100),
    changed_on  DATE
);

Trigger:

CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        changed_on
    )
    VALUES
    (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        USER,
        SYSDATE
    );
END;
/

Now a salary change automatically creates an audit record.

14. Can a trigger call a procedure?

Yes.

CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    send_employee_notification(:NEW.employee_id);
END;
/

This is often better than putting a large amount of business logic directly inside the trigger.

15. Can a trigger contain a COMMIT?

Normally, no.

This is invalid in a normal trigger:

BEGIN
    INSERT INTO employee_log VALUES (...);
    COMMIT;
END;

You will encounter:

ORA-04092: cannot COMMIT in a trigger

The trigger runs as part of the transaction that fired it.

16. What is a mutating table error?

A common Oracle trigger problem is:

ORA-04091: table ... is mutating

This can happen when a row-level trigger tries to query or modify the same table that caused the trigger to fire.

Example:

CREATE OR REPLACE TRIGGER trg_emp
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees;
END;
/

If the trigger fires because of an update to employees, querying employees from the row-level trigger can cause a mutating-table error.

17. How can I solve a mutating-table problem?

A compound trigger is one modern solution.

Conceptually:

CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN
        -- Collect row information
        NULL;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- Query employees here
        NULL;
    END AFTER STATEMENT;

END;
/

The important idea is to collect information at the row level and perform table-level processing in the AFTER STATEMENT section.

18. What is a compound trigger?

A compound trigger allows multiple timing sections within one trigger.

It can contain sections such as:

• BEFORE STATEMENT
• BEFORE EACH ROW
• AFTER EACH ROW
• AFTER STATEMENT

It is particularly useful for:

• Avoiding mutating-table problems
• Sharing state between timing sections
• Bulk processing

19. Can I disable a trigger?

Yes.

ALTER TRIGGER trg_emp DISABLE;

Enable it again:

ALTER TRIGGER trg_emp ENABLE;

For all triggers on a table:

ALTER TABLE employees DISABLE ALL TRIGGERS;

And:

ALTER TABLE employees ENABLE ALL TRIGGERS;

20. How do I drop a trigger?

DROP TRIGGER trg_emp;

21. How do I check whether a trigger is enabled?

Query:

SELECT trigger_name,
       status
FROM user_triggers
WHERE table_name = 'EMPLOYEES';

Possible status:

• ENABLED
• DISABLED

22. Can a trigger be created on a view?

Yes. Oracle supports INSTEAD OF triggers on views.

Example:

CREATE OR REPLACE TRIGGER trg_view_insert
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
BEGIN
    INSERT INTO employees(employee_id, employee_name)
    VALUES (:NEW.employee_id, :NEW.employee_name);
END;
/

Instead of Oracle directly performing the DML against the view, the trigger defines what should happen.

23. What is an INSTEAD OF trigger?

An INSTEAD OF trigger tells Oracle:

"When someone performs this operation on the view, execute this logic instead."

It is primarily useful for making complex or non-updatable views behave as though they can accept DML.

24. Can triggers fire other triggers?

Yes.

For example:

INSERT into EMPLOYEES
      ↓
Trigger A fires
      ↓
Trigger A updates DEPARTMENTS
      ↓
Trigger B fires

This is called cascading trigger execution.

Be careful because excessive trigger chaining can make application behavior difficult to understand and debug.

25. What is a trigger vs a stored procedure?

Trigger	Procedure
Executes automatically	Called explicitly
Associated with an event/object	Independent program
Usually responds to DML/DDL/database events	Performs business/application logic
Cannot normally accept parameters like a procedure	Can accept parameters
Useful for auditing/validation	Useful for reusable business logic

26. Trigger vs constraint — which should I use?

Prefer a constraint when the requirement can be expressed naturally as a constraint.

For example, instead of:

CREATE TRIGGER ...
IF salary < 0 THEN ...

prefer:

ALTER TABLE employees
ADD CONSTRAINT chk_salary
CHECK (salary >= 0);

Constraints are generally clearer and are enforced directly by Oracle.

Use triggers when you need behavior that constraints cannot easily provide, such as:

• Audit logging
• Automatically maintaining related information
• Complex event-based processing
• INSTEAD OF behavior for views

⭐ Common Oracle Trigger Interview Questions

1. What is a trigger?
2. What are BEFORE and AFTER triggers?
3. What is a row-level trigger?
4. What is a statement-level trigger?
5. What are :OLD and :NEW?
6. Can we modify :NEW in an AFTER trigger?
7. What is an INSTEAD OF trigger?
8. What is a mutating-table error?
9. How do you solve a mutating-table problem?
10. What is a compound trigger?
11. Can a trigger contain COMMIT?
12. Can a trigger call a procedure?
13. Can one trigger handle INSERT/UPDATE/DELETE?
14. How do you disable and enable a trigger?
15. How do you find all triggers on a table?
16. What is cascading trigger execution?
17. Trigger vs procedure?
18. Trigger vs constraint?
19. When should you avoid triggers?
20. How do you create an audit trigger?

A Good Interview Example to Remember

CREATE OR REPLACE TRIGGER trg_employee_audit
AFTER UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit
    (
        employee_id,
        old_salary,
        new_salary,
        changed_by,
        changed_on
    )
    VALUES
    (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        USER,
        SYSDATE
    );
END;
/

Interview explanation:
"This is an AFTER row-level trigger. It fires whenever an employee's salary is updated. :OLD gives the previous salary and :NEW gives the new salary, and the trigger stores the change in an audit table."

Key Takeaways

• BEFORE: Use when you need to validate or modify incoming values.
• AFTER: Use for auditing and follow-up actions.
• Row-level: Executes once for every affected row.
• Statement-level: Executes once for the entire SQL statement.
• :OLD: Represents the previous value.
• :NEW: Represents the new value.
• Compound trigger: Useful for combining row-level and statement-level processing.
• INSTEAD OF: Commonly used with views.
• ORA-04091: Indicates a mutating-table problem.
• Constraints: Prefer them when the rule can naturally be enforced using a constraint.

```

Oracle Mutating Table Faqs With Examples

1. What is a mutating table error?

A mutating table error occurs when a row-level trigger tries to query or modify the same table that is currently being changed by the triggering DML statement.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees;
END;
/

Then:

UPDATE employees
SET salary = salary + 1000;

This can produce:

ORA-04091: table EMPLOYEES is mutating

2. Why does the mutating-table error occur?

If Oracle is still processing rows for an UPDATE, a row-level trigger firing during that operation cannot safely query the same table and see a consistent state. That is why Oracle raises ORA-04091.

UPDATE EMPLOYEES
      ↓
Row 1 → Trigger
Row 2 → Trigger
Row 3 → Trigger
...
Row 100 → Trigger
      ↓
Statement completes

3. What is the classic mutating-table example?

A common example is checking a rule by querying the same table inside a row-level trigger.

CREATE OR REPLACE TRIGGER trg_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_avg_salary NUMBER;
BEGIN
    SELECT AVG(salary)
    INTO v_avg_salary
    FROM employees;

    IF :NEW.salary > v_avg_salary * 2 THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary is too high'
        );
    END IF;
END;
/

Running an UPDATE on employees can result in ORA-04091 because the trigger queries the same table while it is being updated.

4. Which triggers commonly cause mutating-table errors?

The problem is specifically associated with row-level triggers that access their triggering table during the DML operation. Statement-level triggers do not have the same row-by-row mutating-table problem.

5. Does every query on the same table cause a mutating-table error?

The problematic case is a row-level trigger querying its own triggering table during the triggering DML operation.

6. Can a row-level trigger modify the same table?

Trying to modify the triggering table from its own row-level trigger can also cause problems, including mutating-table errors and recursive behavior.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    UPDATE employees
    SET last_modified = SYSDATE
    WHERE employee_id = :NEW.employee_id;
END;
/

7. How can we avoid a mutating-table error?

Common solutions are to use a statement-level trigger, use a compound trigger, use a collection to store row information, move processing to the AFTER STATEMENT section, or redesign the business rule.

Solution 1 — Use a Statement-Level Trigger

8. How can a statement-level trigger help?

A statement-level trigger executes after the entire DML statement finishes, so the table is no longer mutating.

CREATE OR REPLACE TRIGGER trg_emp_check
AFTER UPDATE ON employees
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM employees;

    DBMS_OUTPUT.PUT_LINE(
        'Employee count: ' || v_count
    );
END;
/

9. What is the limitation of a statement-level trigger?

A statement-level trigger does not have individual row context, so you cannot use :OLD and :NEW in the statement-level section.

Solution 2 — Compound Trigger

10. Why is a compound trigger useful for mutating-table errors?

A compound trigger allows you to capture information during row processing and access it later in AFTER STATEMENT.

CREATE OR REPLACE TRIGGER trg_salary_check
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

    AFTER STATEMENT IS
        v_avg_salary NUMBER;
    BEGIN
        SELECT AVG(salary)
        INTO v_avg_salary
        FROM employees;

        DBMS_OUTPUT.PUT_LINE(
            'Average salary: ' || v_avg_salary
        );
    END AFTER STATEMENT;

END;
/

11. How can a compound trigger collect changed employee IDs?

CREATE OR REPLACE TRIGGER trg_emp_collect
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

```
TYPE emp_id_list IS
    TABLE OF employees.employee_id%TYPE;

g_emp_ids emp_id_list := emp_id_list();

AFTER EACH ROW IS
BEGIN
    g_emp_ids.EXTEND;
    g_emp_ids(g_emp_ids.COUNT) := :NEW.employee_id;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
    FOR i IN 1 .. g_emp_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            'Updated Employee: ' || g_emp_ids(i)
        );
    END LOOP;
END AFTER STATEMENT;
```

END;
/

12. How does this solve the mutating-table problem?

During AFTER EACH ROW, the trigger only collects data and does not query employees. After the update completes, AFTER STATEMENT runs and can safely query the table.

Solution 3 — Use a Collection

13. Why use a collection?

A collection allows the row-level trigger section to store information without querying the mutating table.

CREATE OR REPLACE TRIGGER trg_emp_changes
FOR UPDATE ON employees
COMPOUND TRIGGER

    TYPE emp_list IS
        TABLE OF employees.employee_id%TYPE;

    g_ids emp_list := emp_list();

    AFTER EACH ROW IS
    BEGIN
        g_ids.EXTEND;
        g_ids(g_ids.COUNT) := :NEW.employee_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        FOR i IN 1 .. g_ids.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'Employee: ' || g_ids(i)
            );
        END LOOP;
    END AFTER STATEMENT;

END;
/

Solution 4 — Use an Autonomous Transaction?

No. It should not be treated as a general solution. For mutating-table problems, prefer compound triggers, statement-level processing, collections, or redesign.

14. Can a package help solve a mutating-table problem?

Historically, one solution was to store values in a package collection and process them later in a statement-level trigger. A compound trigger is usually cleaner.

15. Classic Interview Question

Question: Why does this trigger cause ORA-04091?

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    v_total NUMBER;
BEGIN
    SELECT SUM(salary)
    INTO v_total
    FROM employees;
END;
/

Answer: Because employees is being modified and the row-level trigger attempts to query the same table while it is mutating.

16. How would you fix that trigger?

Move the query to statement-level processing.

CREATE OR REPLACE TRIGGER trg_emp
AFTER UPDATE ON employees
DECLARE
    v_total NUMBER;
BEGIN
    SELECT SUM(salary)
    INTO v_total
    FROM employees;

    DBMS_OUTPUT.PUT_LINE(
        'Total salary: ' || v_total
    );
END;
/

17. What if I need :NEW values as well?

Use a compound trigger.

CREATE OR REPLACE TRIGGER trg_emp
FOR UPDATE ON employees
COMPOUND TRIGGER

    TYPE id_list IS
        TABLE OF employees.employee_id%TYPE;

    g_ids id_list := id_list();

    AFTER EACH ROW IS
    BEGIN
        g_ids.EXTEND;
        g_ids(g_ids.COUNT) := :NEW.employee_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_total NUMBER;
    BEGIN
        SELECT SUM(salary)
        INTO v_total
        FROM employees;

        DBMS_OUTPUT.PUT_LINE(
            'Total salary: ' || v_total
        );

        FOR i IN 1 .. g_ids.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'Changed employee: ' || g_ids(i)
            );
        END LOOP;
    END AFTER STATEMENT;

END;
/

18. Is a mutating-table error a compilation error?

Usually no. The trigger can compile successfully, but the error occurs at runtime when the triggering DML statement executes.

19. What is ORA-04091?

The key Oracle error is ORA-04091: table string.string is mutating, trigger/function may not see it. It indicates that a row-level trigger or function called from it is attempting to access a table that is currently undergoing the triggering DML operation.

20. What is ORA-04088?

You may also see ORA-04088: error during execution of trigger, which commonly appears along with an underlying error such as ORA-04091.

21. Does a SELECT from another table cause a mutating-table error?

Normally no. The mutating-table issue is specifically about accessing the table currently undergoing the triggering operation.

22. Does a foreign-key relationship automatically cause a mutating-table error?

No. The issue is typically caused by a row-level trigger or function attempting to access the table that is currently being modified.

23. Can a function called by a trigger cause a mutating-table error?

Yes. If the function queries the same table that is being modified, the same mutating-table problem can occur.

Quick Interview Table

Question	Answer
What is a mutating table?	A table currently being modified by the triggering DML statement
Common error?	ORA-04091
Usually associated with?	Row-level triggers
Can the trigger query another table?	Yes
Can the trigger safely query the triggering table row-by-row?	Generally no
Can AFTER STATEMENT query the table?	Yes
Best modern solution?	Compound trigger when row + statement processing is needed
Can collections help?	Yes
Does :OLD/:NEW cause the problem?	No
Can a called function cause it?	Yes

Key Interview Point

A mutating-table error (ORA-04091) occurs when row-level trigger processing attempts to access the table that is currently being modified. The usual solutions are to move the table access to statement-level processing or use a compound trigger to collect row-level information and process it after the statement completes.

Oracle Cursor Faqs With Examples

1. What is a Cursor in Oracle?

A cursor is a mechanism used by Oracle to process the result of a SQL statement, especially when working with rows returned by a query.

SQL Query
   ↓
Result Set
   ↓
Cursor
   ↓
Process rows

2. Why do we use Cursors?

Cursors are useful when you need to process query results row by row, perform calculations for each row, apply different logic to different rows, retrieve multiple rows in PL/SQL, or process records sequentially.

3. What are the types of Cursors?

Oracle cursors are mainly divided into two types: Implicit Cursor and Explicit Cursor.

             CURSORS
                |
        ┌───────┴───────┐
        ↓               ↓
    Implicit          Explicit
    Cursor            Cursor

4. What is an Implicit Cursor?

An implicit cursor is automatically created by Oracle whenever a SQL statement is executed. You do not need to declare, open, fetch from, or close it.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE department_id = 10;

    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
/

5. What is an Explicit Cursor?

An explicit cursor is a cursor that the programmer explicitly declares and manages. It is useful when a query returns multiple rows and you want to process them individually.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    OPEN emp_cursor;

    -- Fetch/process rows here

    CLOSE emp_cursor;
END;
/

6. What are the steps of an Explicit Cursor?

The traditional explicit cursor lifecycle has four steps: DECLARE, OPEN, FETCH, and CLOSE.

DOFC = Declare → Open → Fetch → Close

7. How do you Declare a Cursor?

Syntax:

CURSOR cursor_name IS
    SELECT statement;

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    NULL;
END;
/

8. How do you Open a Cursor?

Use OPEN cursor_name; to execute the cursor query and make the result set available for fetching.

9. What is FETCH?

FETCH retrieves rows from the cursor's result set. Each fetch retrieves the next row into the specified variables.

FETCH emp_cursor
INTO v_id, v_name;

10. How do you Close a Cursor?

Use CLOSE cursor_name; to release the cursor resources after processing is complete.

11. Complete Explicit Cursor Example

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;

v_id   employees.employee_id%TYPE;
v_name employees.first_name%TYPE;

BEGIN
OPEN emp_cursor;

LOOP
    FETCH emp_cursor INTO v_id, v_name;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;

CLOSE emp_cursor;

END;
/

DECLARE cursor
      ↓
OPEN cursor
      ↓
FETCH first row
      ↓
Process row
      ↓
FETCH next row
      ↓
Process row
      ↓
...
      ↓
%NOTFOUND = TRUE
      ↓
EXIT LOOP
      ↓
CLOSE cursor

12. What are Cursor Attributes?

Oracle provides cursor attributes to get information about cursor execution.

For implicit cursors:

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPEN

For explicit cursors:

cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ROWCOUNT
cursor_name%ISOPEN

13. What is %FOUND?

%FOUND tells whether the most recent fetch or SQL operation affected or found a row.

14. What is %NOTFOUND?

%NOTFOUND becomes TRUE when the most recent fetch did not retrieve a row. It is commonly used to terminate a cursor loop.

LOOP
    FETCH emp_cursor INTO v_id, v_name;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

15. What is %ROWCOUNT?

%ROWCOUNT tells how many rows have been fetched so far for an explicit cursor.

16. What is %ISOPEN?

%ISOPEN tells whether an explicit cursor is currently open.

17. Explicit Cursor Attributes — Summary

Attribute	Meaning
%FOUND	Last fetch found a row
%NOTFOUND	Last fetch did not find a row
%ROWCOUNT	Number of rows fetched so far
%ISOPEN	Whether cursor is currently open

18. What is an Implicit Cursor Attribute?

For implicit SQL statements, Oracle uses the SQL cursor. The attributes are SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN.

19. Difference Between Implicit and Explicit Cursor

Implicit Cursor	Explicit Cursor
Created automatically	Declared by programmer
Oracle manages it	Programmer manages it
No OPEN required	Usually explicitly opened
No explicit FETCH required	Explicit FETCH is possible
No explicit CLOSE required	Programmer can explicitly close it
Uses SQL%...	Uses cursor_name%...

20. What is a Cursor FOR LOOP?

A cursor FOR loop simplifies explicit cursor processing. Oracle automatically handles opening the cursor, fetching rows, and closing the cursor.

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name
        FROM employees;
BEGIN
    FOR emp IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE(emp.employee_id || ' - ' || emp.first_name);
    END LOOP;
END;
/

21. Why is Cursor FOR LOOP useful?

It makes code shorter and reduces mistakes. Instead of manually writing OPEN, FETCH, LOOP, EXIT, and CLOSE, you can use a Cursor FOR loop.

22. Cursor FOR LOOP Without Declaring a Cursor

BEGIN
    FOR emp IN (
        SELECT employee_id, first_name
        FROM employees
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp.employee_id || ' - ' || emp.first_name);
    END LOOP;
END;
/

23. What is a Parameterized Cursor?

A parameterized cursor accepts parameters when it is opened.

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name
        FROM employees
        WHERE department_id = p_dept_id;
BEGIN
    FOR emp IN emp_cursor(10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp.first_name);
    END LOOP;
END;
/

24. Why use Parameterized Cursors?

They allow the same cursor definition to be reused with different values.

25. What is a Nested Cursor?

A cursor can be used inside another loop or cursor-processing block. This is useful for department-by-department processing of employees.

BEGIN
    FOR dept IN (
        SELECT department_id
        FROM departments
    )
    LOOP
        FOR emp IN (
            SELECT employee_id, first_name
            FROM employees
            WHERE department_id = dept.department_id
        )
        LOOP
            DBMS_OUTPUT.PUT_LINE(emp.first_name);
        END LOOP;
    END LOOP;
END;
/

26. What happens if you FETCH from a closed cursor?

Oracle raises a cursor-related error such as INVALID_CURSOR.

27. What happens if you OPEN an already open cursor?

Opening an already open explicit cursor causes an error such as INVALID_CURSOR.

28. What happens if you CLOSE an already closed cursor?

Attempting to close a cursor that is not open causes an INVALID_CURSOR error.

29. Can a cursor return multiple rows?

Yes. Explicit cursors are commonly used to process multiple rows sequentially.

30. Can SELECT INTO use multiple rows?

A normal SELECT INTO expects a single row. If multiple rows are returned, Oracle raises TOO_MANY_ROWS.

31. Cursor and SELECT INTO Difference

SELECT INTO	Cursor
Generally expects one row	Can process multiple rows
Simple retrieval	Useful for row-by-row processing
Multiple rows can cause TOO_MANY_ROWS	Designed for multiple-row processing

32. What is a REF CURSOR?

A REF CURSOR is a cursor variable that can refer to different query result sets. It is commonly used when passing query results between PL/SQL programs and applications.

33. Strong REF CURSOR vs Weak REF CURSOR

A strong REF CURSOR has a specified return type. A weak REF CURSOR does not specify a fixed return type.

34. Implicit Cursor vs Cursor FOR LOOP

Do not confuse these concepts. An implicit cursor is automatically managed by Oracle for a SQL statement, while a Cursor FOR loop automatically manages the open, fetch, and close process for row-by-row iteration.

35. Complete Cursor Example for Exam

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees;

v_id     employees.employee_id%TYPE;
v_name   employees.first_name%TYPE;
v_salary employees.salary%TYPE;

BEGIN
OPEN emp_cursor;

LOOP
    FETCH emp_cursor INTO v_id, v_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name || ' ' || v_salary);
END LOOP;

CLOSE emp_cursor;

END;
/

DECLARE
   ↓
Define cursor
   ↓
OPEN
   ↓
FETCH
   ↓
Check %NOTFOUND
   ↓
Process row
   ↓
FETCH next row
   ↓
CLOSE

36. Frequently Asked Interview Questions

Question	Answer
What is a cursor?	A mechanism for processing SQL query results, especially row by row.
What are the two main types of cursors?	Implicit and Explicit cursors.
Who manages an implicit cursor?	Oracle automatically manages it.
Who manages an explicit cursor?	The programmer controls its lifecycle.
What are the four steps of an explicit cursor?	DECLARE → OPEN → FETCH → CLOSE
Which attribute checks whether a row was found?	cursor_name%FOUND
Which attribute checks whether a row was not found?	cursor_name%NOTFOUND
Which attribute gives the number of rows fetched?	cursor_name%ROWCOUNT
Which attribute checks whether the cursor is open?	cursor_name%ISOPEN

Important Exam Points

1. A cursor is used to process SQL query results.
2. There are two main types: Implicit and Explicit.
3. Implicit cursors are automatically managed by Oracle.
4. Explicit cursors are declared by the programmer.
5. Traditional explicit cursor steps are DECLARE → OPEN → FETCH → CLOSE.
6. %FOUND tells whether the last fetch found a row.
7. %NOTFOUND tells whether the last fetch did not find a row.
8. %ROWCOUNT tells the number of rows fetched so far.
9. %ISOPEN tells whether an explicit cursor is open.
10. Cursor FOR loops automatically manage opening, fetching, and closing.
11. Parameterized cursors accept values when used or opened.
12. SELECT INTO is generally for a single-row result; cursors are suitable for multiple-row processing.
13. REF CURSOR is a cursor variable used to return or reference query result sets.

Easy Memory Trick

IMPLICIT
→ Oracle manages it

EXPLICIT
→ Programmer defines it

EXPLICIT CURSOR
→ DECLARE
→ OPEN
→ FETCH
→ CLOSE

ATTRIBUTES
→ FOUND
→ NOTFOUND
→ ROWCOUNT
→ ISOPEN