How to cast an event outside

[Sandro Santucciu]

Hello there, i'm new here. I am a young DB admin, my workmates here have just asked me to do something I have never done before: they want to substitute their polling procedures, developped in C# and Silverlight in a Visual Studio 2010 Environment, with a trigger in SQL Server 2008 RD2 that, for each record inserted, could cast an event outside so they know if a table has been update recently without keep checking the database.
Do someone has a hint on how to do this? Is this possible anyway?

Regards,
Thank you.

[Andrew Morton]

Post by Sandro Santucciu
Hello there, i'm new here. I am a young DB admin, my workmates here
have just asked me to do something I have never done before: they
want to substitute their polling procedures, developped in C# and
Silverlight in a Visual Studio 2010 Environment, with a trigger in
SQL Server 2008 RD2 that, for each record inserted, could cast an
event outside so they know if a table has been update recently
without keep checking the database. Do someone has a hint on how to
do this? Is this possible anyway?

This might be of some use:

"Build Apps that Provide Real-Time Information and Customized Content Using
SQL Server Notification Services"
http://msdn.microsoft.com/en-us/magazine/cc188913.aspx

[Erland Sommarskog]

Post by Andrew Morton

Post by Sandro Santucciu
Hello there, i'm new here. I am a young DB admin, my workmates here
have just asked me to do something I have never done before: they
want to substitute their polling procedures, developped in C# and
Silverlight in a Visual Studio 2010 Environment, with a trigger in
SQL Server 2008 RD2 that, for each record inserted, could cast an
event outside so they know if a table has been update recently
without keep checking the database. Do someone has a hint on how to
do this? Is this possible anyway?

"Build Apps that Provide Real-Time Information and Customized Content
Using SQL Server Notification Services"
http://msdn.microsoft.com/en-us/magazine/cc188913.aspx

SQL Server Notification Server is a deprecated product and its not
involving. It would not be a good idea to use it.

It would be possible to implement a trigger in the CLR, which distributes
this event. However, it would not be a good idea. A trigger should execute
quickly, as it executes in the realm of a transaction where you hold locks.
Also, the idea with a trigger is that if it fails, the statement fails. What
is the event cast fails because the receiver is down. Do you want a rollback
in this case?

A better solution is to put a message on a Service Broker queue. Then you
can have various solution on the other end of the queue. You could have a
stored procedure that performs the event-casting. You could also have
external activation, and, yes that essentially means polling. Except that
you can make a call which will wait until something happens.

[Sandro Santucciu]

Thank you Mr. Morton your answer was quite useful. I was thinking to use a Service Broker too, but my workmates believed the trigger could be much of use; now that they know it involves a transaction too they want to stuck with polling again because is already done.

Thank you again, bye.

[Andrew Morton]

Post by Sandro Santucciu
Thank you Mr. Morton your answer was quite useful. I was thinking to
use a Service Broker too, but my workmates believed the trigger could
be much of use; now that they know it involves a transaction too they
want to stuck with polling again because is already done.

I'd go with any answer Erland gives over suggestions I might make regarding
SQL any day of the week.