Re: Docs - Plpgsql trigger example auditing changes into - Mailing list pgsql-patches

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>