One Of The Reasons I Participate In Forums And Newsgroups

This is one of the reasons I participate in SQL Server newsgroups and forums

A person had the following update statement

Update Object_data
set External_Claim_Number = BWCClaimNum
From yson_Claims_eDocs_10_27_2006
where LastName = INJ_lname
and FirstName = INJ_fname
and SSN = inj_ssn
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
and convert(varchar,pkclaim) = internal_claim_Number
and BWCClaimNum is not null
and BWCClaimNum <> External_Claim_Number

On the table that got updated (Object_data) the following trigger was created

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS

declare @rows int
declare @object_Data_ID int
set @rows = @@ROWCOUNT
if @rows = 0
    return
if @rows = 1
    begin
    select @object_Data_Id = object_data_id
    from inserted
    goto singlerecord
    end
declare curObject insensitive cursor for
    select object_data_id
    from inserted
open     curObject
fetch next from curObject into @object_data_id
while (@@fetch_status <> -1)
    begin
    singlerecord:
    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted where object_data_id = @object_data_id
    if @rows = 1
        return
   
    fetch next from curObject into @object_data_id
    end
close curObject
deallocate curObject

I suggested that the person did not need the cursor since he is inserting everything anyway into the object_datahist table
I decided to modify the trigger as follows

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS
IF @@ROWCOUNT =0
RETURN

    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted

I also suggested modifying the following line from the update query
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
to
and injurydate = claim_injury_date
since the dates are the same anyway


This is the response I got

"I tested removing the cursor driven trigger from the Object_Data table in development and replaced it with the set based up date you provided. Here is what happened:

20,784 records in the test.

1. Using the current trigger I canceled the update after an hour and eleven minutes of running. It had updated 10,218 rows.
2. Using the set based trigger it ran in eleven seconds and generated the correct amount of history.

That's really something to me! Thanks again!"


You see, this is one of the reasons I participate in forums/newsgroups because it feels good to help other people. Another reason is that you can look at the responses from the SQL MVP's and see how they would handle certain situations. I remember running into problems once and I also remembered that I saw this same problem in a newsgroup. after that it's a quick Google and you find your answer