2

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!

2
  • 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. Commented Nov 5, 2012 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
    Commented Nov 5, 2012 at 21:39

1 Answer 1

1

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
);
2
  • 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
    Commented Nov 5, 2012 at 23:05
  • Yeah, we generated the triggers from a stored procedure. Commented Nov 5, 2012 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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