How to Create Event Triggers in Oracle

How do I create and manage event triggers in Oracle?

Oracle event triggers (system triggers) automatically fire on database or schema events such as LOGON, DDL, STARTUP, and SHUTDOWN.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is an Oracle event trigger?

Oracle event triggers—also called system triggers—execute PL/SQL blocks when specific database or schema events occur (e.g., LOGON, ALTER, DROP).

When should I use event triggers?

Use them for auditing DDL, enforcing security policies, initializing session settings, or capturing system-wide metrics without touching application code.

How do I create an event trigger?

Write a CREATE TRIGGER statement, pick BEFORE/AFTER timing, list one or more events, scope it to DATABASE or SCHEMA, then add PL/SQL logic.

Minimal template

CREATE OR REPLACE TRIGGER trg_name AFTER LOGON ON DATABASE BEGIN -- logic END; /

What events are available?

LOGON, LOGOFF, STARTUP, SHUTDOWN, CREATE, ALTER, DROP, GRANT, REVOKE, and many more. DDL events can be combined with OR.

How do I audit DDL on the Products table?

CREATE TABLE ddl_audit ( username VARCHAR2(30), action VARCHAR2(30), object_name VARCHAR2(128), action_time TIMESTAMP DEFAULT SYSTIMESTAMP ); CREATE OR REPLACE TRIGGER trg_ddl_audit AFTER CREATE OR ALTER OR DROP ON SCHEMA DECLARE BEGIN IF ora_dict_obj_name = 'PRODUCTS' THEN INSERT INTO ddl_audit(username, action, object_name) VALUES (USER, ora_sysevent, ora_dict_obj_name); END IF; END; /

Best practices for event triggers?

Keep code lightweight, log errors, avoid COMMIT/ROLLBACK, and monitor performance. Always document trigger purpose and owner.

Common mistakes and fixes

Using COMMIT inside a trigger—breaks atomicity. Remove COMMIT and let calling session commit.

Forgetting to limit scope—triggers that fire on every DDL hurt performance. Add IF filters or restrict to specific events.

Are there performance impacts?

Yes. Triggers run synchronously, so heavy logic slows the originating event. Keep work minimal or offload to background jobs.

How do I disable and enable a trigger?

ALTER TRIGGER trg_ddl_audit DISABLE; ALTER TRIGGER trg_ddl_audit ENABLE;

How do I drop a trigger?

DROP TRIGGER trg_ddl_audit;

Need a quick checklist?

1) Identify event and scope. 2) Create audit/support table if needed. 3) Write CREATE TRIGGER. 4) Test. 5) Document and monitor.

FAQ

Can I temporarily disable an event trigger?

Yes—use ALTER TRIGGER trigger_name DISABLE, then ENABLE when done.

How can I list all event triggers?

Query DBA_TRIGGERS or USER_TRIGGERS: SELECT trigger_name, triggering_event FROM user_triggers WHERE base_object_type IS NULL;

Do LOGON triggers slow user connections?

Only if they perform heavy work. Keep LOGON logic short—or push complex tasks to DBMS_SCHEDULER jobs.

Why How to Create Event Triggers in Oracle is important

How to Create Event Triggers in Oracle Example Usage


--Log any price column modification on the Products table
CREATE OR REPLACE TRIGGER trg_price_change
AFTER ALTER ON PRODUCTS
DECLARE
BEGIN
  INSERT INTO ddl_audit(username, action, object_name)
  VALUES (USER, ora_sysevent, ora_dict_obj_name);
END;
/

How to Create Event Triggers in Oracle Syntax


CREATE [OR REPLACE] TRIGGER trigger_name
  {BEFORE | AFTER} event1 [OR event2 ...]
  ON {DATABASE | SCHEMA}
DECLARE
  -- declarations
BEGIN
  -- business logic
END;
/

Example with ecommerce context:

CREATE OR REPLACE TRIGGER audit_product_ddl
AFTER CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
BEGIN
  IF ora_dict_obj_name = 'PRODUCTS' THEN
    INSERT INTO ddl_audit(username, action, object_name)
    VALUES (USER, ora_sysevent, ora_dict_obj_name);
  END IF;
END;
/

Common Mistakes

Including COMMIT or ROLLBACK inside the trigger body. This breaks atomicity and can raise ORA-04092. Fix: remove explicit commits—let the invoking statement control the transaction.
Creating a trigger on every DDL event without filters, causing performance degradation. Fix: add IF checks for specific tables or limit events (e.g., AFTER DROP ON SCHEMA WHEN ora_dict_obj_name='PRODUCTS').

Frequently Asked Questions (FAQs)

Can I disable an event trigger without dropping it?

Yes. Run ALTER TRIGGER trigger_name DISABLE to turn it off, and ALTER TRIGGER trigger_name ENABLE to restore it.

How do I view the source code of an event trigger?

Query USER_SOURCE or use SQL Developer: SELECT text FROM user_source WHERE name='TRIGGER_NAME' ORDER BY line;

Are event triggers replicated in Data Guard?

Yes. Triggers are data dictionary objects and are transferred to standby databases, but they only fire on the primary unless the standby is opened read-write.