Hi,
Olaf Stein wrote:
Hey all,
I asked this before (a while back) and have gotten some answers that solve
my problem partially only.
------- old message ----------
I have a table with 205 columns. When an update statement updates a row in
this table I want a trigger that creates a record of the old row in a
separate table.
The following works fine when not too many columns need to be written into
the other table
CREATE TRIGGER track_table
BEFORE UPDATE ON table FOR EACH ROW
BEGIN
INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2);
END;
Is the any way of using OLD for all columns or generally another way of
doing this.
------- old message ----------
One answer (thanks btw) suggested to do this:
INSERT INTO table_track SELECT * FROM table WHERE key_col=OLD.key_col;
This works fine, requires however that both tables have the same structure
(nr of columns, order, etc). I would like to add an autoincrement ID and a
timestamp to the record written by the trigger and do not want to have a
timestamp field in the original table.
Is there any other way of doing this?
The only other idea I have (and I am not sure if this would even work) is to
select the column headers from information_schema and loop through them
building the actual insert statement.
If your columns are the same other than the ID and timestamp, and the two extra
columns come last in the destination table, you could SELECT *, NULL, NOW() FROM....
Otherwise I think you will have to list the columns explicitly.
You could write a script that will generate the triggers for you fairly easily.
Maybe that will help ease the pain of maintaining things if your schema changes.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
