I have a trigger, but I need to associate with all tables of the my postgres. Is there a command like this below?
CREATE TRIGGER delete_data_alldb
BEFORE DELETE
ON ALL DATABASE
FOR EACH ROW
EXECUTE PROCEDURE delete_data();
No. But multiple triggers could invoke the same stored procedure.
SQL Server allows multiple triggers on the table for the same event and there is no defined order of execution of these triggers. We can set the order of a trigger to either first or last using procedure sp_settriggerorder. There can be only one first or last trigger for each statement on a table.
Syntax. CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ]; Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.
Well there is no database-wide trigger creation but for all such bulk-admin-operations you could use PostgreSQL system tables to generate queries for you instead of writing them by hand. In this case you could run:
SELECT
'CREATE TRIGGER '
|| tab_name
|| ' BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();' AS trigger_creation_query
FROM (
SELECT
quote_ident(table_schema) || '.' || quote_ident(table_name) as tab_name
FROM
information_schema.tables
WHERE
table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_schema NOT LIKE 'pg_toast%'
) tablist;
This will get you set of strings which are SQL commands like:
CREATE TRIGGER schema1.table1 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema1.table2 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema1.table3 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema2.table1 BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
CREATE TRIGGER schema2."TABLE2" BEFORE DELETE ON ALL DATABASE FOR EACH ROW EXECUTE PROCEDURE delete_data();
...
etc
You just need to run them at once (either by psql
or pgAdmin).
Now some explanation:
information_schema.tables
system table. Because there are data of literally all tables, remember to exclude pg_catalog
and information_schema
schemas and toast tables from your select
.quote_ident(text)
function which will put string inside double quote signs (""
) if necessary (ie. names with spaces or capital letters require that).quote_ident(table_schema) || '.' || quote_ident(table_name)
in place of tab_name
.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With