MIME-Version: 1.0
References: <CAB+=1TWT1_kKdBtuWvLBKJ1hkjxsaRZfMQADQU7bsVtat9q-LA@mail.gmail.com>
 <Zmg2GIso2Lb4r_2m@depesz.com>
In-Reply-To: <Zmg2GIso2Lb4r_2m@depesz.com>
From: veem v <veema0000@gmail.com>
Date: Wed, 12 Jun 2024 00:19:55 +0530
Message-ID: <CAB+=1TUWFJ_9BsqVZkj+3V18WxpAZojzhbOLN1kGzUYqx=TLfQ@mail.gmail.com>
Subject: Re: Does trigger only accept functions?
To: depesz@depesz.com
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d0e99f061aa1c5fa"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TUWFJ_9BsqVZkj%2B3V18WxpAZojzhbOLN1kGzUYqx%3DTLfQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, 11 Jun 2024 at 17:03, hubert depesz lubaczewski <depesz@depesz.com>
wrote:

> On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote:
> > to be called from ~50 triggers? or any other better approach exists to
> > handle this?
>
> pgaudit extension?
>
> Or just write all the changes to single table?
>
> Or use dynamic queries that will build the insert based on the name of
> table the event happened on?
>
> Or pass arguments?
>
> Best regards,
>
> depesz
>
>
Thank you so much. I hope you mean something as below when you say making
it dynamic. Because we have the audit tables having more number of columns
as compared to the source table and for a few the column name is a bit
different.

-- Trigger for deletes
CREATE TRIGGER before_delete
BEFORE DELETE ON source_table
FOR EACH ROW EXECUTE FUNCTION log_deletes();


-- Trigger for source_table1
CREATE TRIGGER before_delete_source_table1
BEFORE DELETE ON source_table1
FOR EACH ROW EXECUTE FUNCTION log_deletes();

-- Trigger for source_table2
CREATE TRIGGER before_delete_source_table2
BEFORE DELETE ON source_table2
FOR EACH ROW EXECUTE FUNCTION log_deletes();


CREATE OR REPLACE FUNCTION log_deletes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_TABLE_NAME = 'source_table1' THEN
        INSERT INTO delete_audit1 ( col1, col2, col3)
        VALUES (OLD.col1, OLD.col2, OLD.col3);
    ELSIF TG_TABLE_NAME = 'source_table2' THEN
        INSERT INTO delete_audit2 ( col4, col5, col6)
        VALUES (OLD.col4, OLD.col5, OLD.col6);
    -- Add more conditions for other tables
    ELSE
        RAISE EXCEPTION 'Audit table not defined for %', TG_TABLE_NAME;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div=
 dir=3D"ltr" class=3D"gmail_attr">On Tue, 11 Jun 2024 at 17:03, hubert depe=
sz lubaczewski &lt;<a href=3D"mailto:depesz@depesz.com">depesz@depesz.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On=
 Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote:<br>
&gt; to be called from ~50 triggers? or any other better approach exists to=
<br>
&gt; handle this?<br>
<br>
pgaudit extension?<br>
<br>
Or just write all the changes to single table?<br>
<br>
Or use dynamic queries that will build the insert based on the name of<br>
table the event happened on?<br>
<br>
Or pass arguments?<br>
<br>
Best regards,<br>
<br>
depesz<br>
<br></blockquote><div><br></div><div>Thank you so much. I hope you mean som=
ething=C2=A0as below when you say making it dynamic. Because we have the au=
dit tables having more number of columns as compared to the source table an=
d for a few the column name is a bit different.</div><div><br></div><font f=
ace=3D"monospace">-- Trigger for deletes<br>CREATE TRIGGER before_delete<br=
>BEFORE DELETE ON source_table<br>FOR EACH ROW EXECUTE FUNCTION log_deletes=
();<br><br><br>-- Trigger for source_table1<br>CREATE TRIGGER before_delete=
_source_table1<br>BEFORE DELETE ON source_table1<br>FOR EACH ROW EXECUTE FU=
NCTION log_deletes();<br><br>-- Trigger for source_table2<br>CREATE TRIGGER=
 before_delete_source_table2<br>BEFORE DELETE ON source_table2<br>FOR EACH =
ROW EXECUTE FUNCTION log_deletes();<br><br><br>CREATE OR REPLACE FUNCTION l=
og_deletes()<br>RETURNS TRIGGER AS $$<br>BEGIN<br>=C2=A0 =C2=A0 IF TG_TABLE=
_NAME =3D &#39;source_table1&#39; THEN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSER=
T INTO delete_audit1 ( col1, col2, col3)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 VAL=
UES (OLD.col1, OLD.col2, OLD.col3);<br>=C2=A0 =C2=A0 ELSIF TG_TABLE_NAME =
=3D &#39;source_table2&#39; THEN<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT INTO=
 delete_audit2 ( col4, col5, col6)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 VALUES (O=
LD.col4, OLD.col5, OLD.col6);<br>=C2=A0 =C2=A0 -- Add more conditions for o=
ther tables<br>=C2=A0 =C2=A0 ELSE<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 RAISE EXCE=
PTION &#39;Audit table not defined for %&#39;, TG_TABLE_NAME;<br>=C2=A0 =C2=
=A0 END IF;<br>=C2=A0 =C2=A0 RETURN OLD;<br>END;<br>$$ LANGUAGE plpgsql;<br=
></font><div>=C2=A0</div></div></div>

--000000000000d0e99f061aa1c5fa--