Raising an event when a row is added

[Bob Day]

Using VS 2003, VB, MSDE...

SQL triggers seems to enable you to do other SQL things. I am looking for a
way for ans SQL Trigger to essentially Raise An Event to trigger a thread to
do something that is non SQL.

In other words, when a Row is added to a specific table, how can I aleart a
different thread than the one that added the row that this has happened? I
am not sure trigger is even the right approach.

Any ideas?

Thanks

Bob

 

[Wes Brown]

Bob,

Are you inserting the row from your code? If so the easiest way to handle
notification of the insert would be from whatever class did the insert,
not from SQL Server itself. I don't think there is any way to raise an
event from inside Sql Server that can be consumed by a .Net class. If
some remote application (or person or whatever) is inserting a new row you
might want to make a trigger on the inserted table that updates another
table with the datetime of the insert. You could then periodically poll
the insert-datetime table to see if a record was added since your last
poll.

Wes

 

[Kevin Yu [MSFT]]

Thanks for Wes's quick response.

Hi Bob,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to be notified when a row is
added to the SQL server. If there is any misunderstanding, please feel free
to let me know.

As far as I know, the SQL server trigger cannot be passsed to .NET
framework. So we have to use other ways to be aware of that. If you're
using a SqlDataAdapter to update the DataSet, we can handle the
SqlDataAdapter.RowUpdating event. In the event handler, we can check the
value of e.Row.RowState property. It stands for whether the row is going to
be inserted, updated or deleted. If the value is Added, it will be added to
the database table. So we can do something in the event handler.

However, this can only capture the insert actions that are done through the
SqlDataAdapter. If the insert is done with a SqlCommand.ExecuteNonQuery(),
I don't think we can be notified in .NET framework.

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Bob Day]

Yes, that was helpful. Thanks
Bob Day