December 23, 2011 at 8:22 am
Hello,
I was recently asked to write a report for some of our users to track scheduling status changes in the application they use. The application is from a vendor, and no schema or documentation except for some stored procedures used for interfacing are available. Well I tracked down the table where the data is, but I found that the application will just create a record, update it over time, and then delete it when its no longer needed. The first thought I had was that I could create a trigger on the table to capture the inserts and updates, but I would also be nervous about creating any conditions where a transaction would be inadvertently rolled back and the application disrupted by unexpected behavior. My next thought was that perhaps it was now a good time to propose setting up a replicated copy for reporting purposes, but I'm not sure of a couple things:
* Assuming we would use transactional replication, is it possible to setup a trigger on the subscriber side that will
fire when the records are replicated? I was envisioning the trigger catching the insert/update and then inserting
the data into a separate table
* If a trigger on the subscriber side works, is there anything to keep an eye out for?
* Is there a better way?
I should mention that this would probably be implemented with SQL Server 2008 R2 (we're upgrading in a couple weeks) if that would make a difference.
Thanks for any thoughts or advice in advance!
December 23, 2011 at 10:09 am
Not going to write the code, but my high level solution design for this :-
Whatever the table/s you Want to audit, create one audit table for primary table.
For ex., if your table is emp, create another table emp_Audit then write the trigger in such a way that it capture the modification and put it in emp_audit table.
----------
Ashish
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply