Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create trigger for all table in postgresql?

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();
like image 242
Eduardo Rafael Correa de Souza Avatar asked Apr 16 '15 12:04

Eduardo Rafael Correa de Souza


People also ask

Can we use one trigger for multiple tables?

No. But multiple triggers could invoke the same stored procedure.

Can we create a trigger on multiple tables in SQL?

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.

How do I create a trigger in PostgreSQL?

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.


1 Answers

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:

  • I select names of tables in my database using 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.
  • I use quote_ident(text) function which will put string inside double quote signs ("") if necessary (ie. names with spaces or capital letters require that).
  • When I have list of tables names I just concatenate them with some static strings to get my SQL commands.
  • I write that command using sub-query because I want you to get better idea of what's going on here. You may write a single query by putting quote_ident(table_schema) || '.' || quote_ident(table_name) in place of tab_name.
like image 169
Gabriel's Messanger Avatar answered Nov 01 '22 14:11

Gabriel's Messanger