John Mahoney
asked on
SQL Trigger or Function that updates table with old values
I want to keep versions of data from a table. I have seen update triggers and insert triggers that work to sync data between a main table and a backup or other table but I would like to move the data that is present into the new table before that data is updated with new information. Can this be done with a trigger? The table is MOTS1 and it holds several fields of data about employment empUnemp, empFullTime, empPartTime, etc.,
I have another table called MOTS1History. So say a client changes from empUnemp (unemployed) to working part time empPartTime, I want to track that, so we can look back and see these changes that when he started he was unemployed and now is working part time. So the table would first have him set as empUnemp when created, then when he gets a job it's updated. When it's updated it just overwrites the old value or changes it.
The table was created for us based on state guidelines and I cannot make changes to the original table. Can anyone point me to a tutorial or way to do this?
I saw something and tried this but I get errors saying that the cd.clientHISTORY table object does not exist. The columns here are the columns that could be updated and that I want saved into the history table BEFORE the update.
I have another table called MOTS1History. So say a client changes from empUnemp (unemployed) to working part time empPartTime, I want to track that, so we can look back and see these changes that when he started he was unemployed and now is working part time. So the table would first have him set as empUnemp when created, then when he gets a job it's updated. When it's updated it just overwrites the old value or changes it.
The table was created for us based on state guidelines and I cannot make changes to the original table. Can anyone point me to a tutorial or way to do this?
I saw something and tried this but I get errors saying that the cd.clientHISTORY table object does not exist. The columns here are the columns that could be updated and that I want saved into the history table BEFORE the update.
CREATE TRIGGER trg_MOTS_CLIENT_UPDATE
on cd.client471
AFTER UPDATE
AS
BEGIN
INSERT INTO cd.client471HISTORY ([faccode_c],[countyresp_c],[txstatus_c],[compemploy01_c],[compemploy01_c]
,[compemploy02_c],[compemploy03_c],[compemploy04_c],
[compemploy05_c],[compemploy06_c],[compemploy07_c],[compemploy08_c],[compemploy09_c],[compemploy10_c], [compemploy11_c],[compemploy12_c],[livingarr_c],[livingarrchange_d],
[livingarrlast_c],[lastsubstat_d],[touch_date])
SELECT [faccode_c],[countyresp_c],[txstatus_c],[compemploy01_c],[compemploy01_c],[compemploy02_c],[compemploy03_c],[compemploy04_c],
[compemploy05_c],[compemploy06_c],[compemploy07_c],[compemploy08_c],[compemploy09_c],[compemploy10_c],[compemploy11_c],[compemploy12_c],[livingarr_c],[livingarrchange_d],
[livingarrlast_c],[lastsubstat_d],[touch_date]
from deleted;
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The tutorial finally was able to do the trick by allowing multiple entries for updates and inserts, but all help was needed and greatly appreciated.
ASKER