Introduction to the PostgreSQL event trigger

A regular trigger fires whenever anINSERT,UPDATE,DELETE, orTRUNCATE event occurs on an associated table.

To automatically respond to events related to data definition language (DDL) statements, you can use an event trigger.

An event trigger is a trigger that fires whenever an associated event occurs in the database.

PostgreSQL supports the following events:

• ddl_command_start
• ddl_command_end
• table_rewrite
• sql_drop

Theddl_command_start events before PostgreSQL executes theCREATE,ALTER,DROP,GRANT,REVOKE,SECURITYLABEL, andCOMMENT statements. For complete commands that the event trigger supports, read more on theevent trigger firing matrix.

Please note that theddl_command_start does not occur for shared objects like databases, tablespaces, and roles.

Theddl_command_end occurs after the execution of the above DDL statements.

Thesql_drop event occurs whenever you drop a database object, just before theddl_command_end event.

Thetable_rewrite event occurs before you rewrite a table using theALTER TABLE orALTER TYPE statement.

To create an event trigger, you follow these steps:

First,define a function that will execute when the event trigger fires:

CREATE OR REPLACE FUNCTION event_trigger_function_name()RETURNS EVENT_TRIGGERAS$$BEGIN   -- trigger logic   -- ...   -- no RETURN statementEND;$$;

The event trigger function returnsEVENT_TRIGGER instead ofTRIGGER. Additionally, it does not have anyRETURN statement like a regular trigger function.

Second, create an event trigger using theCREATE EVENT TRIGGER statement:

CREATE EVENT TRIGGER trigger_nameON eventEXECUTE FUNCTION event_trigger_function_name()

PostgreSQL event trigger example

First,create a table calledaudits to store audit logs for commands:

CREATE TABLE audits (    idSERIAL PRIMARY KEY,    usernameVARCHAR(100)NOT NULL,    event VARCHAR(50)NOT NULL,    commandTEXT NOT NULL,    executed_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Theaudits table will record the username, event, command, and timestamp when the command is executed.

Second, define an event trigger function that executes whenever a relevant event occurs:

CREATE OR REPLACE FUNCTION audit_command()RETURNS EVENT_TRIGGERAS $$BEGIN    INSERT INTO audits (username,event , command)    VALUES (session_user, TG_EVENT, TG_TAG );END;$$LANGUAGE plpgsql;

Theaudit_command() function inserts audit records into theaudits table.

Third, create an event trigger that associates the function with DDL commands:

CREATE EVENT TRIGGER audit_ddl_commandsON ddl_command_endEXECUTE FUNCTION audit_command();

Fourth, execute aCREATE TABLE command:

CREATE TABLE regions(    idSERIAL PRIMARY KEY,    name VARCHAR(255)NOT NULL);

Fifth, retrieve data from theaudits table:

SELECT * FROM audits;

Output:

id | username |      event      |   command    |        executed_at----+----------+-----------------+--------------+----------------------------  1 | postgres | ddl_command_end | CREATE TABLE | 2024-03-29 12:12:38.773734(1 row)

Regular trigger vs. Event trigger

The following table compares regular triggers and event triggers:

Summary

• An event trigger is a trigger that fires when an event related to the DDL statement occurs.
• Use theCREATE EVENT TRIGGER statement to define a new event trigger.

Last updated onMarch 29, 2024

On this page

• Introduction to the PostgreSQL event trigger
• PostgreSQL event trigger example
• Regular trigger vs. Event trigger
• Summary