It sounds like you are looking to do an INSERT SELECT. http://dev.mysql.com/doc/mysql/en/insert-select.html
It allows you to insert into one table based on a select from another table. That will take care of creating your history. You then need to do an update on the main table. It's still a two step process that you have to do in your code, but it should accomplish what you are trying to do.
On Apr 14, 2005, at 1:24 PM, Darrell A. Sullivan, II wrote:
Hello,
I am currently looking at moving some of my FoxPro tables to a MySQL implementation and I am hoping someone can help me with how to do the following.
In my current code, whenever anyone makes a change to a row the original
value of the row is written to a history table with the same name with an
"x" appended to it. In addition the "x" table has a column to name the
person who made the change and a timestamp of when it was made. I do this so
I can go back if an account appears to have paid out too much so I can see
what the value of the particular row was when a transaction was processed.
Since I am using Visual Basic and DAO/ADO right now this basically required
me to create a replacement to the .Update method that first writes the data
to the "x" table and then updates the live table.
The drawback is that I can't use a sql "Update" call because I don't have
any control over the results.
When I saw that MySQL was going to support triggers I thought this would
solve my problem since I could put in a before update and a before delete
trigger and save the old data to another table. However, after reading more
closely I see that you can't do any table access from within the stored
procedure so that will not work.
Is there any way to solve something like this in MySQL? It just seems like
something that has to have been encountered by other people.
Thanks, Darrell
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
