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>
