We help IT Professionals succeed at work.

MS SQL Server event on insert

I need to know if this is possible to do with MS SQL Server 2008 and if it is, if someone could point me to how to accomplish this.

1. Have a couple .net clients connect to a database.
2. Each .net client "subscribes" to events that are created when a row is inserted into a particular table.
3. One client inserts a row into that table and an event is triggered on SQL Server and reaches all clients.

Comment
Watch Question

You could do it with a table that contains information about the subscribed clients, then on the relevant tables, use a trigger that checks this table to notify the client.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
Just create triggers on that particular table to INSERT records into other tables (for all clients) which should suffice.

Author

Commented:
rpoole69 - how would the trigger notify the client?  I guess if I wrote the trigger in .net, I can send a message - right?

rrjegan17 - with your approach the clients would have to poll their respective tables.  I'm looking for a way to have the server notify the client.



Author

Commented:
I'm looking for MS SQL Server's analog of this postgres feature:

http://www.postgresql.org/docs/8.4/static/sql-notify.html
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
>> I'm looking for a way to have the server notify the client.

We don't have any equivalent approach as that mentioned in the Postgres documentation..
Instead you can create an application to check for any new records in those tables and send an alert mail accordingly.

Author

Commented:
I believe I can write a trigger in a .net language on MS SQL Server - correct?  A .net language will allow me to send messages.  Do you think I can send a message from a trigger written in .NET to day MSMQ?

If I can send a message from a trigger, then I'm halfway there.  The other half is the subscription.  I suppose a client that wants to subscribe can insert a subscription request into a table like rpoole69 suggests.  The difficulty with this approach is to cleanup subscriptions for a connection when the connection terminates.  Is there some way to trigger an action on the server when a connection terminates?

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
>>  A .net language will allow me to send messages.  Do you think I can send a message from a trigger written in .NET to day MSMQ?

Yes, you can do it but its not recommended as in some cases it might impact your overall server performance in case of any uncommitted transactions..

>> Is there some way to trigger an action on the server when a connection terminates?

no, unless otherwise you log the termination of a connection in some table and check the table frequently for any new records..

Author

Commented:
rrjegan - you're saying that the commit operation would be delayed because a trigger that sends MSMQ messages would be pretty slow and the commit operation would take a long time?  I can probably speed this up by launching a separate thread to send the message or by queueing the outgoing messages in memory and sending them on a dedicated thread.  Do you know if I can launch a thread in a trigger written in a .net language?

I recall on Sybase 11.9.2 a connection can create #temp tables that are tied to a connection.
When the parent connection is terminated, the server drops the #temp table.  I would think that MS SQL server has the same feature.  Is it possible to attach a trigger to be executed when a #temp table is dropped?
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
Yes, you can do it but initiate a new thread if you really don't care the outcomes of the operations done in the thread.

>> Is it possible to attach a trigger to be executed when a #temp table is dropped?

Nope, not possible in SQL Server..
Unless otherwise you drop it explicitly using DROP TABLE, you can't initiate the trigger.

Author

Commented:
Since this question has no answer, I will not add it to the solution base.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
Thanks for adding it to solution base and you have done the correct thing.

Author

Commented:
Looks like there is a feature called "Query Notifications" that does what I ask in the original question.
http://msdn.microsoft.com/en-us/library/t9x04ed2