Run code after insert record in SQL server

My company is using a SQL server 2012 in combination with an IP barcode scanner. The software bundled with the barcode scanner insert records in a specific table in a SQL Server database. So far everthing works fine.

I would like to run some code (in my case VB) or an external .EXE every time a new record is inserted. So I am basically looking for a solution/service/task that monitors my table and fires an exe on insert.

I have looked at a trigger and EXEC MASTER XP_CMDSHELL but our DBA wont let me do anything on the SQL server.

Who's can point me out towards a proper and stable solution ?
HugoMullerAsked:
Who is Participating?
 
Carl TawnSystems and Integration DeveloperCommented:
One option might be to write a Windows Service that uses SqlDependency, in conjunction with Service Broker, to notify you when a record is added.

Simple introduction is available here: http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events

Assuming your DBA does/will allow Service Broker of course :)
0
 
strivoliCommented:
You might set a trigger that fires an event in the Windows Application Log (run eventvwr).
Then, set a Task scheduled to run each time THAT event happens.
0
 
gpizzutoCommented:
If your DBA doesn't allow the creation of TRIGGER or EXTERNAL STORED/PRODECURES, you need to write an application that inspects at fixed times your table and then performs your actions.
I think that the command "SELECT count(*) FROM [Your_Table]" is quick and not so expensive to be called several times each minute.
Get the number of records of your table to understand where a record has just been added.
If data in your table are deleted, then call "SELECT max(Date_Time_Of_Insert) FROM [Your_Table]" or better, if you have an Identity Column: "SELECT IDENT_CURRENT ('[Your_Table_Name]')".
Hope this helps.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The software bundled with the barcode scanner insert records in a specific table
Just to 'check the box' here, is it possible that either the vendor of the software, or you guys, can add this functionality to the front-end software itself?

I can understand a DBA's resistance to add a trigger in itself, let alone one that calls an outside object.
0
 
Shaun KlineLead Software EngineerCommented:
As an additional point, you should be careful about modifying a database used by third-party software. If the vendor ever puts out an upgrade to the software, the upgrade could potentially remove any modifications made to the database.
0
 
gpizzutoCommented:
To enable service_broker you should modify the database settings and call:

ALTER DATABASE [Your_DB] SET ENABLE_BROKER

I don't know if your dba agrees ;-)
0
 
Scott PletcherSenior DBACommented:
It's not quite that simple -- SQL must get an exclusive lock on the db to do the enable broker, and that can be very tricky to get on a busy production db.

Your DBA is somewhat lazy if he/she is just arbitrarily blocking all triggers.  But I 100% agree that no trigger should ever be allowed to call an external .exe -- that's a disaster waiting to happen!

Instead, you could add a tinyint column to the table to indicate that a (new) row needs processed.  Then have a job that runs continually to check that column, do the processing, and reset the column's value when complete.  The DBA can build you a filtered index on that column that will be extremely efficient for finding only the rows that haven't been processed yet.
0
 
gpizzutoCommented:
Another approach:

- Create a new database (your own)
- Re-direct the IP scanning on YOUR database, let's say in table [MyTab]
- Create an insert-trigger on [MyTab]: preform your activities inside it
- Populate the original table with data

Your DBA should be happy !!!
0
 
HugoMullerAuthor Commented:
Hi All,

Thanks for your input. Just a short nuance.
I'm not allowed to create a trigger that calls an external exe by CMDSHELL, however a trigger with TSQL is no problem.

So is there a way to execute my VB .NET code fired by an INSERT trigger ?
0
 
Scott PletcherSenior DBACommented:
Again, running any external code directly from a trigger is a bad idea.

Instead, use the trigger to:

send a message that causes a separate process to fire, using SQL's built-in broker and messaging capabilities
OR
can add a row to another table that is processed every <n/nn> minutes, typically using a SQL job
OR
<other>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.