Use Event Triggers for DDL

You’ve spent weeks perfecting your schema, only to discover that a teammate ran ALTER TABLE in production and dropped a critical constraint. No audit log, no trace of who did it or when. Recovering from unversioned DDL changes is painful: you dig through migration files, chat history, and hope the change wasn’t destructive. PostgreSQL has a built-in solution that most developers overlook: event triggers. Unlike regular triggers, which fire on row-level DML, event triggers fire on DDL events like CREATE TABLE, ALTER TABLE, and DROP TABLE. They give you a safety net to log, audit, or even block schema changes globally. In this lesson, you’ll learn how to use event triggers for DDL changes — from the core concept to a hands-on implementation that you can adapt to your own workflows.

The Problem This Lesson Solves

Databases evolve. Tables get renamed, columns get added, indexes change. The pain: uncontrolled DDL changes are silent and often irreversible. A single DROP COLUMN in production can cascade into broken API responses, missing analytics, and hours of debugging. Regular triggers don’t help because they only fire on INSERT, UPDATE, or DELETE — not on schema modifications.

Consider a typical breach: a developer runs ALTER TABLE orders ADD COLUMN discount numeric; in a maintenance window. No one recorded it. The next day, the application fails because the new column wasn’t handled. Even if you have migration files, they can drift from the live schema, and not all changes go through the migration pipeline.

Event triggers exist to close that gap. They allow you to run a function whenever a DDL command is executed — globally, across the entire database. This means you can tie a central audit log, enforce naming conventions, or send alerts for dangerous operations like DROP TABLE. This lesson shows you how to use event triggers for DDL changes, turning chaos into controlled, observable evolution.

Core Concept / Mental Model

Think of event triggers as airport security for your schema management. Every DDL command—like a passenger boarding a flight—must pass through a checkpoint. You can inspect the command, log it, or stop it altogether before it affects the live plane.

In PostgreSQL, there are two levels:

• Regular triggers operate on rows and fire for DML (e.g., INSERT). They are defined on specific tables and have access to row data.
• Event triggers operate on the whole database and fire for DDL (e.g., CREATE TABLE). They do not have row-level access, but they can inspect the command tag and even the full command text.

It’s helpful to contrast the two:

Event triggers are declared at the database level, using event names such as ddl_command_start, ddl_command_end, and sql_drop. When any DDL command is issued, the trigger function runs at the specified point in the command execution lifecycle.

Key Events

• ddl_command_start — fires right before the DDL command is executed. Use this to block or log upcoming changes.
• ddl_command_end — fires after the command completes. Use this for auditing actions that were successful.
• sql_drop — fires when a DROP statement removes objects. Use this to track what was dropped (even before deletion).
• table_rewrite — fires when a table is rewritten (e.g., ALTER TABLE ... ADD COLUMN with a default). Use for performance warnings.

By combining these events, you can build a comprehensive change audition and control layer.

How It Works Step by Step

Event triggers rely on a trigger function that returns event_trigger. That function is then attached to a specific event using CREATE EVENT TRIGGER. Here’s the logical flow:

1. Create a trigger function (using PL/pgSQL, Python, or another language).
2. Register the trigger on one or more events.
3. When an eligible DDL command is issued, PostgreSQL invokes the function at the right point.
4. The function can read event metadata via pg_event_trigger_ddl_commands() (inside ddl_command_end or sql_drop) and decide to log, notify, or raise an exception.

Step-by-step breakdown:

1. Choose the event(s) that matter to you. If you need to audit all DDL changes, use ddl_command_end. If you need to prevent certain commands, use ddl_command_start.
2. Write the trigger function to inspect the command. Use current_query() or pg_event_trigger_ddl_commands() to extract details like command tag and object identity.
3. Create an audit table to record changes. A minimal table would store the timestamp, user, command tag, and the full query text.
4. Create the event trigger to call your function.
5. Test with a variety of DDL commands and verify the audit log is populated.

Hands-On Walkthrough

Let’s implement a practical audit trail for DDL changes. We’ll start by creating a function and a table to store the logs, then attach event triggers.

Step 1: Create an audit table

CREATE TABLE ddl_audit_log (
    id serial PRIMARY KEY,
    event_type text,
    command_tag text,
    object_type text,
    schema_name text,
    object_name text,
    query text,
    change_time timestamptz DEFAULT now(),
    changed_by text DEFAULT current_user
);

Step 2: Create the event trigger function

We’ll use PL/pgSQL. The function will be called after any DDL command ends and will insert a record into the audit table.

CREATE OR REPLACE FUNCTION audit_ddl_changes()
RETURNS event_trigger AS $$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        INSERT INTO ddl_audit_log (
            event_type,
            command_tag,
            object_type,
            schema_name,
            object_name,
            query
        ) VALUES (
            tg_event,
            r.command_tag,
            r.object_type,
            r.schema_name,
            r.object_identity,
            current_query()
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Pro tip: current_query() returns the full SQL text, which can be huge. For sensitive environments, consider logging only the command tag and object identity instead of the entire query.

Step 3: Create the event trigger

CREATE EVENT TRIGGER trg_audit_ddl
    ON ddl_command_end
    EXECUTE FUNCTION audit_ddl_changes();

Step 4: Test it

-- Create a test table
CREATE TABLE example (id int);

-- Add a column
ALTER TABLE example ADD COLUMN name text;

-- Check the audit log
SELECT event_type, command_tag, schema_name, object_name, changed_by
FROM ddl_audit_log;

Expected output (something like):

 event_type | command_tag | schema_name | object_name        | changed_by
------------+-------------+-------------+--------------------+------------
 ddl_command_end | CREATE TABLE | public      | public.example     | postgres
 ddl_command_end | ALTER TABLE  | public      | public.example     | postgres
(2 rows)

Blocking a dangerous DDL (advanced)

You can also use ddl_command_start to prevent certain commands. Here’s a trigger that blocks DROP TABLE for tables whose name starts with payments_:

CREATE OR REPLACE FUNCTION block_payment_drop()
RETURNS event_trigger AS $$
BEGIN
    -- Check the command tag and object identity
    IF tg_event = 'ddl_command_start' THEN
        IF EXISTS (
            SELECT 1 FROM pg_event_trigger_ddl_commands()
            WHERE command_tag = 'DROP TABLE'
              AND object_identity LIKE '%payments_%'
        ) THEN
            RAISE EXCEPTION 'Dropping payment tables is not allowed. Contact DBA.';
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER trg_block_payment_drop
    ON ddl_command_start
    EXECUTE FUNCTION block_payment_drop();

Now try to drop a protected table:

DROP TABLE IF EXISTS payments_2023;

Result:

ERROR:  Dropping payment tables is not allowed. Contact DBA.

Compare Options / When to Choose What

Event triggers are powerful but not the only mechanism for tracking DDL changes. Here’s a comparison with other approaches:

When to use event triggers:

• Need immediate, automatic logging of schema changes.
• Need to enforce rules like “never drop tables in schema payments”.
• You want a safety net even if a developer bypasses migration tools.

When to prefer migration tools:

• Your team already follows strict migration workflows — event triggers are redundant.
• You need version control and rollback — event triggers cannot revert schema changes.

Variations:

• Use sql_drop instead of ddl_command_end to capture dropped objects even before they're gone.
• Write the trigger function in PL/Python for more complex logic, especially if you need to parse the query.
• Use NOTIFY to send real-time alerts to external monitoring systems.

Troubleshooting & Edge Cases

Event triggers can be tricky in practice. Here are common pitfalls and their fixes:

Pro tip: When using RAISE EXCEPTION to block a command, the exception message appears in the client and the DDL is rolled back. This is great for guards, but be careful not to block your own maintenance scripts.

Edge case: If you use table_rewrite

table_rewrite is not available in all versions (it was added in PostgreSQL 13). It fires when an ALTER TABLE causes a full rewrite (e.g., adding a column with a default). Use it to warn about long-running locks.

What You Learned & What's Next

You now know how to use event triggers for DDL changes. You can create event triggers that log or block schema modifications on a database level. You learned about the different event types (ddl_command_start, ddl_command_end, sql_drop, table_rewrite) and how to write trigger functions in PL/pgSQL. You can audit schema evolution to maintain a clean, observable database.

Next in this PostgreSQL track: you’ll likely dive into advanced indexing strategies or query tuning. With event triggers in your toolbox, you can keep your index changes audited too.

Go ahead and experiment: create a trigger that logs ALTER TABLE commands into a dedicated table with the old and new column definitions. You’re now the database guardian you wished you had.