AFTER TRIGGER

I would like to create a trigger that after a DML event, let's say an
INSERT, will call an external exe file using xp_cmdshell and pass to the exe
the @.@.identity which resulted by the INSERT.
Is there a way to achieve this?Yan,
Doing this will compromise the transaction (i.e. if the external program
hang up, then the transaction will remain open for a long time). Also take i
n
mind that the statement that activated the trigger could have affected
multiple rows. May be if you tell the group what are you trying to
accomplish, somebody can come with a better approach.
AMB
"Yan" wrote:

> I would like to create a trigger that after a DML event, let's say an
> INSERT, will call an external exe file using xp_cmdshell and pass to the e
xe
> the @.@.identity which resulted by the INSERT.
> Is there a way to achieve this?
>
>|||Hi Yan
This trigger runs for me. "Kuku" is a table with an IDENTITY column. I hope
it helps.
CREATE TRIGGER TRG_KUKU_ON_INSERT ON [dbo].[Kuku]
FOR INSERT
AS
DECLARE @.idd int
DECLARE @.cmd NVARCHAR(100)
SET @.idd = @.@.identity
SET @.cmd = 'CommandToExecute ' + CAST(@.idd AS NVARCHAR)
exec master..xp_cmdshell @.cmd
Boaz Ben-Porat
Milestone Systems
Denmark
"Yan" <yanive@.rediffmail.com> wrote in message
news:OSQOgBTmGHA.2372@.TK2MSFTNGP04.phx.gbl...
>I would like to create a trigger that after a DML event, let's say an
>INSERT, will call an external exe file using xp_cmdshell and pass to the
>exe the @.@.identity which resulted by the INSERT.
> Is there a way to achieve this?
>|||Yan wrote:
> I would like to create a trigger that after a DML event, let's say an
> INSERT, will call an external exe file using xp_cmdshell and pass to the e
xe
> the @.@.identity which resulted by the INSERT.
> Is there a way to achieve this?
>
I would take a different approach, for a couple of reasons:
1. An insert doesn't always involve a single record, multiple records
can be inserted at once, thus you won't have a single ID to work with.
2. Involving an external process in a transaction is introducing a
point of failure. If the external process fails, or performs poorly,
your transaction will as will, causing the insert to fail or perform poorly.
I would instead write the trigger to insert all new ID's into a
"staging" table, and create a scheduled job that monitors that staging
table for new records. When new records are found, then run your
external process.|||Thank you Tracy for this post and I thank all other answeres as well.
I have some logic in my trigger to ensure we work just on a single record as
we expect.
I adopt your approach to have the trigger insert the Ids to another table on
which we can make what we need and not in the context of a transaction
(inside the trigger).
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uV6ShYTmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Yan wrote:
> I would take a different approach, for a couple of reasons:
> 1. An insert doesn't always involve a single record, multiple records can
> be inserted at once, thus you won't have a single ID to work with.
> 2. Involving an external process in a transaction is introducing a point
> of failure. If the external process fails, or performs poorly, your
> transaction will as will, causing the insert to fail or perform poorly.
> I would instead write the trigger to insert all new ID's into a "staging"
> table, and create a scheduled job that monitors that staging table for new
> records. When new records are found, then run your external process.