Trigger is not working correctly with large updates

Posted on 2005-04-18
Medium Priority
Last Modified: 2012-05-05
My trigger is set to run a function every time a record changes in one table, and updates a field in another table based on the returned value based on the inserted record.  This runs fine with I keep the number of entities updated less than say 20,000.  When I run a complete update on all entities the updates are not completed correctly?  Can anyone shed some light on why this update works for smaller batches of updates but fails to work correctly when I run it against the entire tables dataset?
Question by:Nobletucky2004
  • 3
  • 2
  • 2
LVL 26

Expert Comment

ID: 13806324
Could you post the code of your trigger ?

Expert Comment

ID: 13806336
1. Can you please post the DDL?
2. The trigger code is probably incorrect. The key idea in the trigger is to uniquely identify the rows that must be updated, according to some criteria. If this unique identification is incorrect, you may run into errors in one scenario, and other different scenarios these errors will not be there.

Please post DDL or provide more details.



Author Comment

ID: 13806614
           declare @last_entity_id int,  
                  @cnt int,
                  @entity_id int,
                  @attribute_one int,
                  @attribute_two varchar(3),
                  @attribute_three datetime,
                  @attribute_four nvarchar(1000),
                  @rowcount int
            select      @cnt = 0,
                  @last_entity_id= 0

            select @rowcount = count(*) from inserted

            while @cnt < @rowcount
                  select      top 1
                        @entity_id = tester_id,
                        @attribute_one = attr1,
                        @attribute_two = attr2,
                        @attribute_three = attr3
                  from inserted
                  where entity_id > @last_entity_id
                  order by entity_id

                  exec sp_upd_overall_status(@entity_id, @attribute_one , @attribute_two, @attribute_three)

                  select      @cnt = @cnt + 1,
                        @last_entity_id = @entity_id
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 13806779
Where do you actually update the underlying table? In the stored proc sp_upd_overall_status?
This is not good practice. The entire execution of the trigger is performed inside the transaction that updates the original underlying table.

Please post code that has logic that writes into the table.
Also - please include the PK of your underlying table.

Author Comment

ID: 13806982
the underlying table is updated in the stored procedure.  The values that are passed into the stored procedure are used in a function call.  The function all return a single char(1) value which is subsequently used in an update statement against the second table.  The primary key is the entity_id.  I you suggesting that calling a stored procedure from a trigger is in bad form?  I'm also not sure I understand your last statement? The execution of the trigger would also be with the same transaction as teh updates on the underlying table?

Logic for stored proc would look something like:

set @valueX = function call(@entity_id, @attribute_one)

update table 2
  set attrY = @valueX
 entity_id = @entity_id  that is really all that is going on.
LVL 26

Accepted Solution

Hilaire earned 1000 total points
ID: 13807071
first of all why don't you use the function directly to update, thus avoiding the whole loop logic ?

Your trigger's code could be as simple as

update T2
set attr = dbo.yourfunction(I.entity_id, I.tester_id)
from YourTable T2
inner join INSERTED I on T2.entity_id = I.entity_ID

chances are that you could even get rid of the function call to make things faster

Author Comment

ID: 13807244
too much code in function  call, trigger would become unmanagable, but removing proc logic may be a alternative.  I'll give it a try.  tks

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question