views:

21

answers:

2

My ERP Vendor has the following trigger on a table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[SOItem_DeleteCheck]
ON [dbo].[soitem]
FOR DELETE
AS 
BEGIN
    DECLARE @RecCnt int, @LogInfo varchar(256)

    SET @RecCnt = (SELECT COUNT(*) FROM deleted)

    IF @RecCnt > 150
    BEGIN
        RAISERROR (54010, 18, 1, 'SOItem') WITH LOG
        ROLLBACK TRANSACTION
    END

    SET @LogInfo = 'Deleting ' + LTRIM(STR(@RecCnt)) + ' Rows From SOItem'

    EXEC LogDeletes @LogInfo
END
GO

This seems very inefficient to me. Doesn't select count(*) take longer than Count(specific field)?

+1  A: 

Honestly even if is is slower, I can run a select stament like that in less than a millisecond on my largest table that has millions of rows which this trigger is unlikely to hit. There is no real performance gain from changing it. I'm curious as to why you would want to rollback any transaction with more than 150 records thoug.

HLGEM
I'm tying to figure that out myself. So, I altered the trigger to be >3 and deleted a sales order with 4 line items but nothing happened. I profile traced the behavior and my ERP deletes each record individually.
DavidStein
When I tried the following statement which would delete 4 records:delete from soitem where fsono = '300604'I get the following error: SQL Server Database Error: Error 54010, severity 18, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.Any ideas?
DavidStein
That's the error in the trigger, look at the code you posted.
HLGEM
+1  A: 

I think in the past there was a benefit to count(1) vs count(*), and we were all taught to use that approach, but at this point it's more about style than performance.

cmsjr