Create event triger

No possible?

Jacek

pon., 9 lip 2018 o 13:03 Łukasz Jarych <jaryszek@gmail.com> napisał(a):

2018-07-10 10:56 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:

No possible?

Nope, you need to set up the trigger on each table.

Jacek

pon., 9 lip 2018 o 13:03 Łukasz Jarych <jaryszek@gmail.com> napisał(a):

Hi,

i have small database and i am tracking changes using trigger:

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig

FOR EACH ROW EXECUTE PROCEDURE change_trigger();

It is possible to create general trigger for all tables?
Like event trigger?

It would be very helpful for me.
Now i have to set up this trigger on each table.

Best,
Jacek

--
Guillaume.

Thank you very much Guillaume.

Do you know maybe any function to do it automatically?

Best,
Jacek

wt., 10 lip 2018 o 11:25 Guillaume Lelarge <guillaume@lelarge.info>
napisał(a):

2018-07-10 11:28 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:

Thank you very much Guillaume.

Do you know maybe any function to do it automatically?

Nope, but it should be easy to write a shell script or a DO script to do it.

Best,

Jacek

wt., 10 lip 2018 o 11:25 Guillaume Lelarge <guillaume@lelarge.info>
napisał(a):

2018-07-10 10:56 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:

No possible?

Nope, you need to set up the trigger on each table.

Jacek

pon., 9 lip 2018 o 13:03 Łukasz Jarych <jaryszek@gmail.com> napisał(a):

Hi,

i have small database and i am tracking changes using trigger:

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig

FOR EACH ROW EXECUTE PROCEDURE change_trigger();

It is possible to create general trigger for all tables?
Like event trigger?

It would be very helpful for me.
Now i have to set up this trigger on each table.

Best,
Jacek

--
Guillaume.

--
Guillaume.

Maybe yes,

but for me when i am learning it is not...

Best,
Jacek

wt., 10 lip 2018 o 11:29 Guillaume Lelarge <guillaume@lelarge.info>
napisał(a):

Łukasz Jarych schrieb am 09.07.2018 um 13:03:

i have small database and i am tracking changes using trigger:

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON t_trig

FOR EACH ROW EXECUTE PROCEDURE change_trigger();

It is possible to create general trigger for all tables?
Like event trigger?

It would be very helpful for me. 
Now i have to set up this trigger on each table.

Maybe pgaudit is a better solution?

https://www.pgaudit.org/

On 07/10/2018 02:30 AM, Łukasz Jarych wrote:

Maybe yes,

but for me when i am learning it is not...

How do you do CREATE TABLE now, ad hoc in the client or via scripts?

If via scripts you could create a template script for the trigger and
then just fill in the table name as needed.

A function to add the trigger would be more involved and I do not have
the time at the moment to create an example. Will see if I can come up
with something later.

Best,
Jacek

--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/10/2018 02:30 AM, Łukasz Jarych wrote:

Maybe yes,

but for me when i am learning it is not...

The example function. It is a minimal example but it should serve as a
starting point.:

CREATE OR REPLACE FUNCTION public.add_trigger(tbl_name character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
EXECUTE 'CREATE TRIGGER ' || quote_ident(tbl_name||'_change') || '
AFTER UPDATE ON ' || quote_ident(tbl_name) || ' EXECUTE PROCEDURE
ts_update()';
END;
$function$
;

create table trg_test(id int, fld_1 varchar);

test=> \d trg_test

Table "public.trg_test"

Column | Type | Collation | Nullable | Default

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

id | integer | | |

fld_1 | character varying | |

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()

Best,
Jacek

wt., 10 lip 2018 o 11:29 Guillaume Lelarge <guillaume@lelarge.info

--
Adrian Klaver
adrian.klaver@aklaver.com

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/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Hi Guys,

sorry for my late answer. I tested this today and working like a charm!

You are brilliant ! thank you, saved my ass!

Best,
Jacek

śr., 11 lip 2018 o 10:30 Ken Tanzer <ken.tanzer@gmail.com> napisał(a):