Solved

Run code after insert record in SQL server

Posted on 2013-11-20
10
1,404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 26

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
 
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:Scott Pletcher
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:Scott Pletcher
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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