PostgreSQL Server Programming - Second Edition

Creating an audit trail

Let's now take a look at the complete example of an event trigger that creates an audit trail of some DDL commands in the database:

CREATE TABLE track_ddl ( event text, command text, ddl_time timestamptz, usr text ); CREATE OR REPLACE FUNCTION track_ddl_function() RETURNS event_trigger AS $$ BEGIN INSERT INTO track_ddl values(tg_tag, tg_event, now(), session_user); RAISE NOTICE 'DDL logged'; END $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE EVENT TRIGGER track_ddl_event ON ddl_command_start WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE') EXECUTE PROCEDURE track_ddl_function(); CREATE TABLE event_check(i int); SELECT * FROM track_ddl; -[ RECORD 1 ]------------------------ event | CREATE TABLE command | ddl_command_start ...