December 14, 2005 at 11:28 am
I have Triggers defined on Insert, Delete, and Update on table "tbl_CATSMenu" with SET NOCOUNT ON for all of them. However, when I execute a DELETE statement that only affects 1 row, I get:
(1 row(s) affected)
(4 row(s) affected)
(4 row(s) affected)
(7 row(s) affected)
I can't figure out where are the 4, 4, and 7 rows affected are coming from. Does anyone know why NOCOUNT isn't working? Here is my DELETE Trigger:
ALTER TRIGGER trtbl_CATSMenu_D
ON tbl_CATSMenu
FOR DELETE
AS
-- Suppress message about number of records affected.
SET NOCOUNT ON
UPDATE tbl_CATSMenu
SET IsFolder = 0, ImageMin = 'reportSmall', ImageMax = 'reportLarge'
WHERE [ID] IN (SELECT DISTINCT ParentID FROM deleted)
-- No more child record
AND NOT EXISTS (SELECT 1
FROM tbl_CATSMenu C
WHERE C.ParentID = tbl_CATSMenu.[ID]
)
December 14, 2005 at 1:56 pm
Are you watching execution plan?
_____________
Code for TallyGenerator
December 14, 2005 at 4:10 pm
Could there be more triggers than your three ?
select T.name as TableName
, TR.name as TriggerName
from sysobjects as T
join sysobjects as TR
on T.id = TR.parent_obj
where TR.type ='TR'
and T.type = 'U'
and T.name = 'tbl_CATSMenu'
SQL = Scarcely Qualifies as a Language
December 15, 2005 at 3:06 am
Are these messages coming from the DELETE operation itself rather than the trigger. What happens if you SET NOCOUNT ON in the QA environment and then delete records?
I guess if these were INSTEAD OF triggers rather than FOR that then you might get no messages.
David
If it ain't broke, don't fix it...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply