Hi,

I have a table with possible references to itself en I use
a trigger on delete to delete those records that refere to
the one that is deleted (all in the same table) The
trigger looks like this:

CREATE TRIGGER TDSDOPDR ON dbo.SDOPDR
FOR DELETE
AS

DECLARE @pk float
DECLARE @fkVENA float

select @fkVENA = fkBHVENA_OPDRref from deleted
select @pk = pkSDOPDRref from deleted

IF @fkVENA > 0
begin
raiserror ('Kan niet verwijderen : de opdracht is reeds
aangerekend/gevorderd.',16,16)
rollback transaction
end

IF not EXISTS (select * from SDOPDR where
(fkSDOPDR_OPDRref_master=@pk or
fkSDOPDR_OPDRref_uitsplVanN0=@pk) and fkBHVENA_OPDRref > 0)
begin
delete from SDOPDR where fkSDOPDR_OPDRref_master=@pk or
fkSDOPDR_OPDRref_uitsplVanN0=@pk
end
else
raiserror ('Kan niet verwijderen : een van de opdrachten
van dit plan is reeds aangerekend/gevorderd.',16,16)


The problem occurs with the last delete statement. The
strange thing is that it deletes what it should delete,
but you get an errormessage
you get when there is nothing to delete!!: row cannot be
located for updating. Some values may have been changed
since it was last read.
you get another errormassage when there is something to
delete : Key column information is insufficient or
incorrect. Too many rows were affected by update.

If I remove the delete statement in the trigger there are
no messages. When it is there it deletes the right things
but you get those annoying messages.

any suggestions?

Arvid

Hi Arvid,

Please try to add the statement SET NOCOUNT ON to the trigger.

When SET NOCOUNT is ON, the count (indicating the number of rows affected
by a Transact-SQL statement) is not returned.

For additional information regarding SET NOCOUNT, please refer to the
following article on SQL Server Books Online:
Topic: "SET NOCOUNT"

Please feel free to let me know if this solves your problem or if you would
like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Thank you, it works great

Arvid


>-----Original Message-----
>Hi Arvid,
>
>Please try to add the statement SET NOCOUNT ON to the
trigger.
>