notification that table rows have been changed

When the table's rows are changed, these changed rows are written to XML,开发者_Python百科 and let me know that the table has been changed.

How can I do this?

If you're looking for a strict TSQL or SQL Server solution:

• write a stored procedure to handle UPDATE, DELETE and INSERT functionality.
• deny UPDATE, DELETE and INSERT to 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.

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.