Using xp_cmdshell in INSERT trigger: bad idea? -- sql server (alternate)

Wangkhar NO[at]SPAM yahoo.com
1/28/2004 2:16:54 AM

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?

[quoted text, click to view]

sql NO[at]SPAM hayes.ch
1/28/2004 2:21:16 AM

[quoted text, click to view]

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.

joelpt NO[at]SPAM eml.cc
1/28/2004 10:46:28 AM

[quoted text, click to view]

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(). ;)

Greg D. Moore (Strider)
1/28/2004 1:45:12 PM

[quoted text, click to view]

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.

[quoted text, click to view]

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Using xp_cmdshell in INSERT trigger: bad idea?