```html

Absolutely. Here’s a practical Oracle Schema-Level Trigger FAQ with examples, aimed at interviews and real-world PL/SQL.

What is a schema-level trigger?

A schema-level trigger is a trigger associated with a particular schema/user. It is mainly used to monitor or control DDL and database events occurring in that schema.

The key idea is: 
User / Schema
       ↓
DDL or database event
       ↓
Schema-level trigger
       ↓
Trigger logic executes

Typical uses include:

  • Auditing CREATE, ALTER, and DROP
  • Preventing certain DDL operations
  • Logging schema changes
  • Auditing user logon/logoff events
  • Enforcing development/production rules

1. What is a schema-level trigger?

A schema-level trigger is created in a schema and responds to events associated with that schema.

For example:

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'DDL operation occurred'
    );

END;
/

This trigger belongs to the schema in which it is created.

It can respond to DDL operations such as:

CREATE
ALTER
DROP

2. What is the difference between a schema trigger and a database trigger?

This is a common interview question.

Schema-level trigger

AFTER CREATE ON SCHEMA

It applies to events in the current schema.

Database-level trigger

AFTER CREATE ON DATABASE

It can respond to events across the database, subject to Oracle privileges and event rules.

SCHEMA TRIGGER
      ↓
One schema


DATABASE TRIGGER
      ↓
Entire database

3. What events can a schema trigger handle?

Schema triggers are commonly used with DDL events such as:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • ANALYZE
  • GRANT
  • REVOKE

The exact events available depend on the trigger/event type and Oracle version.

Database events can also include events such as:

  • LOGON
  • LOGOFF
  • SERVERERROR

where supported by Oracle's trigger syntax.

4. Can a schema trigger be used for INSERT, UPDATE and DELETE?

No, not as a schema-level event trigger.

For normal table DML:

INSERT
UPDATE
DELETE

you use a table trigger:

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

    ...

END;
/

Schema triggers are primarily used for schema/database events, especially DDL.

INSERT / UPDATE / DELETE
        ↓
Table trigger


CREATE / ALTER / DROP
        ↓
Schema / database trigger

5. How do I create a trigger for all DDL in my schema?

Example:

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'DDL operation performed'
    );

END;
/

Now if you execute:

CREATE TABLE test_table (
    id NUMBER
);

the trigger fires.

Similarly:

ALTER TABLE test_table
ADD name VARCHAR2(100);

or:

DROP TABLE test_table;

can cause the trigger to fire.

6. Can we determine which DDL operation occurred?

Yes.

Oracle provides event predicates such as SYSEVENT.

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Event = ' || SYSEVENT
    );

END;
/

Depending on the event, you can determine whether the operation was:

CREATE
ALTER
DROP

7. Can a schema trigger determine the object name?

Yes.

Oracle provides event attributes that can be used in DDL/database event triggers.

Common attributes include:

  • ORA_DICT_OBJ_NAME
  • ORA_DICT_OBJ_TYPE
  • ORA_DICT_OBJ_OWNER

For example:

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Event      = ' || SYSEVENT
    );

    DBMS_OUTPUT.PUT_LINE(
        'Object     = ' || ORA_DICT_OBJ_NAME
    );

    DBMS_OUTPUT.PUT_LINE(
        'Object Type = ' || ORA_DICT_OBJ_TYPE
    );

END;
/

If you execute:

CREATE TABLE employees (
    employee_id NUMBER
);

you could get information similar to:

Event       = CREATE
Object      = EMPLOYEES
Object Type = TABLE

8. Can we audit DDL operations using a schema trigger?

Yes. This is one of the most common use cases.

Create an audit table:

CREATE TABLE ddl_audit (
    username       VARCHAR2(100),
    event_name     VARCHAR2(30),
    object_name    VARCHAR2(128),
    object_type    VARCHAR2(30),
    event_time     TIMESTAMP
);

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_name,
        object_name,
        object_type,
        event_time
    )
    VALUES
    (
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYSEVENT,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE,
        SYSTIMESTAMP
    );

END;
/

Now:

CREATE TABLE test_table (
    id NUMBER
);

can create an audit record such as:

USERNAME   EVENT   OBJECT_NAME   OBJECT_TYPE   EVENT_TIME
--------------------------------------------------------------
SCOTT      CREATE  TEST_TABLE    TABLE         11-AUG-2026 ...

9. Can a schema trigger prevent DDL?

Yes.

This is another important use case.

Suppose you don't want users to drop tables.

CREATE OR REPLACE TRIGGER trg_prevent_drop
BEFORE DROP ON SCHEMA
BEGIN

    RAISE_APPLICATION_ERROR(
        -20001,
        'DROP operations are not allowed'
    );

END;
/

Now:

DROP TABLE employees;

will be blocked by the trigger.

DROP TABLE
    ↓
BEFORE DROP trigger
    ↓
RAISE_APPLICATION_ERROR
    ↓
DROP prevented

10. What is the difference between BEFORE and AFTER schema triggers?

BEFORE

Runs before the event completes.

Example:

CREATE OR REPLACE TRIGGER trg_before_drop
BEFORE DROP ON SCHEMA
BEGIN

    ...

END;
/

Useful for:

  • Validation
  • Blocking operations
  • Security checks

AFTER

Runs after the event.

Example:

CREATE OR REPLACE TRIGGER trg_after_create
AFTER CREATE ON SCHEMA
BEGIN

    ...

END;
/

Useful for:

  • Auditing
  • Logging
  • Notifications
  • Post-DDL processing

11. Can a schema trigger use FOR EACH ROW?

No.

This is a very important distinction.

Schema/database event triggers are not row-level DML triggers.

You don't write:

FOR EACH ROW

for:

AFTER CREATE ON SCHEMA

Correct:

CREATE OR REPLACE TRIGGER trg_ddl
AFTER CREATE ON SCHEMA
BEGIN

    ...

END;
/

12. Can a schema trigger use :OLD and :NEW?

No.

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

They are used for:

  • INSERT
  • UPDATE
  • DELETE

on table rows.

DDL event triggers instead use event attributes such as:

SYSEVENT
ORA_DICT_OBJ_NAME
ORA_DICT_OBJ_TYPE
ORA_DICT_OBJ_OWNER

13. What are Oracle event attributes?

Oracle provides special functions/attributes for database event triggers.

Attribute Meaning
SYSEVENT Event that caused the trigger
ORA_DICT_OBJ_NAME Object name
ORA_DICT_OBJ_TYPE Object type
ORA_DICT_OBJ_OWNER Object owner
ORA_LOGIN_USER Login user
ORA_CLIENT_IP_ADDRESS Client IP address, where applicable
ORA_SYSEVENT Event information in applicable event-trigger contexts

For example:

CREATE OR REPLACE TRIGGER trg_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'User = ' || ORA_LOGIN_USER
    );

    DBMS_OUTPUT.PUT_LINE(
        'Event = ' || SYSEVENT
    );

    DBMS_OUTPUT.PUT_LINE(
        'Object = ' || ORA_DICT_OBJ_NAME
    );

END;
/

14. Can a schema trigger audit only tables?

Yes.

You can inspect ORA_DICT_OBJ_TYPE and conditionally process the event.

CREATE OR REPLACE TRIGGER trg_table_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN

        INSERT INTO ddl_audit
        (
            username,
            event_name,
            object_name,
            object_type,
            event_time
        )
        VALUES
        (
            ORA_LOGIN_USER,
            SYSEVENT,
            ORA_DICT_OBJ_NAME,
            ORA_DICT_OBJ_TYPE,
            SYSTIMESTAMP
        );

    END IF;

END;
/

Now the trigger is interested only in table-related DDL.

15. Can a schema trigger audit procedure creation?

Yes.

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    IF ORA_DICT_OBJ_TYPE = 'PROCEDURE' THEN

        INSERT INTO ddl_audit
        (
            username,
            event_name,
            object_name,
            object_type,
            event_time
        )
        VALUES
        (
            ORA_LOGIN_USER,
            SYSEVENT,
            ORA_DICT_OBJ_NAME,
            ORA_DICT_OBJ_TYPE,
            SYSTIMESTAMP
        );

    END IF;

END;
/

You can similarly check for:

TABLE
VIEW
INDEX
PROCEDURE
FUNCTION
PACKAGE
TRIGGER
SEQUENCE

and other supported object types.

16. Can a schema trigger handle LOGON?

Yes. Event triggers can be used for logon-related processing.

CREATE OR REPLACE TRIGGER trg_schema_logon
AFTER LOGON ON SCHEMA
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'User logged in: ' || ORA_LOGIN_USER
    );

END;
/

A logon trigger can be useful for things such as:

  • Session initialization
  • Security checks
  • Setting application context
  • Auditing

17. Can we set session values using a LOGON trigger?

Yes.

A logon trigger can initialize session/application context or session settings.

Conceptually:

CREATE OR REPLACE TRIGGER trg_schema_logon
AFTER LOGON ON SCHEMA
BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(
        module_name => 'EMPLOYEE_APP',
        action_name => 'LOGIN'
    );

END;
/

This can help identify application sessions in monitoring and auditing.

18. What is a LOGOFF trigger?

A logoff event trigger can execute when a session logs off.

Example:

CREATE OR REPLACE TRIGGER trg_schema_logoff
AFTER LOGOFF ON SCHEMA
BEGIN

    INSERT INTO session_audit
    (
        username,
        action_time
    )
    VALUES
    (
        ORA_LOGIN_USER,
        SYSTIMESTAMP
    );

END;
/

The exact usefulness depends on the auditing requirements and database configuration.

19. Can a schema trigger handle errors?

Oracle also supports event triggers for server errors.

For example:

CREATE OR REPLACE TRIGGER trg_server_error
AFTER SERVERERROR ON SCHEMA
BEGIN

    INSERT INTO error_audit
    (
        username,
        error_time
    )
    VALUES
    (
        ORA_LOGIN_USER,
        SYSTIMESTAMP
    );

END;
/

This can be used to capture information when relevant database/server errors occur.

For detailed error information, Oracle provides additional event functions such as:

ORA_SERVER_ERROR
ORA_SERVER_ERROR_MSG

depending on the information you need.

20. Can a schema trigger be used to prevent TRUNCATE?

Yes.

CREATE OR REPLACE TRIGGER trg_prevent_truncate
BEFORE TRUNCATE ON SCHEMA
BEGIN

    RAISE_APPLICATION_ERROR(
        -20002,
        'TRUNCATE is not allowed in this schema'
    );

END;
/

Then:

TRUNCATE TABLE employees;

can be prevented.

This can be useful in environments where developers should not accidentally remove all rows.

21. Can a schema trigger prevent DROP TABLE but allow other DDL?

Yes.

CREATE OR REPLACE TRIGGER trg_control_ddl
BEFORE DROP ON SCHEMA
BEGIN

    IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN

        RAISE_APPLICATION_ERROR(
            -20010,
            'Dropping tables is not allowed'
        );

    END IF;

END;
/

The idea is:

DROP TABLE
    ↓
Blocked

While dropping some other supported object can potentially be allowed.

22. Can a schema trigger check the object name?

Yes.

For example, prevent dropping a particular table:

CREATE OR REPLACE TRIGGER trg_protect_emp
BEFORE DROP ON SCHEMA
BEGIN

    IF ORA_DICT_OBJ_TYPE = 'TABLE'
       AND ORA_DICT_OBJ_NAME = 'EMPLOYEES'
    THEN

        RAISE_APPLICATION_ERROR(
            -20011,
            'EMPLOYEES table is protected'
        );

    END IF;

END;
/

Then:

DROP TABLE employees;

is rejected.

23. Can a schema trigger execute DML?

Yes, subject to the normal trigger restrictions.

For example, an audit trigger can execute:

INSERT INTO ddl_audit
...

However, you should be careful about transaction behavior.

The audit DML normally participates in the same transaction as the triggering operation.

24. Can a schema trigger use COMMIT?

No.

For example:

CREATE OR REPLACE TRIGGER trg_ddl
AFTER CREATE ON SCHEMA
BEGIN

    INSERT INTO ddl_audit
    VALUES (...);

    COMMIT;

END;
/

is not allowed.

Triggers cannot normally issue:

COMMIT
ROLLBACK

because the trigger is part of the transaction/event execution.

25. What happens if the schema trigger raises an exception?

Suppose:

CREATE OR REPLACE TRIGGER trg_protect
BEFORE DROP ON SCHEMA
BEGIN

    RAISE_APPLICATION_ERROR(
        -20001,
        'DROP is not permitted'
    );

END;
/

Then:

DROP TABLE employees;

fails.

The trigger effectively acts as a gate:

DROP TABLE
    ↓
BEFORE DROP trigger
    ↓
Exception
    ↓
DDL operation fails

26. What is the difference between a schema trigger and a table trigger?

Feature Table Trigger Schema Trigger
Main purpose DML processing DDL/database events
Object Table Schema/event
INSERT ✅ ❌
UPDATE ✅ ❌
DELETE ✅ ❌
CREATE ❌ ✅
ALTER ❌ ✅
DROP ❌ ✅
:OLD Row-level ❌
:NEW Row-level ❌
FOR EACH ROW Possible ❌
DDL auditing Not appropriate ✅

27. Schema trigger vs database trigger

This is another important interview question.

Schema trigger

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE ON SCHEMA
BEGIN

    ...

END;
/

Scope: Current schema

Database trigger

CREATE OR REPLACE TRIGGER trg_database_ddl
AFTER CREATE ON DATABASE
BEGIN

    ...

END;
/

Scope: Database-wide event

DDL EVENT
    |
    +-------------------+
    |                   |
    ↓                   ↓
SCHEMA              DATABASE
TRIGGER              TRIGGER
    |                   |
    ↓                   ↓
One schema's       Database-wide
events             scope

28. What is a good real-world DDL auditing example?

Create an audit table:

CREATE TABLE schema_ddl_audit (
    username       VARCHAR2(128),
    event_name     VARCHAR2(30),
    object_owner   VARCHAR2(128),
    object_name    VARCHAR2(128),
    object_type    VARCHAR2(30),
    event_time     TIMESTAMP
);

Trigger:

CREATE OR REPLACE TRIGGER trg_schema_ddl_audit
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN

    INSERT INTO schema_ddl_audit
    (
        username,
        event_name,
        object_owner,
        object_name,
        object_type,
        event_time
    )
    VALUES
    (
        ORA_LOGIN_USER,
        SYSEVENT,
        ORA_DICT_OBJ_OWNER,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE,
        SYSTIMESTAMP
    );

END;
/

Then:

CREATE TABLE test_table (
    id NUMBER
);

followed by:

ALTER TABLE test_table
ADD name VARCHAR2(100);

and:

DROP TABLE test_table;

can produce audit records similar to:

USERNAME   EVENT   OWNER   OBJECT       TYPE
---------------------------------------------------
SCOTT      CREATE  SCOTT   TEST_TABLE   TABLE
SCOTT      ALTER   SCOTT   TEST_TABLE   TABLE
SCOTT      DROP    SCOTT   TEST_TABLE   TABLE

29. Can we use schema triggers for production protection?

Yes, but use them carefully.

For example, you could prevent developers from dropping critical objects:

CREATE OR REPLACE TRIGGER trg_protect_objects
BEFORE DROP ON SCHEMA
BEGIN

    IF ORA_DICT_OBJ_NAME IN
       ('EMPLOYEES', 'DEPARTMENTS', 'PAYROLL')
    THEN

        RAISE_APPLICATION_ERROR(
            -20020,
            'Protected object cannot be dropped'
        );

    END IF;

END;
/

This can provide an additional safety mechanism.

Important:

Permissions and proper deployment controls should remain the primary security mechanism. Triggers shouldn't be treated as a replacement for Oracle privileges, roles, or controlled deployment.

30. What are common schema-trigger interview traps?

Trap 1

Question: Does a schema trigger fire for every inserted row?

Answer: ❌ No.

Schema event triggers are not row-level DML triggers.

Trap 2

Question: Can a schema trigger use :OLD and :NEW?

Answer: ❌ No.

Those are row-level DML correlation variables.

Trap 3

Question: Can a schema trigger use FOR EACH ROW?

Answer: ❌ No.

DDL/event triggers aren't row-level table triggers.

Trap 4

Question: Can a schema trigger audit CREATE TABLE?

Answer: ✅ Yes.

For example:

AFTER CREATE ON SCHEMA

Trap 5

Question: Can a schema trigger prevent DROP TABLE?

Answer: ✅ Yes.

Use:

BEFORE DROP ON SCHEMA

and raise an exception.

Trap 6

Question: Can a schema trigger perform COMMIT?

Answer: ❌ No.

Trap 7

Question: What's the difference between schema and database trigger?

Answer:

Schema trigger
→ Events in a particular schema

Database trigger
→ Database-wide event scope

31. ⭐ Schema-Level Trigger Cheat Sheet

Feature Schema Trigger
Main purpose DDL / database events
CREATE ✅
ALTER ✅
DROP ✅
TRUNCATE ✅
LOGON ✅ Where supported
LOGOFF ✅ Where supported
SERVERERROR ✅ Where supported
INSERT ❌ Not a table DML trigger
UPDATE ❌ Not a table DML trigger
DELETE ❌ Not a table DML trigger
:OLD ❌
:NEW ❌
FOR EACH ROW ❌
BEFORE ✅
AFTER ✅
COMMIT ❌

The Easiest Way to Remember

Table trigger: "A row changed."

Schema trigger: "Something happened to my schema."

For example:

CREATE TABLE
ALTER TABLE
DROP TABLE
TRUNCATE TABLE
      ↓
Schema-level trigger

Schema Trigger vs Database Trigger

SCHEMA                         DATABASE
   ↓                               ↓
One schema's events          Database-wide events
   ↓                               ↓
Schema-level trigger         Database-level trigger
Interview Tip:

If the interviewer asks, "What is the difference between a schema-level trigger and a table trigger?", a strong answer is:

"A table trigger is primarily used for row-level DML operations such as INSERT, UPDATE and DELETE, while a schema-level trigger is used for schema and database events such as CREATE, ALTER, DROP and other supported events. Schema triggers do not use :OLD, :NEW or FOR EACH ROW."
```

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)