Having Trouble with triggers!! ~ Please Help!
From: JR_DBA (anonymous_at_discussions.microsoft.com)
Date: 02/03/04
- Next message: JoseR_DBA: "Data Source Language"
- Previous message: David Wilson: "RE: Log Reader Agent Syntax Error"
- Next in thread: Jim Johnston: "Re: Having Trouble with triggers!! ~ Please Help!"
- Reply: Jim Johnston: "Re: Having Trouble with triggers!! ~ Please Help!"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 3 Feb 2004 07:35:15 -0800
Hi everyone,
I am trying to create a one-way replication between a
master database and three satellite using triggers. In
this particular case, SQL Replication is not available as
an option, so I have to stick to triggers.
Now, for the interesting part; I create a trigger that
fires whenever a new record is inserted, which looks
something like this:
/*
***
create trigger <<trigger name>> on <<[dbo].[<<table
name>>] after insert as
declare @srecid int -- Source Record ID
select @srecid=(select tb1.<<id field>> from <<table
name>> tb1 inner join inserted i on i.<<id
field>>=tb1.<<id field>>)
-- Then I pass the source record id to a stored procedure
that does the update to the destination databases...
exec <<stored procedure>> @recid=@srecid
GO
***
*/
I created two test databases, one on the same server as a
local satellite database, and another on a remote test
server to be used as my remote satellite test.
What is interesting about it is that my triggers do work
for databases that are within the same server, but if
introduce the remote server name to the insert statement;
my process goes into an infinite loop. It will not
commit the transaction on the master database, and it
seems as if it could not even touch the remote server. I
tried to run a profiler, but I was unable to capture
anything throwing any errors. The process simply sits
there. When I try to kill that process, then it changes
the command to "KILLED/ROLLBACK" but remains alive until
we power cycle the machine. I've done some research, and
based on that I was able to determine that in fact there
was a transaction pending, but fortunately was not
blocking anyone from performing any work, nor takes much
of the server resources, I used every technique that was
offered using EM and through QA, nothing worked, the
process remains alive.
But wait, it gets even stranger...
So far, everything points to something really wrong with
the stored procedure, right?!... Well, I took all the
code from the trigger itself, and placed it on new stored
procedure. The most significant difference I can think
of is that the record has to be already created on the
master table (of course, I am going around the trigger),
so I take an existing ID, pass it to the new "calling"
stored procedure, which will then pass it to the "insert"
stored procedure, guess what, it works!
No changes to the "insert" stored procedure whatsoever.
Any thoughts, comments, prayers? I'll take anything,
even Prozac!! j/k
Your help will be greatly appreciated!
Jose
- Next message: JoseR_DBA: "Data Source Language"
- Previous message: David Wilson: "RE: Log Reader Agent Syntax Error"
- Next in thread: Jim Johnston: "Re: Having Trouble with triggers!! ~ Please Help!"
- Reply: Jim Johnston: "Re: Having Trouble with triggers!! ~ Please Help!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
