Using xp_cmdshell in INSERT trigger: bad idea?

[Joel Thornton]

Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?

I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the insert trigger finish
up?

The alternative solution seems to be frequent polling of the table in
question; while this isn't really a great solution it would seem to
circumvent a table lock brought about by the INSERT trigger.

Thoughts?

Joel

[WangKhar]

Why not do it as a part of the insert. Make peoples inserts go with a
parameterised stored proc, and call it at the end/middle/beginning...
why do they need direct access?
jo****@eml.cc (Joel Thornton) wrote in message news:<c1**************************@posting.google. com>...

Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?

I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the insert trigger finish
up?

The alternative solution seems to be frequent polling of the table in
question; while this isn't really a great solution it would seem to
circumvent a table lock brought about by the INSERT trigger.

Thoughts?

Joel

[Simon Hayes]

jo****@eml.cc (Joel Thornton) wrote in message news:<c1**************************@posting.google. com>...

Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?

I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the insert trigger finish
up?

The alternative solution seems to be frequent polling of the table in
question; while this isn't really a great solution it would seem to
circumvent a table lock brought about by the INSERT trigger.

Thoughts?

Joel

Calling external commands from a trigger is generally considered a bad
idea, for exactly the reason you mention. Anything inside a trigger is
inside a transaction, so you want it to complete as fast as possible,
to prevent blocking. Also, if the process you call never returns at
all, or returns something unexpected, you may have a problem.

One solution is to use the trigger to insert a record into a second
table, then poll that table using a scheduled job which calls your
external program. That way you avoid touching the 'main' table as much
as possible.

Simon

[Greg D. Moore \(Strider\)]

"Joel Thornton" <jo****@eml.cc> wrote in message
news:c1**************************@posting.google.c om...

Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?

I understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the insert trigger finish
up?
Basically yes, you risk locking others out of your table. And what's worse,
if for some reason the external process hangs, your DB is not basically
locked up.

The alternative solution seems to be frequent polling of the table in
question; while this isn't really a great solution it would seem to
circumvent a table lock brought about by the INSERT trigger.
It really depends on what you want to do. Polling is one option. Or as
another poster said, possibly a stored proc.

Perhaps if you explain what exactly you want to do in the command shell that
may help.


Thoughts?

Joel

[Joel Thornton]

> It really depends on what you want to do. Polling is one option. Or as

another poster said, possibly a stored proc.

Perhaps if you explain what exactly you want to do in the command shell that
may help.

It sounds like polling will be the way to go. I would use a stored
procedure for the insert, but the insert is actually being done by a
third-party app which only knows how to insert a record to a table via
ODBC. Correct me if I'm wrong, but I don't think having a secondary
"initial insert" table with trigger would help because I will again be
locking that table until my xp_cmdshell call finishes up.

Thanks for the info. I am probably going to have it poll every 5
seconds so that there is little noticeable lag on the user's side
(which was my motivation for wanting it in the trigger).

Maybe the next incarnation of T-SQL will have fork(). ;)

Joel