Re: Docs - Plpgsql trigger example auditing changes into
От | Mark Kirkwood |
---|---|
Тема | Re: Docs - Plpgsql trigger example auditing changes into |
Дата | |
Msg-id | 41AF85C8.7050103@coretech.co.nz обсуждение исходный текст |
Ответ на | Re: Docs - Plpgsql trigger example auditing changes into another table (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Docs - Plpgsql trigger example auditing changes into
(Bruce Momjian <pgman@candle.pha.pa.us>)
Re: Docs - Plpgsql trigger example auditing changes into (David Fetter <david@fetter.org>) |
Список | pgsql-patches |
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>
В списке pgsql-patches по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Docs - Plpgsql trigger example auditing changes into another table
Следующее
От: Bruce MomjianДата:
Сообщение: Re: contrib/xml2: add function xml_encode_special_chars