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
[PATCHES] Docs - Plpgsql trigger example auditing changes into another table
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). I think this is a beneficial addition as our developers here were confused about how to write more complex triggers that used the special variables - so what better place to find examples than in the documentation! best wishes Mark --- plpgsql.sgml.orig Thu Dec 2 19:07:05 2004 +++ plpgsql.sgmlThu Dec 2 21:49:55 2004 @@ -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 +BEFORE 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
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
