Disabling/enabling of trigger & distributed transaction

I've 2 Windows 2000 server running each own instance of SQL2000. I've setup
both linked servers @. both end.
At server A, it'll call a sp in server B, whereby this sp will update server
B tables based on server A's data. And the server A table A will trigger
back to server B.
However due to some business logic and distributed transaction don't allow a
loopback operation, is there any method the trigger can be bypassed?
I can't drop this trigger as it need to be maintained when other process
access this table.
My question is, can a disable/enable trigger be issued in this sp?
What if another process updating this table at the same time and need the
trigger to be enabled? Will this process be queued after this completion of
sp?
Please advice on how feasible can trigger be controlled from firing..
Server_A call ServerB.sp
|
V
store procedure @. serverB
begin transaction
-- disable trigger
insert into serverB.dbB.dbo.tableB
select * from serverA.dbA.dbo.tableA where key=X
commit transaction
-- enable trigger
|
V
serverB.dbB.dbo.tableB trigger back to serverA
if record not found in serverA
insert into serverA
else
update into server A
Thanks in advance
KristeHi
You can do an ALTER TABLE with the ENABLE/DISABLE TRIGGER, but, the
disabling is for all connections.
The only way you could do this is to have the trigger look up in a table
what connections it can fire for. Not elegant, but might be workable.
Regards
Mike
"Kriste L" wrote:

> I've 2 Windows 2000 server running each own instance of SQL2000. I've setu
p
> both linked servers @. both end.
> At server A, it'll call a sp in server B, whereby this sp will update serv
er
> B tables based on server A's data. And the server A table A will trigger
> back to server B.
> However due to some business logic and distributed transaction don't allow
a
> loopback operation, is there any method the trigger can be bypassed?
> I can't drop this trigger as it need to be maintained when other process
> access this table.
> My question is, can a disable/enable trigger be issued in this sp?
> What if another process updating this table at the same time and need the
> trigger to be enabled? Will this process be queued after this completion o
f
> sp?
> Please advice on how feasible can trigger be controlled from firing..
> Server_A call ServerB.sp
> |
> V
> store procedure @. serverB
> begin transaction
> -- disable trigger
> insert into serverB.dbB.dbo.tableB
> select * from serverA.dbA.dbo.tableA where key=X
> commit transaction
> -- enable trigger
>
> |
> V
> serverB.dbB.dbo.tableB trigger back to serverA
> if record not found in serverA
> insert into serverA
> else
> update into server A
>
> Thanks in advance
> Kriste
>
>