MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Tue, 11 Jun 2024 00:47:14 +0530
Message-ID: <CAB+=1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA@mail.gmail.com>
Subject: Does trigger only accept functions?
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e2d827061a8dff18"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA%40mail.gmail.com>
Precedence: bulk

--000000000000e2d827061a8dff18
Content-Type: text/plain; charset="UTF-8"

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?

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

--000000000000e2d827061a8dff18
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi, It&#39;s version 15.4 of postgres. We have a requ=
irement 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 w=
hich will insert records into the audit table.<br></div><br>But I see the t=
rigger is not accepting the insert query directly, rather it&#39;s asking t=
o call a function and to put the business logic inside that function, somet=
hing as below. So does that mean, to enable audit on the ~50 base table , =
=C2=A0we will have ~50 functions to be created and also they need to be cal=
led from ~50 triggers? or any other better approach exists to handle this? =
<br><br><font face=3D"monospace">CREATE OR REPLACE FUNCTION log_deletes_sou=
rce_table()<br>RETURNS TRIGGER AS $$<br>BEGIN<br>=C2=A0 =C2=A0 INSERT INTO =
source_table_delete_history (record_id, delete_timestamp, col1, col2,col3)<=
br>=C2=A0 =C2=A0 VALUES (OLD.id, current_timestamp, old.col1,old.col2,old.c=
ol3);<br>=C2=A0 =C2=A0 RETURN OLD;<br>END;<br>$$ LANGUAGE plpgsql;<br><br>-=
- Trigger for deletes<br>CREATE TRIGGER before_delete<br>BEFORE DELETE ON s=
ource_table<br>FOR EACH ROW EXECUTE FUNCTION=C2=A0</font>

<span style=3D"font-family:monospace">log_deletes_source_table</span>=C2=A0=
=C2=A0<font face=3D"monospace">();</font><br><div><font face=3D"monospace">=
<br></font></div><div><font face=3D"monospace">Regards</font></div><div><fo=
nt face=3D"monospace">Veem</font></div></div>

--000000000000e2d827061a8dff18--