Friday, March 9, 2012

error with trigger

Hi ,
I have created a trigger as follows :
========================================
=========================
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER trigger Usr_Trig_GL53
ON Gl530106
FOR INSERT , UPDATE
NOT FOR REPLICATION
AS
IF UPDATE(GL53001) OR UPDATE(GL53003) or UPDATE(GL53004)
-- this is an update
BEGIN
UPDATE b
SET b.[GL53001] = a.[GL53001] , b.[GL53003] = a.[GL53003] , b.[GL53004] = a.
[GL53004]
FROM inserted as a INNER JOIN ALTIRISSERVER.ScalaDB_SGO_TEST.dbo.Gl530106
as b ON a.GL53001 = b.GL53001
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================
=========================
however when i made some modifications i was prompted the follow error
message
"another user has modified the contents of this table or view ; the database
row you are modifying no longer exists in the database .
Database Error : [Microsoft][ODBC Server Driver][SQL Server][OLD/DB provider
returned Message : New transaction cannot enlist in the specified transactio
n
coordinator ]
[Microsoft][ODBC Server Driver][SQL Server][ The operation cannot be
performed because the OLD DB Provider 'SQLOLEDB' was unable to begin a
distributed transaction "
Is this due to any services that not running ? --> i have started the SQL
Server Agent and the DTC from SQL EM
i am using SQL Standard Edition sp3 but this test wqas carried out from my
machine which is installed with MSDE2000
appreciate ur advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1Hi,
I guess there is no primary key on the table whereas SQL Server could
decide what row to update there, so put a primary key on the table you
want to access / update and that should be fine. If you want to change
a view on the remote server, make sure that the view is updateable,
otherwise the row cannot be changed either.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi,
I guess there is no primary key on the table whereas SQL Server could
decide what row to update there, so put a primary key on the table you
want to access / update and that should be fine. If you want to change
a view on the remote server, make sure that the view is updateable,
otherwise the row cannot be changed either.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi ,
I have added PK to both the source & destination table but i still have the
same error
appreciate ur advice
tks & rdgs
Jens wrote:
>Hi,
>I guess there is no primary key on the table whereas SQL Server could
>decide what row to update there, so put a primary key on the table you
>want to access / update and that should be fine. If you want to change
>a view on the remote server, make sure that the view is updateable,
>otherwise the row cannot be changed either.
>HTH, Jens Suessmeyer.
>--
>http://www.sqlserver2005.de
>--
Message posted via http://www.webservertalk.com

No comments:

Post a Comment