Solved

SQL Trigger or Function that updates table with old values

Posted on 2016-10-06
5
37 Views
Last Modified: 2016-10-07
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.
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;

Open in new window

0
Comment
Question by:John Mahoney
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 41832718
Insert trigger would capture the initial values when it's created.  
Update triggers has special tables called inserted and deleted.  Deleted are the old values and inserted are the new values.

This is a good tutorial of what you're looking for:
https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/
0
 
LVL 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41832857
This is what you need i think..

--


CREATE TRIGGER trg_MOTS_CLIENT_UPDATE
ON CD.CLIENT471
AFTER INSERT,UPDATE,DELETE
AS
 BEGIN
	
	IF EXISTS (SELECT * FROM DELETED) -- check update happens or not
	BEGIN 
		
		----Logic you wanted to implement for update

		--Here you will updated row in the inserted table
		--and the row which going to update in Deleted table
			
	END
	ELSE
	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 INSERTED

	END

END;


--

Open in new window


Enjoy !!
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 41834017
CREATE TRIGGER trg_MOTS_CLIENT_UPDATE
ON cd.client471
AFTER UPDATE
AS
SET NOCOUNT ON;
INSERT INTO cd.MOTS1History (
    [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;
0
 

Author Comment

by:John Mahoney
ID: 41834224
Hello, and thank you.
0
 

Author Closing Comment

by:John Mahoney
ID: 41834228
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.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now