When the table's rows are changed, these changed rows are written to XML, and let me know that the table has been changed.
How can I do this?
+1
A:
If you're looking for a strict TSQL or SQL Server solution:
- write a stored procedure to handle
UPDATE,DELETEandINSERTfunctionality. - deny
UPDATE,DELETEandINSERTto users - allow EXEC to users on this new stored proc
- on each call to the stored proc, make an entry into another table, specifically built for auditing.
- write a SQL Job to poll this audit table for new records. Use SQL Mail to send email. You weren't clear about what kind of notification you wanted, but I assumed email.
2nd less attractive solution: You could also use triggers on the table to capture the UPDATE, DELETE and INSERT activity. Strongly consider the stored proc solution over triggers.
p.campbell
2010-09-08 02:37:21
+1: Triggers are more flexible if this is for maintenance, rather than new development.
OMG Ponies
2010-09-08 02:39:05
@OMG: I always go back and forth on the triggers. Even get downvotes for even suggesting them as a second option on SO. This scenario screamed 'too many things to do' for the trigger. Auditing is funny, but it could go either way, and also depending on the environment. 50-50.
p.campbell
2010-09-08 02:41:41
OMG Ponies
2010-09-08 02:49:11
So you're saying you don't like the trigger approach because it's often used to add rules to the database layer in ORM scenarios, whereas stored procedures are indicative of a thicker db layer less likely to be treated as a dumb data store by builders of ORM-based apps?
Ken Redler
2010-09-08 03:06:41
A:
If you can't alter how data is being changed in your table, the best solution would be to setup a trigger to capture changes in a separate table, and then write some code to periodically poll this table, and build your xml file.
Its worth noting that this will potentially slow down your db performance when editing data in this table (good for audit when users are making changes, bad for programatically changed data), and any errors coming from the trigger lead to quite misleading messages thrown back out of sql server.
See this question for some pointers on setting up the trigger.
Jon Freedman
2010-09-08 08:34:10