How to Use Event Triggers in PostgreSQL

How do I create and manage PostgreSQL event triggers?

Event triggers fire a user-defined function in response to database-wide events such as DDL commands, enabling global auditing, security, and automation.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What are event triggers in PostgreSQL?

Event triggers execute a stored function when high-level events—CREATE, ALTER, DROP, TRUNCATE, or a SQL session start—occur. Unlike table triggers, they watch the entire database, letting you audit DDL, block risky changes, or rebuild metadata automatically.

When should I use event triggers?

Use event triggers to log schema changes, enforce naming conventions, refresh materialized views after bulk loads, or prevent table drops in production. Because they run for every matching command, reserve them for tasks that truly need database-wide scope.

How do I create an event trigger?

Write a SECURITY DEFINER function in PL/pgSQL, then bind it with CREATE EVENT TRIGGER. Choose the event (ddl_command_start, ddl_command_end, sql_drop, etc.) and optional filter tags such as ALTER TABLE.

Example: Audit DDL changes

The snippet below records every table change in an audit_ddl table. In an ecommerce app, this helps track accidental changes to Orders or Products.

CREATE TABLE audit_ddl(log_id serial PRIMARY KEY, event text, command text, executed_at timestamptz DEFAULT now()); CREATE OR REPLACE FUNCTION log_ddl() RETURNS event_trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO audit_ddl(event, command) SELECT tg_tag, current_query(); END;$$; CREATE EVENT TRIGGER trg_audit_ddl ON ddl_command_end EXECUTE FUNCTION log_ddl();

How do I list event triggers?

Query pg_event_trigger or run \dE+ in psql to see trigger names, events, and enabled status.

How do I change or drop an event trigger?

Disable a trigger with ALTER EVENT TRIGGER trg_name DISABLE;, enable it again with ENABLE;, or remove it entirely using DROP EVENT TRIGGER trg_name;.

Best practices for event triggers

Keep trigger functions lightweight—off-load heavy work to background jobs. Always wrap data-modifying statements in EXCEPTION blocks to avoid cascading failures. Version-control the trigger code with the rest of your schema.

Common mistakes and how to avoid them

Recursive effects: An event trigger that issues DDL can fire itself repeatedly. Use SET LOCAL session_replication_role = replica or check tg_tag to short-circuit recursion.

Over-filtering: Forgetting WHEN TAG IN ('ALTER TABLE') means the trigger fires on every DDL, hurting performance. Specify only the tags you need.

Why How to Use Event Triggers in PostgreSQL is important

How to Use Event Triggers in PostgreSQL Example Usage


-- Auto-refresh materialized view after bulk price update
CREATE OR REPLACE FUNCTION refresh_product_stats() RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
  PERFORM refresh_materialized_view('product_sales_stats');
END;$$;

-- Fire after ALTER TABLE Products
CREATE EVENT TRIGGER trg_refresh_product_stats
  ON ddl_command_end
  WHEN TAG IN ('ALTER TABLE')
  EXECUTE FUNCTION refresh_product_stats();

How to Use Event Triggers in PostgreSQL Syntax


CREATE EVENT TRIGGER <trigger_name>
    ON { ddl_command_start | ddl_command_end | sql_drop | table_rewrite | pg_authid_drop }
    [ WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE', 'DROP TABLE', ... ) ]
    EXECUTE FUNCTION <function_name>();

-- Example in an ecommerce context
-- Prevent DROP TABLE on critical tables like Orders and Customers
CREATE OR REPLACE FUNCTION block_drop() RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_TAG = 'DROP TABLE' AND TG_TABLE_NAME IN ('orders','customers') THEN
     RAISE EXCEPTION 'Dropping critical tables is forbidden.';
  END IF;
END;$$;

CREATE EVENT TRIGGER trg_block_drop
  ON sql_drop
  WHEN TAG IN ('DROP TABLE')
  EXECUTE FUNCTION block_drop();

Common Mistakes

Defining an event trigger function that runs heavy queries directly. This slows every DDL command. Fix by queuing work to a background job or NOTIFY/LISTEN.
Leaving a trigger enabled in development and production without version control. Schema migrations may fail. Fix by scripting ENABLE/DISABLE in migration files.