Re: Having Trouble with triggers!! ~ Please Help!

From: JoseR (anonymous_at_discussions.microsoft.com)
Date: 02/04/04 

Date: Wed, 4 Feb 2004 11:21:52 -0800

Hello Jim,

I tried your sample, and it works interactively (Query
Analyzer), but if I attempt to run the same code through
a trigger, it hangs.

Any thoughts?

Jose R.

>-----Original Message-----
>Try changing your trigger.
>
>A trigger fires once per statement, not
>once per row. Thus, if you insert more than one row,
only one
>will be replicated to the other table. This is how the
trigger
>should read (completely disregarding the problem with
the linked
>server for the moment, since I don't know what your
problem is there.)
>
>
> INSERT RemoteServer.dbname.dbo.tablename(srecid)
> SELECT srecid
> FROM inserted i
> WHERE NOT EXISTS (SELECT *
> FROM
RemoteServer.dbname.dbo.tablename a
> WHERE a.srecid = i.srecid)
>
>V/R Jim Johnston
>MCSD.NET, MCDBA
>
>"JR_DBA" <anonymous@discussions.microsoft.com> wrote in
message news:<8df501c3ea6b$51d5c430$a301280a@phx.gbl>...
>> 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
>.
>

Relevant Pages

  • SQL to Oracle update trigger fails due to distributed transaction error 7391
    ... I am having a hard time creating a Trigger to update an Oracle ... I am using a SQL Server 2005 Express database on a Win XP Pro ...
    (comp.databases.ms-sqlserver)
  • Re: Trigger, alternative way to pass variable to trigger
    ... You don't have to execute the setUser command in the master database, ... create trigger trigger name ... to call stored procedure or execute sql commands and let it handle it the ... Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • Trigger Timeout Loop Issue
    ... In our database code, we have an AFTER trigger on a specific ... since the record is locked by the trigger, the stored procedure ... processing will the stored procedure (and thus the web service) ...
    (comp.databases.oracle.server)
  • RE: Calling stored procs on AS/400
    ... OK, trigger sounds good. ... > Generally we do not support executing remote stored procedures if linked ... > server is non-SQL Server. ... > Thus the trigger can fire the stored procedure. ...
    (microsoft.public.sqlserver.server)
  • Re: Question on Triggers
    ... If the trigger fires, it can either execute code directly or it can execute a stored procedure. ... Cross database does have security issues, but Erland Sommarskog's article discusses all the ways that you can get this to work, with the pros and cons of each. ...
    (microsoft.public.sqlserver.programming)
Loading