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_datesince 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
