Thread: A generic trigger to log chanes on database tables

Hi!

Is there a way that I could create a trigger that logs the changes (updates, deletes) of a table? I mean, I want to put in a table the changes to any table in a database and also put in the table the column that was modified and the corresponding value.

My audit table looks like this:

Table name: TableChangesLog
Columns: ChangeID int not null auto_increment --> Primary Key of table
               TableName -> Name of table modified
               ColumnName -> Column modified
               ColumnValue -> The original value
               Operation -> "Update" or "Delete"

My idea is to create a generic trigger that will log all changes for all database tables.

Is this possible?

Thanks in advance ...

am  Fri, dem 24.11.2006, um  0:32:30 +0800 mailte Charles Ambrose folgendes:
> Hi!
>
> Is there a way that I could create a trigger that logs the changes (updates,
> deletes) of a table? I mean, I want to put in a table the changes to any table
> in a database and also put in the table the column that was modified and the
> corresponding value.

Not a generic way, you must create a audit-table for every desired
table, but it works:

http://pgfoundry.org/projects/tablelog/


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Nov 23, 2006, at 10:32 AM, Charles Ambrose wrote:
> Is there a way that I could create a trigger that logs the changes
> (updates, deletes) of a table? I mean, I want to put in a table the
> changes to any table in a database and also put in the table the
> column that was modified and the corresponding value.

If you want a table-generic method, look at how Slony does it. You
probably won't want to use exactly that code, but it should get you
close.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

----- Original Message -----
From: "Charles Ambrose" <jamjam360@gmail.com>

> Is there a way that I could create a trigger that logs the changes
> (updates, deletes) of a table? I mean, I want to put in a table the
> changes to any table in a database and also put in the table the  column
> that was modified and the corresponding value.

Charles,

You may want to check out the following (may also give you addtional ideas
for what you're trying to achieve):

http://www.varlena.com/GeneralBits/104.php
(Logging Audit Changes with Composite Typed Columns).

Regards,
George