Retain previous data and update with new data

The tags suggest that this is in a trigger; therefore, one option is to use the DELETED and INSERTED tables. In other words, DELETED.SafetyStock is the previous, removed value; INSERTED.SafetyStock, the new. Hence, an UPDATE with a JOIN should suffice.

UPDATE tto
SET tto.SafetyStock = i.SafetyStock
  , tto.PrevSafetyStock = d.PrevSafetyStock
FROM database2.Warehouse tto
JOIN DELETED d ON d.PK = tto.PK /* e.g., Item ID */
JOIN INSERTED i ON i.PK = tto.PK /* should be same as above. */
;

Select all Open in new window

In my experience, the SET operations happen sequentially, but I showed example above as I do not believe you want to depend on the order of assignments. However, in theory, the following also should work.

UPDATE tto
SET tto.PrevSafetyStock = tto.SafetyStock
  , tto.SafetyStock = i.SafetyStock
FROM database2.Warehouse tto
JOIN INSERTED i ON i.PK = tto.PK /* e.g., Item ID */
;

Select all Open in new window

Or have another trigger on the database2.Warehouse table that maintains the PrevSafetyStock, but it would be prudent to reduce the amount of triggers (i.e., complexity) in the process.

I hope that helps!

Should this be a 'Instead of Insert' or 'After Update' ??

Not sure about the scripting

Thank you mwvisa1