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.sgmlThu 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 applicationPL/pgSQL/application.
+ /para
+
+ example id=plpgsql-trigger-audit-example
+titleA applicationPL/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,
+salaryinteger
+);
+
+CREATE TABLE emp_audit(
+operation char(1) NOT NULL,
+stamp timestamp NOT NULL,
+useridtext 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
