How can I automatically generate sql update scripts when some data is updated ?
- by Brann
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)
