Re: Create event triger
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 napisał(a):
>
>
> On Tue, Jul 10, 2018 at 5:45 PM Adrian Klaver
> 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/ *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
> to
> learn more about AGENCY or
> follow the discussion.
>
Re: Create event triger
On Tue, Jul 10, 2018 at 5:45 PM Adrian Klaver
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/ *
ken.tan...@agency-software.org
(253) 245-3801
Subscribe to the mailing list
to
learn more about AGENCY or
follow the discussion.
Re: Create event triger
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
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Create event triger
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.kla...@aklaver.com
Re: Create event triger
Ł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
Maybe yes, but for me when i am learning it is not... Best, Jacek wt., 10 lip 2018 o 11:29 Guillaume Lelarge napisał(a): > 2018-07-10 11:28 GMT+02:00 Łukasz Jarych : > >> 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 >> napisał(a): >> >>> 2018-07-10 10:56 GMT+02:00 Łukasz Jarych : >>> No possible? >>> Nope, you need to set up the trigger on each table. >>> >>> Jacek pon., 9 lip 2018 o 13:03 Łukasz Jarych 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
2018-07-10 11:28 GMT+02:00 Łukasz Jarych : > 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 > napisał(a): > >> 2018-07-10 10:56 GMT+02:00 Łukasz Jarych : >> >>> No possible? >>> >>> >> Nope, you need to set up the trigger on each table. >> >> Jacek >>> >>> pon., 9 lip 2018 o 13:03 Łukasz Jarych 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
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 napisał(a): > 2018-07-10 10:56 GMT+02:00 Łukasz Jarych : > >> No possible? >> >> > Nope, you need to set up the trigger on each table. > > Jacek >> >> pon., 9 lip 2018 o 13:03 Łukasz Jarych 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
2018-07-10 10:56 GMT+02:00 Łukasz Jarych : > No possible? > > Nope, you need to set up the trigger on each table. Jacek > > pon., 9 lip 2018 o 13:03 Łukasz Jarych 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
No possible? Jacek pon., 9 lip 2018 o 13:03 Łukasz Jarych 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 >
Create event triger
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
