Oracle Database-Level Trigger FAQs with Examples

A database-level trigger is a trigger associated with the entire database, rather than with one specific table or schema.

It is commonly used for:

  • Database-wide DDL auditing
  • Logon/logoff auditing
  • Server-error auditing
  • Preventing certain database-wide operations
  • Monitoring database events
  • Security and session initialization

The key idea is:

                 DATABASE
                    |
        +-----------+-----------+
        |           |           |
      LOGON       DDL       SERVERERROR
        |           |           |
        +-----------+-----------+
                    ↓
          Database-level trigger

1. What is a database-level trigger?

A database-level trigger is a trigger that fires in response to database-wide events.

For example:

CREATE OR REPLACE TRIGGER trg_db_ddl
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Database DDL event occurred'
    );

END;
/

This is different from:

AFTER CREATE ON SCHEMA

because:

SCHEMA trigger
      ↓
One schema

DATABASE trigger
      ↓
Database-wide scope

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

This is one of the most common interview questions.

Schema-level:

CREATE OR REPLACE TRIGGER trg_schema_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
BEGIN
    ...
END;
/

The trigger is associated with one schema.

Database-level:

CREATE OR REPLACE TRIGGER trg_db_ddl
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN
    ...
END;
/

The trigger is database-wide.

                 DDL EVENT
                    |
          +---------+---------+
          |                   |
       SCHEMA              DATABASE
       TRIGGER              TRIGGER
          |                   |
     One schema          Whole database

3. What events can a database-level trigger handle?

Common database event triggers include:

DDL events:

CREATE
ALTER
DROP
TRUNCATE
ANALYZE
GRANT
REVOKE

Database/session events:

LOGON
LOGOFF
STARTUP
SHUTDOWN
SERVERERROR

The exact supported events depend on the Oracle trigger type and environment.

4. Can a database trigger handle INSERT, UPDATE and DELETE?

Not as a database event trigger.

Normal DML triggers are associated with tables:

CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    ...
END;
/

Database-level event triggers are used for events such as:

CREATE
ALTER
DROP
LOGON
LOGOFF
STARTUP
SHUTDOWN
SERVERERROR

Think:

INSERT / UPDATE / DELETE
        ↓
Table trigger

CREATE / ALTER / DROP / LOGON
        ↓
Database event trigger

5. How do I create a database-wide DDL trigger?

Example:

CREATE OR REPLACE TRIGGER trg_database_ddl
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'DDL event: ' || SYSEVENT
    );

END;
/

Now a DDL operation such as:

CREATE TABLE test_table (
    id NUMBER
);

can fire the trigger.

Likewise:

ALTER TABLE test_table
ADD name VARCHAR2(100);

or:

DROP TABLE test_table;

can fire it.

6. Can a database trigger determine which object was affected?

Yes.

Oracle provides event attributes such as:

SYSEVENT
ORA_DICT_OBJ_NAME
ORA_DICT_OBJ_TYPE
ORA_DICT_OBJ_OWNER

Example:

CREATE OR REPLACE TRIGGER trg_database_ddl
AFTER CREATE OR ALTER OR DROP ON DATABASE
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
    );

    DBMS_OUTPUT.PUT_LINE(
        'Owner       = ' || ORA_DICT_OBJ_OWNER
    );

END;
/

For:

CREATE TABLE employees (
    employee_id NUMBER
);

You can get information conceptually like:

Event       = CREATE
Object      = EMPLOYEES
Object Type = TABLE
Owner       = SCOTT

7. What is SYSEVENT?

SYSEVENT tells you which database event caused the trigger to fire.

For example:

CREATE OR REPLACE TRIGGER trg_db_event
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

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

END;
/

Possible values can include:

CREATE
ALTER
DROP

depending on the triggering event.

8. What is ORA_DICT_OBJ_NAME?

It provides the name of the dictionary object associated with the event.

Example:

DBMS_OUTPUT.PUT_LINE(
    ORA_DICT_OBJ_NAME
);

If:

CREATE TABLE employees (...);

causes the trigger to fire, the object name can be:

EMPLOYEES

9. What is ORA_DICT_OBJ_TYPE?

It provides the type of the affected dictionary object.

For example:

TABLE
VIEW
INDEX
PROCEDURE
FUNCTION
PACKAGE
TRIGGER

Example:

IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN
    DBMS_OUTPUT.PUT_LINE('Table DDL');
END IF;

10. What is ORA_DICT_OBJ_OWNER?

It identifies the owner of the dictionary object.

Example:

DBMS_OUTPUT.PUT_LINE(
    'Owner = ' || ORA_DICT_OBJ_OWNER
);

This is particularly useful for a database-wide trigger because objects may belong to different schemas.

11. Can a database trigger audit DDL operations?

Yes.

This is one of the most common real-world uses.

Create an audit table:

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

Create the trigger:

CREATE OR REPLACE TRIGGER trg_database_ddl_audit
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

    INSERT INTO database_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;
/

Now suppose:

CREATE TABLE test_table (
    id NUMBER
);

Then:

ALTER TABLE test_table
ADD name VARCHAR2(100);

And later:

DROP TABLE test_table;

The audit table can contain records such as:

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

12. Can a database trigger prevent DDL?

Yes.

For example, suppose you want to prevent DROP operations.

CREATE OR REPLACE TRIGGER trg_prevent_drop
BEFORE DROP ON DATABASE
BEGIN

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

END;
/

Now:

DROP TABLE employees;

will be blocked by the trigger.

Conceptually:

DROP TABLE
     ↓
BEFORE DROP trigger
     ↓
RAISE_APPLICATION_ERROR
     ↓
DROP fails

13. Can we prevent dropping only certain objects?

Yes.

Example:

CREATE OR REPLACE TRIGGER trg_protect_tables
BEFORE DROP ON DATABASE
BEGIN

    IF ORA_DICT_OBJ_TYPE = 'TABLE'
       AND ORA_DICT_OBJ_NAME IN
           ('EMPLOYEES', 'DEPARTMENTS', 'PAYROLL')
    THEN

        RAISE_APPLICATION_ERROR(
            -20010,
            'This table is protected'
        );

    END IF;

END;
/

Now:

DROP TABLE employees;

is rejected.

But another object may not be affected by this particular condition.

14. Can a database trigger handle LOGON?

Yes.

A database-level logon trigger can execute when a user logs into the database.

Example:

CREATE OR REPLACE TRIGGER trg_db_logon
AFTER LOGON ON DATABASE
BEGIN

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

END;
/

Because this is:

AFTER LOGON ON DATABASE

it has database-wide scope.

15. What is the difference between LOGON ON SCHEMA and LOGON ON DATABASE?

Schema-level:

AFTER LOGON ON SCHEMA

Applies to logons associated with that schema.

Database-level:

AFTER LOGON ON DATABASE

Applies database-wide.

ON SCHEMA
    ↓
Specific schema

ON DATABASE
    ↓
All applicable database sessions

This makes database-level logon triggers useful for centralized session initialization or auditing.

16. What is a common LOGON trigger use case?

One common use is setting application/session context.

Example:

CREATE OR REPLACE TRIGGER trg_db_logon
AFTER LOGON ON DATABASE
BEGIN

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

END;
/

This can help identify sessions in monitoring tools.

Another common use is auditing:

CREATE TABLE login_audit (
    username    VARCHAR2(128),
    login_time  TIMESTAMP
);

Trigger:

CREATE OR REPLACE TRIGGER trg_login_audit
AFTER LOGON ON DATABASE
BEGIN

    INSERT INTO login_audit
    (
        username,
        login_time
    )
    VALUES
    (
        ORA_LOGIN_USER,
        SYSTIMESTAMP
    );

END;
/

17. Can a database trigger handle LOGOFF?

Yes.

Example:

CREATE OR REPLACE TRIGGER trg_db_logoff
AFTER LOGOFF ON DATABASE
BEGIN

    INSERT INTO login_audit
    (
        username,
        login_time
    )
    VALUES
    (
        ORA_LOGIN_USER,
        SYSTIMESTAMP
    );

END;
/

In a real implementation, you'd normally use a separate audit table/columns for logoff events rather than calling the column login_time.

18. Can a database trigger handle STARTUP?

Yes.

A database event trigger can respond to startup events.

Example:

CREATE OR REPLACE TRIGGER trg_db_startup
AFTER STARTUP ON DATABASE
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Database has started'
    );

END;
/

Typical uses include:

  • Database initialization
  • Monitoring
  • Startup auditing
  • Session/environment setup

19. Can a database trigger handle SHUTDOWN?

Yes, where supported by the Oracle event-trigger model.

Example:

CREATE OR REPLACE TRIGGER trg_db_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN

    DBMS_OUTPUT.PUT_LINE(
        'Database is shutting down'
    );

END;
/

These types of triggers are normally used by DBAs for database lifecycle management and auditing.

20. Can a database trigger handle SERVERERROR?

Yes.

Example:

CREATE OR REPLACE TRIGGER trg_server_error
AFTER SERVERERROR ON DATABASE
BEGIN

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

END;
/

This can be useful for auditing certain database/server errors.

Oracle also provides error-related event functions such as:

ORA_SERVER_ERROR
ORA_SERVER_ERROR_MSG

for obtaining more information about the error.

21. Can we capture the actual Oracle error number?

Yes.

For example, Oracle provides:

ORA_SERVER_ERROR(1)

to retrieve an applicable server error code.

Example:

CREATE OR REPLACE TRIGGER trg_server_error
AFTER SERVERERROR ON DATABASE
BEGIN

    INSERT INTO database_error_audit
    (
        username,
        error_code,
        error_time
    )
    VALUES
    (
        ORA_LOGIN_USER,
        ORA_SERVER_ERROR(1),
        SYSTIMESTAMP
    );

END;
/

The exact error-stack handling should be designed carefully when implementing production auditing.

22. Can one database trigger handle multiple events?

Yes, depending on the supported event combination.

For DDL:

CREATE OR REPLACE TRIGGER trg_database_ddl
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

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

END;
/

The trigger can determine the event using:

SYSEVENT

For example:

CREATE → CREATE
ALTER  → ALTER
DROP   → DROP

23. Can a database trigger use FOR EACH ROW?

No.

This is a very common interview question.

You don't write:

AFTER CREATE ON DATABASE
FOR EACH ROW

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

Correct:

CREATE OR REPLACE TRIGGER trg_db_ddl
AFTER CREATE ON DATABASE
BEGIN
    ...
END;
/

24. Can a database trigger use :OLD and :NEW?

No.

:OLD and :NEW are row-level DML correlation variables.

They are used with table triggers such as:

AFTER UPDATE ON employees
FOR EACH ROW

Database event triggers use event attributes instead:

SYSEVENT
ORA_DICT_OBJ_NAME
ORA_DICT_OBJ_TYPE
ORA_DICT_OBJ_OWNER
ORA_LOGIN_USER

25. Can a database trigger query dictionary views?

Yes, where appropriate.

For example:

SELECT COUNT(*)
FROM all_objects
WHERE owner = ORA_DICT_OBJ_OWNER;

However, you should be careful with queries and privileges in event triggers because these triggers can execute in sensitive database contexts.

26. Can a database trigger perform DML?

Yes.

This is common for auditing.

For example:

INSERT INTO database_ddl_audit
(
    username,
    event_name,
    object_name
)
VALUES
(
    ORA_LOGIN_USER,
    SYSEVENT,
    ORA_DICT_OBJ_NAME
);

The DML becomes part of the transaction/event processing.

27. Can a database trigger execute COMMIT?

No.

For example:

CREATE OR REPLACE TRIGGER trg_db_ddl
AFTER CREATE ON DATABASE
BEGIN

    INSERT INTO database_ddl_audit (...);

    COMMIT;

END;
/

A trigger cannot normally issue:

COMMIT
ROLLBACK

The trigger participates in the transaction context of the operation that caused it.

28. Can a database trigger call a procedure?

Yes.

Example:

CREATE OR REPLACE TRIGGER trg_db_ddl
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

    log_ddl_event(
        ORA_LOGIN_USER,
        SYSEVENT,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_TYPE
    );

END;
/

Procedure:

CREATE OR REPLACE PROCEDURE log_ddl_event
(
    p_username     VARCHAR2,
    p_event        VARCHAR2,
    p_object_name  VARCHAR2,
    p_object_type  VARCHAR2
)
IS
BEGIN

    INSERT INTO database_ddl_audit
    (
        username,
        event_name,
        object_name,
        object_type,
        event_time
    )
    VALUES
    (
        p_username,
        p_event,
        p_object_name,
        p_object_type,
        SYSTIMESTAMP
    );

END;
/

This can keep complex trigger logic cleaner.

29. What is the difference between a table trigger, schema trigger, and database trigger?

This is one of the most important interview comparisons.

Feature Table Trigger Schema Trigger Database Trigger
Main purpose Table DML Schema events Database-wide events
INSERT ✅ ❌ ❌
UPDATE ✅ ❌ ❌
DELETE ✅ ❌ ❌
CREATE ❌ ✅ ✅
ALTER ❌ ✅ ✅
DROP ❌ ✅ ✅
LOGON ❌ Possible ✅
STARTUP ❌ ❌ ✅
SHUTDOWN ❌ ❌ ✅
:OLD / :NEW Row-level ❌ ❌
FOR EACH ROW Possible ❌ ❌
Scope Table Schema Database

30. Database trigger vs schema trigger: example

Suppose there are three schemas:

HR
FINANCE
SALES

A schema trigger:

CREATE OR REPLACE TRIGGER trg_hr_ddl
AFTER CREATE ON SCHEMA
BEGIN
    ...
END;
/

is associated with one schema.

A database trigger:

CREATE OR REPLACE TRIGGER trg_all_ddl
AFTER CREATE ON DATABASE
BEGIN
    ...
END;
/

can monitor applicable DDL events across the database.

                 DATABASE
        +------------+------------+
        |            |            |
       HR         FINANCE       SALES
        |            |            |
     schema       schema        schema
     trigger      trigger       trigger

                 versus

             DATABASE TRIGGER
                    ↓
          +---------+---------+
          ↓         ↓         ↓
         HR      FINANCE    SALES

31. Can a database trigger protect production objects?

Yes.

For example:

CREATE OR REPLACE TRIGGER trg_protect_production
BEFORE DROP ON DATABASE
BEGIN

    IF ORA_DICT_OBJ_OWNER = 'HR'
       AND ORA_DICT_OBJ_NAME IN
           ('EMPLOYEES', 'PAYROLL')
    THEN

        RAISE_APPLICATION_ERROR(
            -20050,
            'Production object is protected'
        );

    END IF;

END;
/

This can add a safety layer against accidental DDL.

However, privileges, roles, deployment controls, and Oracle security features should be the primary protection mechanism. A trigger should not be the only defense.

32. What happens if the database trigger raises an exception?

Suppose:

CREATE OR REPLACE TRIGGER trg_block_drop
BEFORE DROP ON DATABASE
BEGIN

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

END;
/

Then:

DROP TABLE employees;

fails because the trigger raises an exception.

Execution flow:

DROP TABLE
    ↓
BEFORE DROP
    ↓
Exception
    ↓
DDL fails

33. Can multiple database triggers exist for the same event?

Yes, Oracle can have multiple triggers associated with events, subject to the applicable trigger rules and ordering options.

For example:

TRG_DDL_A
TRG_DDL_B
TRG_DDL_C

could all respond to a DDL event.

If execution order matters, Oracle provides trigger ordering mechanisms for applicable trigger types/versions. In practice, avoid relying on an arbitrary firing order unless you explicitly control it.

34. What is a good complete DDL-audit example?

Step 1: Create audit table

CREATE TABLE database_ddl_audit (
    audit_id       NUMBER GENERATED ALWAYS AS IDENTITY,
    username       VARCHAR2(128),
    event_name     VARCHAR2(30),
    object_owner   VARCHAR2(128),
    object_name    VARCHAR2(128),
    object_type    VARCHAR2(30),
    event_time     TIMESTAMP
);

Step 2: Create database trigger

CREATE OR REPLACE TRIGGER trg_database_ddl_audit
AFTER CREATE OR ALTER OR DROP ON DATABASE
BEGIN

    INSERT INTO database_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;
/

Step 3: Perform DDL

CREATE TABLE demo_table (
    id NUMBER
);

Then:

ALTER TABLE demo_table
ADD name VARCHAR2(100);

Then:

DROP TABLE demo_table;

Step 4: Check audit

SELECT
    username,
    event_name,
    object_owner,
    object_name,
    object_type,
    event_time
FROM database_ddl_audit
ORDER BY audit_id;

You should see records representing:

CREATE
ALTER
DROP

35. What are common database-trigger interview traps?

Trap 1

Question: Does a database trigger fire once per row?

Answer: ❌ No.

Database event triggers respond to database events, not individual table rows.

Trap 2

Question: Can database triggers use :OLD and :NEW?

Answer: ❌ No.

Those are row-level DML variables.

Trap 3

Question: Can database triggers use FOR EACH ROW?

Answer: ❌ No.

They are event triggers rather than row-level table triggers.

Trap 4

Question: Can a database trigger audit CREATE TABLE?

Answer: ✅ Yes.

For example:

AFTER CREATE ON DATABASE

Trap 5

Question: Can a database trigger prevent DROP TABLE?

Answer: ✅ Yes.

For example:

BEFORE DROP ON DATABASE

followed by RAISE_APPLICATION_ERROR.

Trap 6

Question: Can a database trigger handle LOGON?

Answer: ✅ Yes.

AFTER LOGON ON DATABASE

Trap 7

Question: Can a database trigger handle STARTUP?

Answer: ✅ Yes.

AFTER STARTUP ON DATABASE

Trap 8

Question: Can a database trigger execute COMMIT?

Answer: ❌ No.

36. Database-level trigger cheat sheet

Memorize this:

                 DATABASE TRIGGER
                 ----------------

Scope             Entire database

CREATE            ✅

ALTER             ✅

DROP              ✅

TRUNCATE          ✅

LOGON             ✅

LOGOFF            ✅

STARTUP           ✅

SHUTDOWN          ✅

SERVERERROR       ✅

INSERT            ❌ Not table DML

UPDATE            ❌ Not table DML

DELETE            ❌ Not table DML

:OLD              ❌

:NEW              ❌

FOR EACH ROW      ❌

COMMIT            ❌

The easiest way to remember:

TABLE
  ↓
INSERT / UPDATE / DELETE
  ↓
TABLE TRIGGER


SCHEMA
  ↓
DDL events in one schema
  ↓
SCHEMA TRIGGER


DATABASE
  ↓
Database-wide events
  ↓
DATABASE TRIGGER

One-line interview answer

A database-level trigger is an event trigger defined at the database scope that responds to events such as DDL, LOGON, LOGOFF, STARTUP, SHUTDOWN, and SERVERERROR, rather than to individual table rows.

Most Important Distinction

Table Trigger
    → Row/data changes

Schema Trigger
    → Events in one schema

Database Trigger
    → Database-wide events

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)