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

Error with the trigger

I made a trigger for delete just like this.

"
CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]
FOR DELETE
AS

Declare @.severity int,
@.IdNmbr nvarchar(10)
Set @.Severity = 0

Declare NoId Cursor Local Static for
Select [Promo_Id] from deleted
Open NoId
While 1=1
Begin
Fetch NoId Into
@.IdNmbr

If @.@.Fetch_Status <> 0
Break

If Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@.IdNmbr)
Set @.Severity = @.Severity+1
Else
Begin
Delete From MbPromo_hdr where [Promo_Id]=@.IdNmbr
End
End
Close NoId
Deallocate NoId

If @.Severity = 0
Commit
Else
Begin
RollBack
Print 'Data Cannot Be delete'
End
Go
"

When i delete the record from Enterprise manager it give me an error

"Another user has modified the content of this table or view. The database
row you are modifying no longer exists in the database."

Why? And it happen with all of the record at my table

--
Message posted via http://www.sqlmonster.comAvoid cursors at any time, buit especially in triggers.

I don't understand the purpose of the DELETE statement here. This is an
AFTER trigger so the row has already been deleted. Also, why are you
using a trigger to check for dependent rows? It seems like you could do
that more easily with a foreign key.

If Promo_id is unique in MbPromo_hdr then your trigger could be
rewritten as:

CREATE TRIGGER MbPromoHdrDel ON dbo.MbPromo_hdr FOR DELETE
AS
IF EXISTS
(SELECT *
FROM deleted AS D
JOIN mbpromo_dtl AS M
ON D.promo_id = M.promo_id)
BEGIN
ROLLBACK TRAN
RAISERROR('Data cannot be deleted',16,1)
END

but a foreign key would be a much better solution.

--
David Portas
SQL Server MVP
--|||Michael Teja via SQLMonster.com (forum@.SQLMonster.com) writes:
> CREATE TRIGGER [MbPromoHdrDel] ON [dbo].[MbPromo_hdr]
> FOR DELETE
> AS
> Declare @.severity int,
> @.IdNmbr nvarchar(10)
> Set @.Severity = 0
>
> Declare NoId Cursor Local Static for
> Select [Promo_Id] from deleted
> Open NoId
> While 1=1
> Begin
> Fetch NoId Into
> @.IdNmbr
> If @.@.Fetch_Status <> 0
> Break
> If Exists (Select 1 from MbPromo_dtl where [Promo_Id]=@.IdNmbr)
> Set @.Severity = @.Severity+1
> Else
> Begin
> Delete From MbPromo_hdr where [Promo_Id]=@.IdNmbr
> End
> End

In addition to David's comments: this would make a little more sense,
if you had an INSTEAD OF trigger. Rather than having an AFTER trigger
that rolls back a large DELETE in case of error, an INSTEAD OF trigger
can check for conditions before hand, but must then also carry out the
original action.

However, there is still no reason to do this one-by-one, and referential
constraints are better to use for this.

> If @.Severity = 0
> Commit

And this is something you should not do in a trigger! If you commit within
a trigger (and the trigger does not have a matching BEGIN TRANSACTION),
you create an error situation which causes the terminattion of the batch
when the trigger exits, so subsequent statments are not executed. In
SQL 2000 there is no error message actually printed, it all happens
internally in the server. SQL 2005 will give an error message for this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanx for the help.

It work.

--
Message posted via http://www.sqlmonster.com