Re: Trigger problem (losing my mind here!)

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/10/05 

Date: Thu, 10 Feb 2005 15:32:10 +0100

The difference is that for the single INSERT, the remote insert is the only operation in the
transaction. For the trigger case, the remote insert is in the same transaction as the insert on the
STK_MOVEMENTS table. This makes it a distributed transaction, so DTC need to be in place.

Also, why are you INSERTing into OPENQUERY? Why not just use 4-part naming and specify the qualifies
table name, just as a regular INSERT? 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Fat Superman" <FatSuperman@gmail.com> wrote in message 
news:1108042691.241861.256170@f14g2000cwb.googlegroups.com...
> Hi everyone
>
> I have something interesting (annoying) going on with my trigger on
> SQL2K, I've noticed several threads on this in the past, but no
> resolution for the posters.
>
> I have a simple insert statement which puts a few fields into a table
> on a linked server.
>
> Code that works in SQL QA:
> INSERT OPENQUERY(DBODBC, 'SELECT * FROM STOCK') values ('StockCode',
> 111, 'Reference')
> This code works fine from SQL query analyzer but fails when run as a
> trigger.
>
> Code that FAILS in SQL trigger
> CREATE TRIGGER HN_TRAN_STKMOVE
> ON STK_MOVEMENTS
> AFTER INSERT AS
> INSERT OPENQUERY(DBODBC, 'SELECT * FROM STOCK') values ('StockCode',
> 111, 'Reference')
>
> The error I get is "The operation could not be performed because the
> OLE DB provider 'MSDASQL' was unable to begin a distributed
> transaction" - I don't understand the difference between running the
> code from QA and using it on a trigger!!?
>
> Anybody got any ideas?
> Thanks as always!
> David
>

Relevant Pages

  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Re: Transaction Abort in a trigger SQL 2008, writing to event log
    ... creating another transaction within the trigger. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Error handling
    ... If you want to ignore errors in the trigger, you can add SET XACT_ABORT OFF ... transaction to be rolled back. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Triggering an application from SQL Server?
    ... I was just wondering would it be possible to have a trigger in a SQL 2000 ... the trigger is pretty straight forward but having it launch a VFP ... A trigger executes in the context of a transaction - ie. ... If you must do it from SQL code then do so *outside* a transaction. ...
    (microsoft.public.fox.programmer.exchange)