I received this code and have attached the README. Can I get a review
of its usefulness from someone?
---------------------------------------------------------------------------
Andreas Scherbaum wrote:
>
> Hello,
>
> i have written a module for logging changes on a table (without knowing
> the exact column names).
> Dont know where to put it, but its ready for use in the contrib directory.
>
> Its available at: http://ads.ufp.de/projects/Pg/table_log.tar.gz (3k)
>
> Would be nice, if this can be added.
>
>
> Best regards
>
> --
> Andreas 'ads' Scherbaum
> Failure is not an option. It comes bundled with your Microsoft product.
> (Ferenc Mantfeld)
>
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
table_log () -- log changes to another table
some code taken from noup.c
written by Andreas ' ads' Scherbaum (ads@ufp.de)
create trigger with log table name as argument
if no table name is given, the actual table name
plus '_log' will be used
example:
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test_table FOR EACH ROW
EXECUTE PROCEDURE table_log();
^^^^^ 'test_table_log' will be used to log changes
CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test_table FOR EACH ROW
EXECUTE PROCEDURE table_log('log_table');
^^^^^ 'log_table' will be used to log changes
the log table needs exact the same columns as the
table where the trigger will be used
(but without any constraints)
plus three extra columns:
trigger_mode VARCHAR(10)
trigger_tuple VARCHAR(5)
trigger_changed TIMESTAMP
trigger_mode contains 'INSERT', 'UPDATE' or 'DELETE'
trigger_tuple contains 'old' or 'new'
on INSERT, a log with the 'new' tuple will be written,
on UPDATE a log with the old tuple and a log with
the new tuple will be written and on DELETE a log
with the old tuple will be written
a good method to create the log table from the existing table:
-- create the table without data
SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMP;
see table_log.sql for a demo
If you have any changes or improvements, please contact me.
