Re: Docs - Plpgsql trigger example auditing changes into

Patch applied. Thanks.

---------------------------------------------------------------------------

Mark Kirkwood wrote:
> Tom Lane wrote:
>
> >Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> 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
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073