Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I am pretty inexperienced with this.

I need a generic trigger, able to create and save in a fixed table some sort of identification data for a changed row from generic (any) table. The identification data should be used later to SELECT the changed item in the given table.

Can be this done without previously knowing the table structure?

The only idea I had, but it's way too inefficient in my opinion, also requires previous knowledge of the table column names, is to save a hash by:

MD5(concat(NEW.field1, NEW.field2, NEW.field3, ...))

then 

SELECT * FROM chaged_table WHERE hash = MD5(concat(field1, field2, field3, ...))

to identify the changed row in the table which triggered the change.

I would greatly appreciate any help or suggestions!

share|improve this question
I feel that you have two options: either have to know the tables beforehand or have an additional column in each table, which stores the previous version of the row (FK on the PK). Then you don't have UPDATEs at all, but INSERTs and flags for outdated rows (that is, it means two additional columns). For the first option I wrote a trigger generating mechanism in PostgreSQL (that way I can do the thing programmatically) and a time-machine procedure which can tell me the changes between two dates. – dezso Nov 5 '12 at 21:34
Interesting idea, the problem is I can't change this tables. The system is meant to be plugged as an incremental backup helper for some third party php applications by a generic pull based php backup engine. – sanyi Nov 5 '12 at 21:39

1 Answer

up vote 0 down vote

I've used this setup before, controlled by triggers. It works for any table with a primary key, and can be queried easily.

CREATE TABLE `auditLog` (
  `db_user` varchar(255) default NULL,
  `table_name` varchar(255) default NULL,
  `row_id` int(11) default NULL,
  `field_name` varchar(255) default NULL,
  `old_value` blob,
  `new_value` blob,
  `ts` timestamp NOT NULL default CURRENT_TIMESTAMP
);
share|improve this answer
Thank You for your answer. I was more interested in how to build the trigger itself without need of knowing the primary key and not the way to store the change. On the other hand this seems more and more unlikely to be possible. So most probably I will have to search for an easy way to automate trigger generation from the table schematic. – sanyi Nov 5 '12 at 23:05
Yeah, we generated the triggers from a stored procedure. – Alain Collins Nov 5 '12 at 23:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.