How to be notified when a insert/update happens?

Question

Post reply

How to be notified when a insert/update happens?

willian-1136841

SSChasing Mays

Points: 645
More actions
August 5, 2009 at 6:21 am

#93935

Hi!
I have a table where the users are always adding information. I'll like to show in my application a message to all them every time a row is inserted or updated.
In Interbase there is a "event" process that does this communication.
But, here in SQL Server, is there a "event" like that? How could I send a message to all clients when that table changes?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Two possibilities come to my mind:

1) You could send a mail to all users from a trigger.

2) Insert the rows into a message table from a trigger and then read, notify and delete rows from the app side in a recurring task

Hope this helps

Gianluca

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 2

well it can be done...but the better question is do you really want to....you might not after you think through the ramifications...

lets assume that someone adds a new record and saves, then edits a record, saves, then edits it a little more, then saves again...all within say, ten seconds.(this is a very common data entry situation...heck, i re-edit my forum posts a couple of times after the original posting, after re-reading and reviewing the post)

do you really want to send three emails saying data has changed within that time? how often do changes to this data occur that you want to alert a group of people?

ok now the technical aspects:

in SQl 2005, the procedure msdb.dbo.sp_send_dbmail is asyncronous, meaning the it doesn't wait to completely send the email before it returns control back tot eh calling procedure. that means if you use it in a trigger whent eh event occurs, then you won't suffer performance wise.

rather than a trigger, I'd suggest tracking which items changed, and polling whether enough changes have occured to justify sending an email that identifies all the changes up to a certain point...more like a once a day notification, or once a week, rather than instantaneously in a trigger.

-=Edit=- Gianluca and I are on the exact same page...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

willian-1136841 SSChasing Mays Points: 645 More actions · Answer 3

Thanks for answer!

But I don´t wanna send a e-mail: I just need send a "event" that can be captured by Delphi. I have a toolbar on my program that changes the buttons color: each color is a status of a specific row. I wanna that toolbar read this unique table all time it changes, I don´t wanna a timer that reads at a specific time, I wanna know in my Delphi program that a "event" happened and so I'll read that table again.

Is this possible?

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

It can't be done that simply. There's no such feature in SQL Server. Plenty of workarounds, but no such feature.

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 5

Yeah, SQL Server is really stateless in that situation, like a web page would be...unless you poll for a new snapshot of data,and compare against your local cache to see if they are different, there is no way to determine that a change occurred.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Gianluca Sartori (8/5/2009)
Two possibilities come to my mind:
1) You could send a mail to all users from a trigger.
2) Insert the rows into a message table from a trigger and then read, notify and delete rows from the app side in a recurring task
Hope this helps
Gianluca

The bad part about #1 above is if there is any latency or the email server is down... BOOM!

I cheat on things like this... xp_CmdShell to a batch file with a NETSEND.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Jeff Moden (8/5/2009)
I cheat on things like this... xp_CmdShell to a batch file with a NETSEND.

LOL!

Haven't heard of NET SEND in the last 5 years!

It's disabled by default in every windows installation >= XP SP2!

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Gianluca Sartori (8/6/2009)
Jeff Moden (8/5/2009)
I cheat on things like this... xp_CmdShell to a batch file with a NETSEND.
LOL!
Haven't heard of NET SEND in the last 5 years!
It's disabled by default in every windows installation >= XP SP2!

I love things like Net Send... it all falls into the category of (pardon the poetic license), "You can't teach a new dog old tricks." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 9

I am forced to use Vista at work - I miss NET SEND!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

pothikmoney Newbie Points: 1 More actions · Answer 10

ok now the technical aspects:

in SQl 2005, the procedure msdb.dbo.sp_send_dbmail is asyncronous, meaning the it doesn't wait to completely send the email before it returns control back tot eh calling procedure. that means if you use it in a trigger whent eh event occurs, then you won't suffer performance wise.

rather than a trigger, I'd suggest tracking which items changed, and polling whether enough changes have occured to justify sending an email that identifies all the changes up to a certain point...more like a once a day notification, or once a week, rather than instantaneously in a trigger.

Ashley Madison [/url]

nigel. SSChampion Points: 11636 More actions · Answer 11

Have a look at this:

http://www.simple-talk.com/sql/sql-server-2005/using-and-monitoring-sql-2005-query-notification/

I tried it some time ago but could never get it to work, you might have more luck.

It all depends whether you can reference the .NET assemblies in Delphi (I was using C# so not a problem), never used Delphi so I'm no use there, sorry.

I just thought it may give you somewhere to start.

This may help too: http://www.google.com/search?q=Query+Notifications

Good luck

Nigel

Nigel
How to post questions