>>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).

Mark Kirkwood

1 files changed, 65 insertions, 1 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 7221606d6ab..15499720f3d 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.49 2004/11/15 06:32:14 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.50 2004/12/03 17:12:09 momjian Exp $
 -->
 
 <chapter id="plpgsql"> 
@@ -2556,6 +2556,70 @@ $emp_stamp$ LANGUAGE plpgsql;
 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>