On my previous post, I suggested how to create a schema, a table and a trigger function, in order to audit a PostgreSQL database.
To audit a table, you would have to create a trigger for that table, calling the code from the generic trigger.
In my case, I want to audit every table in the database, and I think most people will likely want to audit every table, or at least most tables in the database.
To escape the tedious task of writing code to implement that n-times, I put together a script that will generate an audit trigger for each table in the database.If you want to apply it to a restricted number of tables instead, you could easily change it to read the table names from a list.
CREATE OR REPLACE FUNCTION create_audit_triggers() RETURNS void AS $BODY$ DECLARE r RECORD; _string varchar ( 1000 ); BEGIN FOR r IN SELECT distinct tablename FROM pg_catalog.pg_tables where schemaname='public' LOOP IF NOT EXISTS(SELECT * FROM information_schema.triggers WHERE event_object_table = r.tablename AND trigger_name = r.tablename || '_audit' ) THEN --raise info '%' , r.tablename; _string :=' CREATE TRIGGER ' || r.tablename || '_audit ' || ' AFTER INSERT OR UPDATE OR DELETE ON ' || r.tablename || ' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();'; raise info '%', _string; EXECUTE ( _string ) ; END IF ; end loop; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION update_info_tables2() OWNER TO postgres;
This will check if the trigger already exists (for which an error would be raised!), and generate the triggers during the blink of an eye (depending on the size of your database!). Thus you could use it for updating the triggers, after you added a couple of tables in the database.