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