Database Event Triggers FAQS

1. What is an Oracle Database Event Trigger?

An event trigger (also called a system trigger) is a trigger that fires automatically in response to specific database or system events, rather than DML operations (INSERT, UPDATE, DELETE).

These events include:

• Database startup/shutdown
• User logon/logoff
• DDL statements (CREATE, ALTER, DROP)
• Server errors

2. What are the types of database event triggers?

Oracle supports:

Database-Level Triggers

Fire for events affecting the whole database.
Examples:

• AFTER STARTUP
• BEFORE SHUTDOWN
• AFTER LOGON ON DATABASE
• AFTER DDL ON DATABASE
• AFTER SERVERERROR ON DATABASE

Schema-Level Triggers

Fire for events affecting a specific schema.
Example:

• AFTER DDL ON schema_name.SCHEMA

3. How is an event trigger different from a DML trigger?

Feature	Event Trigger	DML Trigger
Fires on	System/DDL events	INSERT/UPDATE/DELETE
Scope	Database or Schema	Table or View
Example	Log user logins	Audit row changes

4. How do you create a database event trigger?

Example: Log every user login.

CREATE OR REPLACE TRIGGER logon_audit

AFTER LOGON ON DATABASE

BEGIN

   INSERT INTO audit_log (username, logon_time)

   VALUES (USER, SYSDATE);

END;

/

5. What privileges are required to create event triggers?

To create a database-level trigger, you need:

• ADMINISTER DATABASE TRIGGER privilege

For schema-level triggers:

• CREATE TRIGGER privilege

6. What events can be used with database triggers?

Common events include:

• AFTER STARTUP
• BEFORE SHUTDOWN
• AFTER LOGON
• BEFORE LOGOFF
• AFTER DDL
• AFTER SERVERERROR
• AFTER SUSPEND

7. How can event triggers be used?

Common use cases:

• Auditing DDL changes
• Tracking user sessions
• Blocking specific users
• Enforcing naming conventions
• Logging database errors
• Restricting schema changes

8. Can event triggers prevent actions?

Yes.

For example, to prevent DROP operations:

CREATE OR REPLACE TRIGGER prevent_drop

BEFORE DROP ON DATABASE

BEGIN

   RAISE_APPLICATION_ERROR(-20000, 'DROP is not allowed!');

END;

/

9. How do you disable or drop an event trigger?

Disable:

ALTER TRIGGER trigger_name DISABLE;

Enable:

ALTER TRIGGER trigger_name ENABLE;

Drop:

DROP TRIGGER trigger_name;

10. Where are event trigger definitions stored?

They are stored in the data dictionary views such as:

• DBA_TRIGGERS
• ALL_TRIGGERS
• USER_TRIGGERS

11. Are event triggers performance-intensive?

Usually lightweight, but:

• Poorly written triggers can impact login performance.
• Avoid heavy processing inside AFTER LOGON triggers.
• Keep logic simple and efficient.

12. What are common mistakes?

• Forgetting required privileges
• Causing recursive DDL loops
• Blocking SYS users unintentionally
• Not handling exceptions properly
• Committing inside triggers unnecessarily

13. Can you audit DDL statements using event triggers?

Yes. Example:

CREATE OR REPLACE TRIGGER ddl_audit

AFTER DDL ON DATABASE

BEGIN

   INSERT INTO ddl_log

   VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_NAME, USER, SYSDATE);

END;

/

Oracle provides built-in functions like:

• ORA_SYSEVENT
• ORA_DICT_OBJ_NAME
• ORA_LOGIN_USER

14. Can event triggers fire for specific objects only?

At schema level, yes.
At database level, you must filter inside the trigger using:

IF ORA_DICT_OBJ_TYPE = 'TABLE' THEN

   -- logic

END IF;

15. Are event triggers available in all Oracle versions?

Database event triggers are supported in most modern versions of Oracle Database (8i and later), with enhancements in newer releases.