Right, so you’ve mastered row-level triggers and you’re feeling pretty good. You can make your database dance a little jig every time a single row is updated. Cute. But what if you want to conduct the entire orchestra? What if you need to catch an event as broad as “someone just dropped a table” or “a new function was created”? You don’t care about the individual rows; you care about the entire statement and its metaphysical impact on your database’s schema. For that, you need the big guns: Event Triggers.

These are the sentinels at the gate of your database’s structure. They fire for Data Definition Language (DDL) events—the big three: CREATE, ALTER, and DROP. And they operate at the whole-statement level. There is no BEFORE for a DROP; you can’t have an OLD or NEW record because you’re not dealing with rows of data, you’re dealing with objects. You’re playing in a different league now.

The Nuts and Bolts of CREATE EVENT TRIGGER

The syntax is refreshingly straightforward, a rare treat in the SQL world. You give it a name, tell it when to fire, and then tell it what to do.

CREATE EVENT TRIGGER log_ddl_drops
ON DDL_COMMAND_END
WHEN TAG IN ('DROP TABLE', 'DROP FUNCTION', 'DROP VIEW')
EXECUTE FUNCTION my_audit_function();

Let’s break this down. The ON clause can be one of two moments:

  • DDL_COMMAND_START: Fires right before the command executes. This is your chance to be a bouncer and check the guest list.
  • DDL_COMMAND_END: Fires right after the command executes. This is for logging, cleanup, or sending out a triumphant email that a new table was born.

The WHEN TAG IN is your filter. It’s how you avoid creating a trigger that fires on every single DDL command, which is a fantastic way to bring your database to its knees. The “tag” is the specific command. You can’t just say 'DROP'; you have to be specific, like 'DROP TABLE' or 'CREATE INDEX'. This is one of those design choices that feels pedantic but is actually there to save you from your own ambition.

The Trigger Function: Where the Magic (and Pain) Happens

This is the most important part. Your trigger function must be written as a function that returns event_trigger, not trigger. And it doesn’t get handed a nice, neat OLD and NEW record. Oh no. It gets its information from a set of special functions that peek into the system to see what just happened.

CREATE OR REPLACE FUNCTION my_audit_function()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    -- Log the specific command that fired us
    INSERT INTO ddl_audit_log (username, object_type, command_tag, command_text, executed_at)
    VALUES (
        current_user,
        (SELECT object_type FROM pg_event_trigger_ddl_commands()), -- Gets info about the DDL command
        (SELECT command_tag FROM pg_event_trigger_ddl_commands()),
        current_query(),
        NOW()
    );
END;
$$;

Here’s the kicker, and a common pitfall: pg_event_trigger_ddl_commands() returns a set. If your DDL command does more than one thing (like a DROP TABLE ... CASCADE that drops multiple dependent objects), this function returns a row for each object affected. My simple example above will fail with “more than one row returned” if your drop cascades. You almost always want to handle this with a FOR LOOP.

CREATE OR REPLACE FUNCTION robust_audit_function()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        INSERT INTO ddl_audit_log (username, object_type, object_identity, command_tag)
        VALUES (current_user, r.object_type, r.object_identity, r.command_tag);
    END LOOP;
END;
$$;

Why You Can’t Have BEFORE DROP (And What To Do Instead)

This is the single most common “gotcha.” People instinctively want a BEFORE DROP trigger to save a copy of a table being dropped. You can’t. By the time the DDL_COMMAND_END event fires, the table is already gone, vanished into the ether. This feels like a glaring omission, but it’s a architectural limitation.

Your workaround? Use DDL_COMMAND_START. You can’t prevent the drop from a function (well, you can by raising an exception, but that’s a nuclear option), but you can make a backup before it happens.

CREATE OR REPLACE FUNCTION rescue_dropped_table()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'DROP TABLE'
    LOOP
        -- This is a simplified example. In reality, you'd need dynamic SQL.
        EXECUTE format('CREATE TABLE %I_backup AS SELECT * FROM %I;', obj.object_identity, obj.object_identity);
        RAISE NOTICE 'Made a backup of % called %_backup', obj.object_identity, obj.object_identity;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER rescue_trigger
ON DDL_COMMAND_START
WHEN TAG IN ('DROP TABLE')
EXECUTE FUNCTION rescue_dropped_table();

Best practice? Be incredibly careful with this. If someone drops 50 tables at once, you’re about to make 50 backups. Your storage will hate you. This is a surgical tool, not a blunt instrument.

The Final Word: Power and Responsibility

Event triggers are phenomenally powerful. You can build an entire audit trail of your schema changes, enforce naming conventions, or prevent certain types of objects from being created altogether. But with great power comes great responsibility. A bug in a row-level trigger might mess up some data. A bug in an event trigger that fires on CREATE can prevent any new object from being created, effectively halting development. Test them in a isolated environment first. Filter them aggressively with WHEN TAG. And remember, you’re not just writing code; you’re installing a watchdog for the very structure of your database. Make sure it’s well-trained.