CODEWITHSUNDEEP
triggerssql-server-2008-r2sql-update
Jeremy McDevitt
Jeremy McDevitt

Reputation: 115

Unable to update rows individually

I am trying to update our patient table within our system, but due to a connection we have with the state that sends modifications to that table to an external system I'm unable to do mass updates due to several triggers in our system (Microsoft SQL Server 2008 R2). The main piece of code in the trigger that is preventing 

IF (@numrows > 1) 
BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    SELECT @errmsg = OBJECT_NAME(@@PROCID) + ' : more than one row is updated in table Patient'                                 
    RAISERROR(@errmsg,16,21) 
    RETURN
END

I cannot simply shut these triggers off where it would break a lot of things, so to do what would be a simple act of 

update patient set security_level = '2'
where security_level = '1'

I used the following code which used to work in prior versions

declare @tmp_table table(
PRG int identity(1,1) Primary Key,
patient_id int
)

declare @start_value int = 1,
 @finish_value int,
 @patient_id int

Insert Into @tmp_table(patient_id ) Select patient_id  From patient where  security_level = '1'

Select @finish_value = max(PRG) From  @tmp_table

While @start_value <= @finish_value 
Begin
  --now get a key for patient and store in variables
  Select @patient_id =  patient_id 
  From  @tmp_table
  Where PRG = @start_value 

  --.. and now update by key
  Update patient
  set security_level = '2'
  Where patient_id  = @patient_id 


Set @start_value = @start_value  + 1
End

I get the following error upon running that code

Msg 50000, Level 16, State 21, Procedure tU_Patient_HL7, Line 64
tU_Patient_HL7 : more than one row is updated in table Patient

Msg 3609, Level 16, State 1, Line 22
The transaction ended in the trigger. The batch has been aborted.

Any idea how I could tweak this or recode this to update the security level of all patients who have a security level set at 1 and switch it to 2?

Update Would there be any way I could loop 

 Update top (1) patient
 set security_level = '2' 
 where security_level = '1'

Until all rows are affected? That would work as well.

Upvotes: 1

Views: 69

Answers (2)

DBADon
DBADon

Reputation: 579

If you add to your update code a "GO 100", that should do the trick. You may need to put a delay in there as well if it updates too fast for the trigger.

Update top (1) patient
set security_level = '2' 
where security_level = '1'
GO 100

The number of executions (i.e. 100 in my example) is up to you.

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28938

With out full code ,it is hard .I am guessing your update statement is conflicting with below peice of code..

IF (@numrows > 1)

even though you use 

  Update patient
  set security_level = '2'
  Where patient_id  = @patient_id

Your update query may affect more than one row.So my best bet would be to tweak your update query to below or change trigger if you can which is desirable

 Update top (1) patient
  set security_level = '2'
  Where patient_id  = @patient_id

Upvotes: 1

Related Questions