im wondering if its possible to copy an old value to another table for audit purposes when its changed.
e.g.
under tbl_reqs there is a field called fldstatus
when this field is changed i want it to copy what the field was before and insert it into my tbl_oldstatus table as well as the date it was entered and the corresponsing record number.
e.g.
before:-
tbl_reqs
reqnumber - status
123456 - ordered
after:-
reqnumber - status
123456 - completed
tbloldstatus
reqnumber - old status - change date
123456 - ordered - 03/01/2007
etc.
Once again i havent got a clue how to do this as im still a newbie!
You could use a trigger for this - whenever a record is changed the Trigger could copy the Old record (or the New record if you prefer) to an "Audit table"
Thanks Kristen ive had a look at the code you have created on that FAQ site but i dont understand what it does ive already got the old status table made, what syntax etc would i need in the trigger?
This will insert into your Audit Table the original values from all the columns in the source table - plus two additional leading columns for the Type of the action (Update or Delete) and the current Date/Time. You can change the SELECT list to only insert the columns you need.
If that isn't clear then I reckon you need to read up on Triggers and possibly some more basic syntax stuff on INSERT.
copying values from one table to another
Posted - 01/03/2007 : 07:27:07
