SQL Server Trigger question

[ego-adam]

Hi All,

I have a cf app accessing data from a database that is controlled primarily by
our MRP software. My problem isn't really ColdFusion related, but MS SQL. Sorry
for posting it here, but I am a CF programmer, not an SQL guru.

I wrote a trigger to capture records (and copy them to an external table) as
they are inserted from within the MRP. (MS SQL Server 2000). The trigger is
succefull when I manually insert records, but not when the MRP inserts them. I
can only assume, that the MRP inserts the records from within a transaction,
and because my trigger fires during that transaction, the transaction fails and
get rolled back.

How do I write a trigger that wont interupt the transaction?

Any help would be great! Thanks!

[paross1]

I'm not a SQL Server guru, but wouldn't your trigger be considered to be a
nested transaction if it was written that way? I've never used triggers in SQL
Server myself, but I have used them with Oracle, so it wouldn't hurt to take a
look at how it was written.

There is a lot of information in SQL Server BOL regarding nesting
transactions, rollbacks in stored procedures and triggers, etc., which may shed
some light on how to write your trigger to fire within a transaction and not
get rolled back.

Phil

[SQLMenace]

A trigger firing will no cause a transaction to rollback
use profiler to capture the SQL from your MRP, then rerun the exact statement in QA and see what happens

[sdupre]

There was a bug in SQL Server INSERT triggers found in 3.4 and fixed in 3.5.

See if the 3.5 driver description (in the coldfusion hotfix site) is the problem you're having.