Re: Docs - Plpgsql trigger example auditing changes into - Mailing list pgsql-patches

Patch applied.  Thanks.

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



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