Capturing Previous Value

Capturing Previous Value

• Bernd Wechner

  Hi Adamus,

  Adding the extra field to the table is one of the possibilities we are pursuing to solve our problem.

  Thanks,

  Lynn

  
  

• oopman

  Adamus,

  I understand good db design and, under normal circumstances we would do that. We are going to add the "oldMyField" field to the table and update that with the previous value. What we have is 2 separate manufacturing systems that must talk to each other. Thus, if a user of one system updates a line in that system, then the updates must be communicated to the other system. Since the 2 systems' tables don't share the same primary key values, we have to use a string value to find the corresponding record in the other system and, for that, we need to know what the string value was befor it was changed and send that to the other system for update.

  Thanks again for the help.

  Lynn Trapp
  MS Access MVP

  
  

• chris29

  I didn't doubt your awareness of db design Lynn :)

  I was just attempting to prevent any off-the-wall or abstract approaches from entering the thread.

  I understand your situation and a single field will work, but I am still confused on why you can't update simultaneously

  Is one system remote (unable to establish a join )
  Is one a testing server and another a production server
  Do you want to validate the entry before updating the second system

  Just curious,

  Adamus

  
  
  

• ichi

  Hi Lynn,

  In good db design, there should be a new record inserted, not continual updates to existing records and you track the history through timestamps.

  However, if you want to just capture the previous existing value, it would be more practical and efficient to add a field called oldMyField and record the old value in your existing table instead of creating an entirely new table to record just this value.

  Adamus

   

  
  
  

• Roman S.

  Hi Lynn,

   

  Sure, you could do that via either a trigger or in SQL Server 2005, the OUTPUT clause.

   

  Here's an example with OUTPUT.  Doing the same thing in a trigger would be similar (you'd use the virtual 'deleted' table to get the pre-update value).

   

  ---

  USE TempDB
  GO

   

  CREATE TABLE ABC(x INT)
  GO

   

  INSERT ABC (x) VALUES (1)
  GO

   

  --Output clause returns the original value
  UPDATE ABC
  SET x = 2
  OUTPUT deleted.x
  GO

   

  DROP TABLE ABC
  GO

  ---

   

  
  --
  Adam Machanic
  Pro SQL Server 2005, available now
  http://www..apress.com/book/bookDisplay.html bID=457
  --

   

   

  ""Lynn Trapp"@discussions.microsoft.com" <"= UTF-8 B THlubiBUcmFwcA== ="@discussions.microsoft.com> wrote in message news:626f32c7-6059-4d1d-bddc-24e2a778d367@discussions.microsoft.com...

  When updating the value of a field in a table, is it possible to capture -- either for insertion in another table or as a variable -- the previous value of the field Thus, if a field value is 'Value1' and a user updates it to 'Value2' is there some way to hold 'Value1' as a variable

  Lynn Trapp

  
  

• Adam.Kahtava

  No, we haven't considered that, but I'll suggest it to the team.

  Thanks,

  Lynn

  
  

• Dan Cleveland

  Adamus Turner wrote:

  I understand your situation and a single field will work, but I am still confused on why you can't update simultaneously Is one system remote (unable to establish a join ) Is one a testing server and another a production server Do you want to validate the entry before updating the second system Just curious, Adamus

  Adamus,

  Mainly, we can't do the update simultaneously because one of the systems is proprietary and we are not allowed to do direct table updates. We have to use a stored procedure to call stored procedures in the proprietary system.

  Lynn Trapp
  MS Access MVP

  
  

• Pankaj11

  Have you considered using replication to do this It will do exactly what you're talking about and you don't have to do it all yourself.
  

• WRBehning

  Hi Adam,

  Thanks for that example. It looks rather promising. I have the odd situation of needing to keeping 2 tables in separate systems in synch. Thus, if a user changes a value in one of the tables (and they only have access to a couple of fields), then I need to be able to programatically identify and update the corresponding record in the other table (thus the need for the original value in the first table).

  Thanks again,

  Lynn

  
  

• B-Dubs

  Adamus Turner wrote:

  Hi Lynn, In good db design, there should be a new record inserted, not continual updates to existing records and you track the history through timestamps. However, if you want to just capture the previous existing value, it would be more practical and efficient to add a field called oldMyField and record the old value in your existing table instead of creating an entirely new table to record just this value. Adamus

  
  
  And what you are referring to would be called "slowly changing dimensions."
  
  
  
• Capturing Previous Value