Trigger DTSRun Problem

[ChuckGann]

Hi everyone,

We are trying to call a DTS package upon change to a table in SQL2000. The trigger itself runs fine, but it is causing an error message within the application (Great Plains). I think I am misunderstanding when the trigger executes.

What we would like is for the trigger to execute after the transaction(s) in the original table have been committed. It seems to run before they are committed. Is there a way around this?

Thanks.

 

[SQLDenis]

after or instead of trigger?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

>>What we would like is for the trigger to execute after the transaction(s) in the original table have been committed

The trigger fires upon a DML event on the table, it fires when that event happens, it can not fire after the transaction has been commited because how would a rollback occur if the trigger throws an error

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ChuckGann]

Sorry, it is an After trigger.

 

[SQLDenis]

see answer above your answer

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ChuckGann]

It sounds like we are trying to get this to do something its not designed to do. The DTS package that the trigger is calling is copying a modified set of data from the original table into a staging area. It is not testing data integrity or anything like that.

Is there another good way of doing this besides setting some kind of schedule to test the data for changes at a particular time increment?