trigger blocking

[lendbz]

Hi,

I am trying to implement a trigger that run a dts package when something is inserted.

My trigger is like this
"CREATE TRIGGER [RUNupdateDTS] ON [dbo].[temp]
FOR INSERT
AS
exec master..xp_cmdshell 'DTSRUN /S (local) /e /n import_iqvc_inventory ' "

In my dts package, one of the step is to transfer those data from the temp table to another table, with the following transfer data task statement in the dts package
" SELECT SKU, Description, Price, MerchantSKU
FROM temp"

Now i try to insert something to my temp table to test the trigger, like:

"insert into iqvcimporttemp (SKU, description, price, merchantsku) values ('343', '34343', 22.22, 'cc11')"

The trigger does start and run the dts package, but then it frooze... I look and found that it is stopping at the point when it's trying to transfer the data from the temp table to another table, at that select statement above, it is being blocked by that insert statement.. How would i make it work???

thanks,
lenny

 

[mrdenny]

You won't be able to select that record until it has been completly commit, which means until after the trigger in done running.

You can try using the with (nolock) on your select statement. That will do a dirty read and may get you what you are looking for.

select *
from temp with (nolock)
where ...

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[lendbz]

oh ok thanks. but is there any way to start the trigger(and thus the select statement) only after the insert has been committed?

 

[mrdenny]

Nope, the trigger must be completed before the insert can be commited. If the trigger fails then the insert must be rolled back.

Why are you running a DTS package? What command can't be done via T/SQL directly within the trigger?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[lendbz]

OH. well, in the DTS package i have a connection to the foxpro database, and it insert data from that sqlserver temp table to the foxpro table... can that be done with T/SQL inside the trigger???