Docs - Plpgsql trigger example auditing changes into another table

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.

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