Re: [PATCHES] Docs - Plpgsql trigger example auditing changes into

[Mark Kirkwood]

Tom Lane wrote:
Mark Kirkwood <[EMAIL PROTECTED]> 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