public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: veem v <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Does trigger only accept functions?
Date: Mon, 10 Jun 2024 13:20:45 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB+=1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA@mail.gmail.com>
References: <CAB+=1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA@mail.gmail.com>

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]

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Does trigger only accept functions?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox