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]

Re: Using xp_cmdshell in INSERT trigger: bad idea?

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?


joelpt@eml.cc (Joel Thornton) wrote in message news:<c190a45a. 0401272101.258f bedc@posting.go ogle.com>...[color=blue]
> 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[/color]

[Simon Hayes]

Re: Using xp_cmdshell in INSERT trigger: bad idea?

joelpt@eml.cc (Joel Thornton) wrote in message news:<c190a45a. 0401272101.258f bedc@posting.go ogle.com>...[color=blue]
> 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[/color]

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\)]

Re: Using xp_cmdshell in INSERT trigger: bad idea?


"Joel Thornton" <joelpt@eml.c c> wrote in message
news:c190a45a.0 401272101.258fb edc@posting.goo gle.com...[color=blue]
> 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?[/color]

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.
[color=blue]
>
> 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.[/color]

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.

[color=blue]
>
> Thoughts?
>
> Joel[/color]

[Joel Thornton]

Re: Using xp_cmdshell in INSERT trigger: bad idea?

> It really depends on what you want to do. Polling is one option. Or as[color=blue]
> another poster said, possibly a stored proc.
>
> Perhaps if you explain what exactly you want to do in the command shell that
> may help.[/color]

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