Tom Lane wrote:
Mark Kirkwood <[EMAIL PROTECTED]> writes:Thanks Tom - I was busy checking the spelling, but didn't check if it was functionally correct :-(
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.
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