Results 1 to 14 of 14

Thread: Trigger problem

  1. #1
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Unanswered: Trigger problem

    I have a SQL statement that updates a lot of records and a Trigger that fires for updates on the table. The Trigger works fin e when only one record is updated vut generates a Subquery returned more than 1 value error when updating more than one record. What can I do?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Fix the trigger?

    My first guess would be that the trigger is written using a subquery, when it should be using a JOIN instead. Can you post the trigger, or shall we play 20 (or more) questions?

    -PatP

  3. #3
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Microsoft SQL Server > Trigger problem

    The Trigger errors out on the Set @intUid = (Select intUid from Inserted) as shown by the output from tne error text.

    CREATE TRIGGER UPDATE_tblUser
    ON dbo.tblUser
    FOR UPDATE AS
    DECLARE
    @intUid as int
    Set @intUid = (Select intUid from Inserted)

    Server: Msg 512, Level 16, State 1, Procedure UPDATE_tblUser, Line 6
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yep, that it will if more than one row is affected. When a trigger executes, it does so once per triggering statement, not once per triggering row. If the statement that launched the trigger affected 1000 rows, then there would be 1000 rows in the inserted view when the trigger executes.

    Do you want to post the rest of the trigger so we can try to help you fix it, or is the score now one question down, N to go?

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nevermind, I found it in your other thread on this topic. Try:
    Code:
    CREATE TRIGGER UPDATE_tblUser ON tblUser FOR UPDATE AS
    
    UPDATE a
       SET 
          intEmp = i.intEmp
    ,     vchrCompID = i.vchrCompID
    ,     vchrPwd = i.vchrPwd
    ,     vchrLogin = i.vchrLogin
    ,     vchrFirstName = i.vchrFirstName
    ,     vchrLastName = i.vchrLastName
    ,     vchrEmailAddress = i.vchrEmailAddress
    ,     bitActive = i.bitActive
    ,     intPWDAttempt = i.intPWDAttempt
    ,     vchrCreatedWho = i.vchrCreatedWho
    ,     dtmCreatedDate = i.dtmCreatedDate
    ,     vchrModifiedWho = i.vchrModifiedWho
    ,     dtmModifiedDate = i.dtmModifiedDate
       FROM deleted AS d
       JOIN inserted AS i ON (i.intUid = d.intUid)
       JOIN [dbPortal].[dbo].tblUser AS a ON (a.intUid = d.intUID)
       WHERE  'sam' = d.vharComdID
    
    END
    -PatP

  6. #6
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Trigger Problem

    Seems to be working now. At least not erroring out. Now I have to test.

  7. #7
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Trigger Problem

    I have discovered other Triggers with simular problem but the Triggers ard different.

    CREATE TRIGGER DELETE_Skids
    ON Skids
    FOR DELETE AS
    DECLARE
    @CompanyID as varchar(20),
    @SkidID as int
    SET @CompanyID=(Select CompIDfrom deleted)
    SET @SkidID = (Select intSkidID from deleted)
    IF @CompanyID= 'NewCompany'
    BEGIN
    delete [dbSkids].[dbo].Skids where RolID = @SkidID
    END

    This is for deletes and needs the same but differtent solution. I have been trying differnet things after the where .

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In this case, you need to remove the trigger completely (make it go away), and replace it with ON DELETE CASCADE.

    -PatP

  9. #9
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Trigger Problem

    But Skids is a different database

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ooops, my bad! Then I'd use:
    Code:
    CREATE TRIGGER DELETE_Skids ON Skids FOR DELETE AS
    
    DELETE FROM s
       FROM deleted AS d
       JOIN [dbSkids].[dbo].Skids AS s
          ON (s.RolID = d.intSkidID)
       WHERE 'NewCompany' = d.CompID
    
    RETURN
    -PatP

  11. #11
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    New Trigger Problem

    Thanks for the help on the other trigger

    I have trigger with a different configuration. This one starts out with
    set @pwd = (select vchrPwd from inserted) as usual
    which of course creats Subquery returned more than 1 value.
    I changed every occurance of @pwd to
    SELECT vchrPwd from inserted as i join tblUser h on i.intUid = h.intUid
    which I was hoping would work but it doesn't. Any ideas?

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It actually does work in a perverse sort of way, but it would return multiple values where a scalar was required, which would cause SQL Server to object rather strongly!

    The problem with all of your triggers that you've posted so far is that they were written with the idea of processing only one row at a time. In other words, they make the assumption that the inserted and/or deleted tables will have only one row in them. This is obviously a bad assumption based on the problems that you are having.

    The code seems to be realatively well written, but with the wrong mind-set (thinking in rows instead of in sets). You probably need to revisit every one of your triggers, and think about the effects of the code when more than one row was affected in the underlying table (meaning that there may be many rows in inserted and deleted). You probably also need to think about the kind of chaos that can be caused by changes to the primary key, which would cause rows in inserted to fail to match rows in deleted!

    I don't know of a "one size fits all" solution for your problem. The only way that I know to fix it is to manually review all of the trigger code.

    -PatP

  13. #13
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Next Step

    Thank you. Very good overservation which leaves me the task of recoding the Triggers.

    I have another Thread running discussing a Trigger where I am doing just that. An unusual application of a data verification Trigger.

    By the way the advise I have received here has been very usefull in this endevor.

  14. #14
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Thumbs up Solution Found

    I found the solution yesterday. I someting like this:

    set @count = @@rowcount
    while (@count <= @@rowcount)
    begin
    select dbo.fdecrypt(vchrPwd) from inserted
    set @count = @count + 1
    end

    I check each row one row at a time.

Posting Permissions

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