Solved

Run code after insert record in SQL server

Posted on 2013-11-20
10
1,218 Views
Last Modified: 2013-12-24
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 ?
0
Comment
Question by:HugoMuller
10 Comments
 
LVL 19

Expert Comment

by:strivoli
ID: 39662423
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
 
LVL 8

Expert Comment

by:gpizzuto
ID: 39662447
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39662456
>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
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 39662479
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
 
LVL 25

Expert Comment

by:Shaun Kline
ID: 39662490
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:gpizzuto
ID: 39662500
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39662821
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
 
LVL 8

Assisted Solution

by:gpizzuto
gpizzuto earned 250 total points
ID: 39665439
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
 

Author Comment

by:HugoMuller
ID: 39665482
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39665953
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now