Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Problem: Trigger and multiple updates to the table

    Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

    UPDATE
    table1
    SET
    col1 = 0
    WHERE col2 between 10 and 20

    Error I am getting is :

    Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

    What is the best possible way to make it work? Thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?

    CREATE trigger t_thickupdate on dbo.sched_lot_hdr
    for update
    as

    -- declare local variables
    declare
    @lotno int,
    @charthick varchar(10),
    @decthick decimal(6,5)

    -- populate local variables
    set @lotno = (select lotno from inserted)
    set @charthick = (select matcharthick from inserted)
    set @decthick = (select matthick from inserted)

    -- determine which was updated: character thickness or decimal thickness
    if update(matthick)

    -- decimal thickness was updated
    begin
    set @charthick = (select thkfrac from prod_thk_stds where thkdec = @decthick)
    update sched_lot_hdr set matcharthick = @charthick where lotno = @lotno
    end
    else

    -- character thickness was updated
    begin
    set @decthick = (select thkdec from prod_thk_stds where thkfrac = @charthick)
    update sched_lot_hdr set matthick = @decthick where lotno = @lotno
    end

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    A trigger act on 1 or more rows...depending on the DML


    inserted and/or deleted can have more than 1 row...

    You need to al;ter your thinking and think more set based....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Also looking at it...

    Your updating the same table in the trigger that is causing the trigger action because of an update.....that doesn't make sense....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest using:
    PHP Code:
    CREATE TRIGGER t_thickupdate
       ON dbo
    .sched_lot_hdr 
       
    FOR UPDATE
    AS

    -- 
    determine which was updatedcharacter thickness or decimal thickness
    IF Update(matthick)    -- decimal thickness was updated
       UPDATE sched_lot_hdr
          SET matcharthick 
    s.thkfrac
          FROM inserted 
    AS i
          JOIN sched_lot_hdr
             ON 
    (sched_lot_hdr.lotno i.lotno)
          
    JOIN prod_thk_stds AS s
             ON 
    (s.thkdec i.matthick)
       ELSE            -- 
    character thickness was updated
          update sched_lot_hdr
             set matthick 
    s.thkdec
             FROM inserted 
    AS i
             JOIN prod_thk_stds 
    AS s
                ON 
    (s.thkfrac i.matcharthick)
             
    JOIN sched_lot_hdr
                ON 
    (sched_lot_hdr.lotno i.lotno)

    RETURN 
    Note that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!

    -PatP

  7. #7
    Join Date
    Apr 2004
    Posts
    3
    yeah

    This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.

    Update: thats fast. Thanks Pat, I will test it.
    Last edited by elearner; 04-21-04 at 13:57.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    A trigger act on 1 or more rows...depending on the DML


    inserted and/or deleted can have more than 1 row...

    You need to al;ter your thinking and think more set based....
    Yea vous! I'm with you on that!
    Originally posted by Brett Kaiser
    Also looking at it...

    Your updating the same table in the trigger that is causing the trigger action because of an update.....that doesn't make sense....
    Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •