Thread: Docs - Plpgsql trigger example auditing changes into another table
This patch adds another plpgsql trigger example to the chapter. It uses
the emp table again, but shows how to audit changes into another table
(emp_audit).
I think this is a beneficial addition as our developers here were
confused about how to write more complex triggers that used the special
variables - so what better place to find examples than in the documentation!
best wishes
Mark
--- plpgsql.sgml.orig Thu Dec 2 19:07:05 2004
+++ plpgsql.sgml Thu Dec 2 21:49:55 2004
@@ -2556,6 +2556,70 @@
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
</programlisting>
+
+
+ </example>
+
+ <para>
+ Another way to log changes to a table involves creating a new table that
+ holds a row for each insert, update, delete that occurs. This approach can
+ be thought of as auditing changes to a table.
+ </para>
+
+ <para>
+ <xref linkend="plpgsql-trigger-audit-example"> shows an example of an
+ audit trigger procedure in <application>PL/pgSQL</application>.
+ </para>
+
+ <example id="plpgsql-trigger-audit-example">
+ <title>A <application>PL/pgSQL</application> Trigger Procedure For Auditing</title>
+
+ <para>
+ This example trigger ensures that any insert, update or delete of a row
+ in the emp table is recorded (i.e. audited) in the emp_audit table.
+ The current time and user name are stamped into the row, together with
+ the type of operation performed on it.
+ </para>
+
+<programlisting>
+CREATE TABLE emp (
+ empname text NOT NULL,
+ salary integer
+);
+
+CREATE TABLE emp_audit(
+ operation char(1) NOT NULL,
+ stamp timestamp NOT NULL,
+ userid text NOT NULL,
+ empname text NOT NULL,
+ salary integer
+);
+
+CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
+ BEGIN
+ --
+ -- Create a row in emp_audit to reflect the operation performed on emp,
+ -- make use of the special variable TG_OP to work out the operation.
+ --
+ IF (TG_OP = 'DELETE') THEN
+ INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
+ RETURN OLD;
+ ELSIF (TG_OP = 'UPDATE') THEN
+ INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
+ RETURN NEW;
+ ELSIF (TG_OP = 'INSERT') THEN
+ INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
+ RETURN NEW;
+ END IF;
+ END;
+$emp_audit$ language plpgsql;
+
+
+CREATE TRIGGER emp_audit
+BEFORE INSERT OR UPDATE OR DELETE ON emp
+ FOR EACH ROW EXECUTE PROCEDURE process_emp_audit()
+;
+</programlisting>
</example>
</sect1>
Mark Kirkwood <markir@coretech.co.nz> writes:
> This patch adds another plpgsql trigger example to the chapter. It uses
> the emp table again, but shows how to audit changes into another table
> (emp_audit).
Should be an AFTER trigger, else you may be recording the wrong data, or
even an event that didn't happen at all.
regards, tom lane
Tom Lane wrote:
>Mark Kirkwood <markir@coretech.co.nz> writes:
>
>
>>This patch adds another plpgsql trigger example to the chapter. It uses
>>the emp table again, but shows how to audit changes into another table
>>(emp_audit).
>>
>>
>
>Should be an AFTER trigger, else you may be recording the wrong data, or
>even an event that didn't happen at all.
>
Thanks Tom - I was busy checking the spelling, but didn't check if it
was functionally correct :-(
New patch attached.
--- plpgsql.sgml.orig 2004-12-03 10:01:54.648595360 +1300
+++ plpgsql.sgml 2004-12-03 10:08:58.017297192 +1300
@@ -2556,6 +2556,70 @@
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
</programlisting>
+
+
+ </example>
+
+ <para>
+ Another way to log changes to a table involves creating a new table that
+ holds a row for each insert, update, delete that occurs. This approach can
+ be thought of as auditing changes to a table.
+ </para>
+
+ <para>
+ <xref linkend="plpgsql-trigger-audit-example"> shows an example of an
+ audit trigger procedure in <application>PL/pgSQL</application>.
+ </para>
+
+ <example id="plpgsql-trigger-audit-example">
+ <title>A <application>PL/pgSQL</application> Trigger Procedure For Auditing</title>
+
+ <para>
+ This example trigger ensures that any insert, update or delete of a row
+ in the emp table is recorded (i.e. audited) in the emp_audit table.
+ The current time and user name are stamped into the row, together with
+ the type of operation performed on it.
+ </para>
+
+<programlisting>
+CREATE TABLE emp (
+ empname text NOT NULL,
+ salary integer
+);
+
+CREATE TABLE emp_audit(
+ operation char(1) NOT NULL,
+ stamp timestamp NOT NULL,
+ userid text NOT NULL,
+ empname text NOT NULL,
+ salary integer
+);
+
+CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
+ BEGIN
+ --
+ -- Create a row in emp_audit to reflect the operation performed on emp,
+ -- make use of the special variable TG_OP to work out the operation.
+ --
+ IF (TG_OP = 'DELETE') THEN
+ INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
+ RETURN OLD;
+ ELSIF (TG_OP = 'UPDATE') THEN
+ INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
+ RETURN NEW;
+ ELSIF (TG_OP = 'INSERT') THEN
+ INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
+ RETURN NEW;
+ END IF;
+ END;
+$emp_audit$ language plpgsql;
+
+
+CREATE TRIGGER emp_audit
+AFTER INSERT OR UPDATE OR DELETE ON emp
+ FOR EACH ROW EXECUTE PROCEDURE process_emp_audit()
+;
+</programlisting>
</example>
</sect1>
Patch applied. Thanks. --------------------------------------------------------------------------- Mark Kirkwood wrote: > Tom Lane wrote: > > >Mark Kirkwood <markir@coretech.co.nz> writes: > > > > > >>This patch adds another plpgsql trigger example to the chapter. It uses > >>the emp table again, but shows how to audit changes into another table > >>(emp_audit). > >> > >> > > > >Should be an AFTER trigger, else you may be recording the wrong data, or > >even an event that didn't happen at all. > > > Thanks Tom - I was busy checking the spelling, but didn't check if it > was functionally correct :-( > > New patch attached. > --- plpgsql.sgml.orig 2004-12-03 10:01:54.648595360 +1300 > +++ plpgsql.sgml 2004-12-03 10:08:58.017297192 +1300 > @@ -2556,6 +2556,70 @@ > CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp > FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); > </programlisting> > + > + > + </example> > + > + <para> > + Another way to log changes to a table involves creating a new table that > + holds a row for each insert, update, delete that occurs. This approach can > + be thought of as auditing changes to a table. > + </para> > + > + <para> > + <xref linkend="plpgsql-trigger-audit-example"> shows an example of an > + audit trigger procedure in <application>PL/pgSQL</application>. > + </para> > + > + <example id="plpgsql-trigger-audit-example"> > + <title>A <application>PL/pgSQL</application> Trigger Procedure For Auditing</title> > + > + <para> > + This example trigger ensures that any insert, update or delete of a row > + in the emp table is recorded (i.e. audited) in the emp_audit table. > + The current time and user name are stamped into the row, together with > + the type of operation performed on it. > + </para> > + > +<programlisting> > +CREATE TABLE emp ( > + empname text NOT NULL, > + salary integer > +); > + > +CREATE TABLE emp_audit( > + operation char(1) NOT NULL, > + stamp timestamp NOT NULL, > + userid text NOT NULL, > + empname text NOT NULL, > + salary integer > +); > + > +CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ > + BEGIN > + -- > + -- Create a row in emp_audit to reflect the operation performed on emp, > + -- make use of the special variable TG_OP to work out the operation. > + -- > + IF (TG_OP = 'DELETE') THEN > + INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; > + RETURN OLD; > + ELSIF (TG_OP = 'UPDATE') THEN > + INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*; > + RETURN NEW; > + ELSIF (TG_OP = 'INSERT') THEN > + INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*; > + RETURN NEW; > + END IF; > + END; > +$emp_audit$ language plpgsql; > + > + > +CREATE TRIGGER emp_audit > +AFTER INSERT OR UPDATE OR DELETE ON emp > + FOR EACH ROW EXECUTE PROCEDURE process_emp_audit() > +; > +</programlisting> > </example> > </sect1> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, Dec 03, 2004 at 10:14:48AM +1300, Mark Kirkwood wrote: > Tom Lane wrote: > > >Mark Kirkwood <markir@coretech.co.nz> writes: > > > > > >>This patch adds another plpgsql trigger example to the chapter. It > >>uses the emp table again, but shows how to audit changes into > >>another table (emp_audit). > >> > >> > > > >Should be an AFTER trigger, else you may be recording the wrong > >data, or even an event that didn't happen at all. > > > Thanks Tom - I was busy checking the spelling, but didn't check if > it was functionally correct :-( It occurs to me that this might be a place to demo table inheritance too, to ease automating the creation, etc. of the auditing infrastructure :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
David Fetter wrote:
>It occurs to me that this might be a place to demo table inheritance
>too, to ease automating the creation, etc. of the auditing
>infrastructure :)
>
>
>
It certainly makes the creation of the audit table painless :
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL
) INHERITS (emp);
However SELECT FROM emp will return rows from emp_audit as well! (unless
we always use FROM ONLY or set SQL_INHERITANCE=false). This seem likely
to confuse things!
regards
Mark