Re: [PATCHES] Docs - Plpgsql trigger example auditing changes into
David Fetter wrote: It occurs to me that this might be a place to demo table inheritance too, to ease automating the creation, etc. of the auditing infrastructure :) It certainly makes the creation of the audit table painless : CREATE TABLE emp_audit( operation char(1) NOT NULL, stamp timestamp NOT NULL, useridtext NOT NULL ) INHERITS (emp); However SELECT FROM emp will return rows from emp_audit as well! (unless we always use FROM ONLY or set SQL_INHERITANCE=false). This seem likely to confuse things! regards Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Docs - Plpgsql trigger example auditing changes into
Patch applied. Thanks. --- Mark Kirkwood wrote: Tom Lane wrote: Mark Kirkwood [EMAIL PROTECTED] 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 applicationPL/pgSQL/application. + /para + + example id=plpgsql-trigger-audit-example +titleA applicationPL/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, +salaryinteger +); + +CREATE TABLE emp_audit( +operation char(1) NOT NULL, +stamp timestamp NOT NULL, +useridtext 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Docs - Plpgsql trigger example auditing changes into
On Fri, Dec 03, 2004 at 10:14:48AM +1300, Mark Kirkwood wrote: Tom Lane wrote: Mark Kirkwood [EMAIL PROTECTED] 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 :-( It occurs to me that this might be a place to demo table inheritance too, to ease automating the creation, etc. of the auditing infrastructure :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Docs - Plpgsql trigger example auditing changes into another table
Mark Kirkwood [EMAIL PROTECTED] 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. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Docs - Plpgsql trigger example auditing changes into
Tom Lane wrote: Mark Kirkwood [EMAIL PROTECTED] 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.sgml2004-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 applicationPL/pgSQL/application. + /para + + example id=plpgsql-trigger-audit-example +titleA applicationPL/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, +salaryinteger +); + +CREATE TABLE emp_audit( +operation char(1) NOT NULL, +stamp timestamp NOT NULL, +useridtext 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