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
ddl_time | 2014-04-13 16:58:40.331385
usr | testusrThe example is actually quite simple. Here's what we have done in the example:
First, we created a table where we store the audit log. The table is quite simple at...