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
adrian.klaver@xxxxxxxxxxx
