Baron, Thanks a lot Adding the columns to the end works...
Olaf On 5/30/07 2:13 PM, "Baron Schwartz" <[EMAIL PROTECTED]> wrote: > 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 ------------------------- Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
