Trigger is not working correctly with large updates

Posted on 2005-04-18
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
    LVL 26

    Expert Comment

    Could you post the code of your trigger ?
    LVL 5

    Expert Comment

    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

               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
    LVL 5

    Expert Comment

    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

    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

    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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how the fundamental information of how to create a table.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now