Using trigger to notify an assembly of changes.

[simo]

I have a requirement to create a sorted list of objects, stored within
an assembly, and I would like to use a After Insert/Update trigger to
notify that assembly that something has changed, rather than polling
the database for changes all the time.

My initial problem is that I need to create a static list so that it
can be dynamically looked at by other assemblies, but SQL Server won't
allow me to attach a dll which contains a static method.

Has anyone done something like this? Are there any decent examples
available anywhere?

any help will be appreciated.

simo

[Hugo Kornelis]

On 12 Oct 2006 19:31:52 -0700, simo wrote:

>
I have a requirement to create a sorted list of objects, stored within
an assembly, and I would like to use a After Insert/Update trigger to
notify that assembly that something has changed, rather than polling
the database for changes all the time.

My initial problem is that I need to create a static list so that it
can be dynamically looked at by other assemblies, but SQL Server won't
allow me to attach a dll which contains a static method.

Has anyone done something like this? Are there any decent examples
available anywhere?

any help will be appreciated.

Hi simo,

Not a direct reply to your question - but have you looked into using
Query Notifications rather than using triggers to roll your own
notification solution? Query Notifications are designed specifically to
notify front-end applications when the result of a query has changed.
Note that Query Notifications work only on SQL Server 2005.

Here's a link to the relevant documentation in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/06b1c6fa-3024-4425-bd99-64b6d3c80267.htm

--
Hugo Kornelis, SQL Server MVP

[simo]

Not a direct reply to your question - but have you looked into using
Query Notifications rather than using triggers to roll your own
notification solution? Query Notifications are designed specifically to
notify front-end applications when the result of a query has changed.
Note that Query Notifications work only on SQL Server 2005.

Here's a link to the relevant documentation in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/06b1c6fa-3024-4425-bd99-64b6d3c80267.htm

--
Hugo Kornelis, SQL Server MVP

Thanks for that Hugo,
The Query Notifications stuff is pretty cool and I have implemented it
and got it working - all very impressive.
I'm actually using it in a service object to check the status of
records in a specific table waiting to be updated by the UI. Where
the guy who has written the Web Front End is polling the database to
find out when my service has updated the status - but the web front end
is consuming about 30% CPU resources and the Query Notification is
using no noticable resources - so he might be doing the same thing
next.

My only problem was that the MS documentation for this was very
detailed about the internals of how all this works, but completely
lacked any useable examples - but we have google and found a few
examples that were quite good.

the best I found was at code magazine by julia lerman.
http://www.code-magazine.com/Article...uickid=0605061

Cheers

simo

[Hugo Kornelis]

On 17 Oct 2006 17:00:00 -0700, simo wrote:

(snip)

>My only problem was that the MS documentation for this was very
detailed about the internals of how all this works, but completely
lacked any useable examples - but we have google and found a few
examples that were quite good.

Hi simo,

(Sorry for the late reply)

Thanks for the feedback. I will make sure that folks from Microsoft get
to see your message.

I have no personal experience implementing Query Notifications, so I
can't make any further comments on this issue.

--
Hugo Kornelis, SQL Server MVP

[Hugo Kornelis]

On Sun, 22 Oct 2006 23:26:53 +0200, Hugo Kornelis wrote:

>On 17 Oct 2006 17:00:00 -0700, simo wrote:

(snip)

>>My only problem was that the MS documentation for this was very
detailed about the internals of how all this works, but completely
lacked any useable examples - but we have google and found a few
examples that were quite good.

Hi simo,

(Sorry for the late reply)

Thanks for the feedback. I will make sure that folks from Microsoft get
to see your message.

Hi simo,

Here's the reply I've gotten from Microsoft:

>We are aware of the lack of examples in the QN documentation. There is an
existing doc bug to fix this, but I don't know at this time when that bug
will be resolved.

--
Hugo Kornelis, SQL Server MVP