Linked-Server (Trigger in a Trigger Problem) 
Author Message
 Linked-Server (Trigger in a Trigger Problem)

I have a trigger on a table (a) which will make the insertion on a second
table (b),
now Table (b) has a trigger which makes an insertion on Table (c) on the
linked server.

my trigger for Table (A) is :

create trigger a1 on A for insert as
insert into b values ('xyz')

my trigger for Table (B) is :

create trigger b1 on B for insert as
begin distributed transaction
insert into linkserv.xyz.dbo.c values('xyz')
commit tran


can anybody tell me what's causing this..

i verified by checking  the MSDTC Services for distributed transactions and
they are running fine,,

Thanks.

Saleem Hakani



Fri, 02 May 2003 03:00:00 GMT
 Linked-Server (Trigger in a Trigger Problem)

When you work with triggers it treats all the operation as one transaction
(i.e.  if a trigger failed then it rolls back with the operation that
activated the trigger),  so maybe it failed on the trigger on table b.   You
can test it if you'll disable the trigger on table b,  and then see if the
trigger on table a works.  From my experience with linked servers (I have to
admit that I don't have much experience with linked servers),  you need to
specify SET XACT_ABORT ON in the trigger that works on the linked server.
Give it a try:-)

Adi


Quote:
> I have a trigger on a table (a) which will make the insertion on a second
> table (b),
> now Table (b) has a trigger which makes an insertion on Table (c) on the
> linked server.

> my trigger for Table (A) is :

> create trigger a1 on A for insert as
> insert into b values ('xyz')

> my trigger for Table (B) is :

> create trigger b1 on B for insert as
> begin distributed transaction
> insert into linkserv.xyz.dbo.c values('xyz')
> commit tran


> can anybody tell me what's causing this..

> i verified by checking  the MSDTC Services for distributed transactions
and
> they are running fine,,

> Thanks.

> Saleem Hakani



Sat, 03 May 2003 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. SQL 7: Problems w/ insert query on Oracle 8 linked server from trigger

2. SQL Server Trigger - MQ SQL Linked Server Table

3. Triggers, Triggers and more Triggers

4. update trigger on linked server (sql server 7.0)

5. Trigger Problem (Delete Trigger)

6. Triggers: Can SQL Server execute thousands of Triggers at a time

7. SQL Server triggers vs. Oracle triggers

8. Session problem with triggers and remote DB Link

9. Creating a Trigger which updates a linked server - Heterogeneous queries error

10. Linked Oracle Server and Triggers

11. Linked Server in Trigger

12. Trigger for linked server


 
Powered by phpBB® Forum Software