How can I automatically generate sql update scripts when some data is updated ?

Question

I'd like to automatically generate an update script each time a value is modified in my database. In other words, if a stored procedure, or a query, or whatever updates column a with value b in table c (which as a pk column (i,j...k), I want to generate this :

update c set a=b where i=... and j=... and k=...

and store it somewhere (for example as a raw string in a table). To complicate things, I want the script to be generated only if the update has been made by a specific user. Good news is that I've got a primary key defined for all my tables.

I can see how to do this using a trigger, but I would need to generate specific triggers for each table, and to update them each and every-time my schema changes.

I guess there are some built-in ways to do this as SQL server sometimes need to store this kind of things (while using transactional replication for example), but couldn't find anything so far ... any ideas ?

I'm also interested in ways to automatically generate triggers (probably using triggers - meta triggers, huh? - since I will need to update triggers automatically when the schema change)

---

Here's why I want to do this. I've got a production database A and a datawarehouse database B. I'm using transactional replication to keep the databases in sync. I need to be able to update some values in the datawarehouse without updating the production database. This works fine with transactional replication, but raises two issues, which lead to the above question :

• How do I keep track of the manual changes I've made ? (I plan on source-controlling the generated script.)
• How do I restore this database if needed, as doing a new snapshot would loose all my updates ? (applying the auto-generated script solves the problem)

Answer 1

Rather than thinking in terms of a script to update the data, why not store the data which has changed?

History tables populated by a trigger are good for this as each change writes a new row to the history table recording the date/time of the change as well as the user performing the change.

The history table will be the same as the original table with the addition of the following columns (use your own naming convention, of course):

update_date datetime DEFAULT CURRENT_TIMESTAMP
update_by_user varchar(100) DEFAULT SYSTEM_USER

Then you create a trigger on your table for UPDATE on your base table:

CREATE TRIGGER MyTable_Record_Changes
   ON MyTable
   AFTER UPDATE
AS 
BEGIN
    INSERT [MyTable_History] ([col_1], [col_2], ...)
    SELECT [col_1], [col_2], ...
    FROM deleted
END

Now you have a full history of the changes made to your data, when and by whom. You can then take those changes and compare/update the other database as necessary.