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>
 
---------------------------(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

Reply via email to