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