Posted by AlterEgo on 01/19/07 19:26

tolcis,

There are a couple of considerations you might want to think about here.

1. Triggers should be designed as set operations. A trigger only fires once
for each batch that is being updated. If the batch updates multiple rows,
then your trigger should account for it. As it is written, your trigger
assumes only one row is getting updated.

2. You are already selecting a set where the appcode = 'I', why the if
@appcode = 'I' comparison later in the trigger?

3. Personal architectural philosophy: I would not execute a DTS package
directly from a trigger. There are many external kinds of issues that can
cause a DTS package to fail. I would make the interaction with the DTS
package more loosely coupled with the application. An alternative would be
to have the DTS package set up a job to run once a minute ( or hour, etc.).
First job step could check to see if any rows have to be processed ( appcode
= 'I' and > datesent = 0). If none need to be processed then exit the job
with success, otherwise execute the DTS package.

-- Bill

"tolcis" <nytollydba@gmail.com> wrote in message
news:1169229052.442503.88100@a75g2000cwd.googlegroups.com...
>I have a trigger that should be execute on each row insert and only if
> appcode = 'I' and datasent = 0. It should execute a DTS package. The
> DTS package by itself runs about 6 seconds. Trigger was created
> successfuly. When I try to insert a row my db hangs. I can see that
> SPID on my db is hang by SPID from master db. It doesn't complete
> until I kill that SPID. Why is that?
> This is the trigger that I am trying to execute:
> CREATE TRIGGER myExport
> ON ruExport
>
> FOR INSERT
> AS
> declare @appcode varchar (10)
> select @appcode = appcode from ruexport where appcode = 'I' and
> datesent = 0
>
> if @appcode = 'I'
> exec master..xp_cmdshell 'DTSRun /S "KOCL384017SQL" /N "Sys_Export" /G
> "{17D112A8-413E-420F-A624-3790BDFBED9F}" /W "0" /E'
>
> go
>
> Any suggestions?
>

[Back to original message]