<!--

$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.49 2004/11/15 06:32:14 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.50 2004/12/03 17:12:09 momjian Exp $

-->

<chapter id="plpgsql">

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>