suggested architecture\pattern to windows service which monitors and notify by mail for changes?

i want to build a windows service that will notify some admins in the company for main BL that changes in the DB. does someone can s开发者_运维问答uggest how to build it that it will not need maintenance and in a smart way. (like writing the stored procedures in a table ?). which technologies can i use ?( like SQL dependency?) if anyone has done something like that i would be happy to hear advices...

---

There are 3 basic patterns to do this.

1. Notification event, what is being changed raises an event "Hi someone changed me", and then you catch that event.
2. Polling, the client asks the database what happened since I last asked you what happened.
3. Gateway, all changes made through a gateway, the gateway reports on which changes have been made.

Which one you choose will depend on what technology you have available and what your requirements are as to how fast you need to know about the change.

---

SQL Server comes w/ a lot of way to capture changes (including data specific and schema specific). See Change Data Capture and other topics like DDL Trigger, Event Notification and SQL Trace. In your case, I think it's the later 3 topics.

All 3 methods should be able to run in database scope or even server wide scope (meaning you can set this once and forget about it, thus should satisfy your maintenance free requirement).

That combined with Database Mail capability of SQL Server, you should be able to implement this without having to resort to custom windows service.

Just a word of caution... People think this is a good idea, but when not implemented correctly, expect a mailbox full of SPAM, SPAM and more SPAM. hint: Temp Table manipulation.