Patch applied. Thanks.
---
Mark Kirkwood wrote:
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 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
+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
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
