I have two tables, a product table and a product history table. I want the product history table to be updated with a new when a record when the product table is updated.
Sounds like I need a trigger. However I'm not sure how I would go about grabbing the last field updated. Note, there are no timestamps in the product table. The Product history table contains this with a relationship on product id. So for example:
1) I change the ProductName field from "TestA" to "TestB" (in Product Table)
2) Trigger fires (Product Table)
3) I want the field ProductName to be captured along with the initial value (TestA) and the new value (TestB) (I'll probably use 3 variables)
4) My insert into will insert a new record in the update history table with the following info: (modifieddate, fieldname (this is field that was updated), initial value, new value, user updated, product id)
The T-SQL for the inserts and update i got down, but what i'm trying to figure out if there is a way I can grab the record that just got updated along with the initial value, new value, and field name. Please note, that I don't want to add a modified date in the product table field to avoid the redundancy (its in the product history table) This is the method I'll end up using if I don't figure out how to do it the way I want to though.
Hope I'm making sense.