Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run code after insert record in SQL server

Posted on 2013-11-20
10
Medium Priority
?
1,586 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 20

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 66

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 52

Accepted Solution

by:
Carl Tawn earned 750 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 27

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 70

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 750 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 70

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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

705 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