Re: Create event triger - Mailing list pgsql-general

On Tue, Jul 10, 2018 at 5:45 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

select add_trigger('trg_test');

test=> \d trg_test

                    Table "public.trg_test"


  Column |       Type        | Collation | Nullable | Default


--------+-------------------+-----------+----------+---------


  id     | integer           |           |          |


  fld_1  | character varying |           |          |


Triggers:


     trg_test_change AFTER UPDATE ON trg_test FOR EACH STATEMENT EXECUTE
PROCEDURE ts_update()

To take this a step further, if you really have a lot of tables and want to do it automatically, you could do something like this:

SELECT table_name,add_trigger(table_name) FROM information_schema.tables WHERE table_schema='public';

This assumes that you want to add the trigger to _all_ your tables, and that you haven't made use of schemas and so your tables are all in the public schema. 

If that's not the case, you could adjust accordingly.  It would be safest to just pull the table names first, make sure the list is what you want, and then run with the add_trigger.  So start with this:

SELECT table_name FROM information_schema.tables WHERE table_schema='public';

and if the list of tables is what you want, then run with the add_trigger included.

Cheers,

Ken

--

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.