Thread: Create event triger

Create event triger

From

Łukasz Jarych

Date:

09 July 2018, 14:03:53

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

Re: Create event triger

From

Łukasz Jarych

Date:

10 July 2018, 11:56:52

No possible?

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

Re: Create event triger

From

Guillaume Lelarge

Date:

10 July 2018, 12:24:59

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.

Re: Create event triger

From

Łukasz Jarych

Date:

10 July 2018, 12:28:09

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 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.

Re: Create event triger

From

Guillaume Lelarge

Date:

10 July 2018, 12:29:38

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.

Re: Create event triger

From

Łukasz Jarych

Date:

10 July 2018, 12:30:18

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

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.

Re: Create event triger

From

Thomas Kellerer

Date:

10 July 2018, 13:05:09

Ł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/

Re: Create event triger

From

Adrian Klaver

Date:

10 July 2018, 16:56:20

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

Re: Create event triger

From

Adrian Klaver

Date:

11 July 2018, 03:44:39

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

Re: Create event triger

From

Ken Tanzer

Date:

11 July 2018, 11:29:57

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.

Re: Create event triger

From

Łukasz Jarych

Date:

13 July 2018, 12:30:16

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

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.