public inbox for [email protected]
help / color / mirror / Atom feedRe: Does trigger only accept functions?
2+ messages / 2 participants
[nested] [flat]
* Re: Does trigger only accept functions?
@ 2024-06-10 20:20 Adrian Klaver <[email protected]>
2024-06-11 01:41 ` Re: Does trigger only accept functions? Laurenz Albe <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Adrian Klaver @ 2024-06-10 20:20 UTC (permalink / raw)
To: veem v <[email protected]>; pgsql-general <[email protected]>
On 6/10/24 12:17, veem v wrote:
> Hi, It's version 15.4 of postgres. We have a requirement to have the
> audit enabled for the delete queries on the base table. And for that we
> are planning to have one audit table created for each base table and
> have triggers on each of the base tables to be fired on delete which
> will insert records into the audit table.
>
> But I see the trigger is not accepting the insert query directly, rather
> it's asking to call a function and to put the business logic inside that
> function, something as below. So does that mean, to enable audit on the
> ~50 base table , we will have ~50 functions to be created and also they
> need to be called from ~50 triggers? or any other better approach exists
> to handle this?
The below tells you what you need:
https://www.postgresql.org/docs/15/sql-createtrigger.html
That is either a function or a procedure.
You could create one function with dynamic SQL and call that from each
trigger. Yes there would need to be trigger on each table in that case.
As to alternatives:
https://www.pgaudit.org/
>
> CREATE OR REPLACE FUNCTION log_deletes_source_table()
> RETURNS TRIGGER AS $$
> BEGIN
> INSERT INTO source_table_delete_history (record_id,
> delete_timestamp, col1, col2,col3)
> VALUES (OLD.id, current_timestamp, old.col1,old.col2,old.col3);
> RETURN OLD;
> END;
> $$ LANGUAGE plpgsql;
>
> -- Trigger for deletes
> CREATE TRIGGER before_delete
> BEFORE DELETE ON source_table
> FOR EACH ROW EXECUTE FUNCTION log_deletes_source_table ();
>
> Regards
> Veem
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Does trigger only accept functions?
2024-06-10 20:20 Re: Does trigger only accept functions? Adrian Klaver <[email protected]>
@ 2024-06-11 01:41 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2024-06-11 01:41 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; veem v <[email protected]>; pgsql-general <[email protected]>
On Mon, 2024-06-10 at 13:20 -0700, Adrian Klaver wrote:
> https://www.postgresql.org/docs/15/sql-createtrigger.html
>
> That is either a function or a procedure.
The trigger function must be a function, it cannot be a procedure.
The syntax EXECUTE PROCEDURE is just for backward compatibility with
the time before PostgreSQL had procedures.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-06-11 01:41 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-10 20:20 Re: Does trigger only accept functions? Adrian Klaver <[email protected]>
2024-06-11 01:41 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox