DDL Event Trigger

Unlike regular triggers, which are attached to a single table and capture only DML (Data Manipulation Language) events, event triggers are global to a particular database and are capable of capturing DDL (Data Definition Language) events.

• The ddl_command_start event occurs just before the execution of a CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT or REVOKE command. No check whether the affected object exists or doesn't exist is performed before the event trigger fires.

• The ddl_command_end event occurs just after the execution of this same set of commands.

We can use event triggers to log DDL events in a table. Below is an example of how to create an event trigger to log DDL events in a table.

CREATE TABLE ddl_logs (
    log_id SERIAL PRIMARY KEY,
    event_type TEXT,
    object_type TEXT,
    schema_name TEXT,
    object_name TEXT,
    ddl_command TEXT,
    executed_by TEXT,
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        INSERT INTO ddl_logs (event_type, object_type, schema_name, object_name, ddl_command, executed_by)
        VALUES (r.command_tag, r.object_type, r.schema_name, r.object_identity, r.command, current_user);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER track_ddl_changes ON ddl_command_end
EXECUTE FUNCTION log_ddl_event();