notification that table rows have been changed

Question

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?

The problem is that I couldn't change the stored proc . Maybe I can add a trigger instead . But how can I know these changes . Is there any good way to do this ? — Ariver, Sep 08 '10 at 06:01

score 1 · Answer 1 · answered Sep 08 '10 at 02:37

1

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.

answered Sep 08 '10 at 02:37

p.campbell

91,713
61
243
314

+1: Triggers are more flexible if this is for maintenance, rather than new development. – OMG Ponies Sep 08 '10 at 02:39
@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 Sep 08 '10 at 02:41
Yeah, sometimes things are "fashionable". If it's new dev, I avoid triggers if at all possible & prefer that everyone go through stored procedures. I've come to accept triggers more, but still loath ORM in general. – OMG Ponies Sep 08 '10 at 02:49
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 Sep 08 '10 at 03:06

score 0 · Answer 2 · edited May 23 '17 at 11:47

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.

notification that table rows have been changed

2 Answers2