Run a vb net procedure when sql table insert is triggered

[Gum]

This is a question that was not answered in the dot Net forum and instead was
referred to the framework ADO.net forum:

I need to run a VB.Net 2008 procedure whenever there is an insert on a sql
table. Conceptually, I could create a trigger (either within Visual Studio
or SQL Server Mgmt Studio) that would be external (to each other).

How can I create a VB.net event triggered by a SQL table insert that could
be used to initiate action in a VB .net sub?

 

[sqlguru]

Create a cache dependency on the table in .NET.

 

[William Vaughn MVP]

What you're describing is a CLR trigger that invokes a VB.NET procedure.
This is not that hard to do (I have no idea what sqlguru is asking you to
do).
I describe (in great detail) how to do this in my 7th edition. Check it out
at the library.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Bill,

Sorry, but this is alone marketing, in my idea you should at least give
another hint than alone to your book.

Cor

 

[Cor Ligthert[MVP]]

To be more implicit.

I have given Gum the link to SQL notification service, but I knew that it
was not what he was asking and I have seen answering his question more times
by you.

Therefore I told that this newsgroup was better for him to ask his question.

I am at home, I don't have your book here, and because you know it, I did
not take to time to invest it, it is something I simply wont use.

Cor

 

[Gum]

Thanks Cor. What I have noted is that some remarks/suggestions that appear
brief can be indeed quite profound. So I never take just look at
appearances. Your suggestion of this discussion group is quite good and I
know that you wanted to help.

The suggestion of sqlguru points to a great device, but it involves polling
the database every x milliseconds and unexpected events may occur (as it
happens at times) when the database event (eg. insert) occurs in the middle
of a polling event.

Somehow I suspected that the answer could be in the CLR Trigger and I tested
it. However as often happens, the code implementation is quite simple, but
the details - auditing - control feedback loop - of the trigger in .Net
proved elusive.

I purchased Bill's book many months ago, so when he suggested that I read
his book, I looked at page 910 and onwards to see whether it answers my
questions. Based on page 912, I gather that the class library
SqlTriggerContext.TriggerAction properties are relevant and when an insert
trigger code is called the TriggerAction.insert is true. I will test and
confirm how this works and respond accordingly.