locked
Trigger error on update RRS feed

  • Question

  • Hi all,

    Can anyone tell me why this query is failing with the error message. I am expecting the update query to update RowNum in 10 rows. As far as I am aware an update query can update more than one row.
    This has been driving me insane for an hour now. I can write and execute this type of qery in my sleep and always get a successful execution but the damn thing wont work now!! Am I losing the plot here?


    update Events set RowNum = 301 where FileName = 'i2wHrswq' and Tag = 0


    Error:
    Msg 512, Level 16, State 1, Procedure EventInserter, Line 17

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    • Edited by sean_mufc Thursday, February 19, 2009 8:48 AM
    Wednesday, February 18, 2009 1:00 PM

Answers

  • TEST TEST TEST TEST TEST this :)
    USE mapping  
     
    ALTER TRIGGER dbo.eventinserter  
      ON dbo.events  
      AFTER UPDATEINSERT 
    AS 
      BEGIN 
     
        SET NOCOUNT ON 
     
          --Holder table for eventid identity values  
          DECLARE @new_event_ids table (  
             eventid        int 
           , championshipid int 
           , eventdate      datetime  
          )  
     
          --Updates  
          UPDATE sports.dbo.events  
          SET    isenabled = i.isenabled  
          FROM   sports.dbo.events As e  
           INNER 
            JOIN inserted As i  
              ON e.eventid = i.eventid  
     
          --Inserts  
          INSERT INTO sports.dbo.events (eventtype, championshipid, isenabled, status, eventdate, name, hasstats, minbettreble, disporder)  
          OUTPUT inserted.eventid  
               , inserted.championshipid  
               , inserted.eventdate  
          INTO   @new_event_ids (eventid, championshipid, eventdate)  
          SELECT '0' As eventtype  
               , i.championshipid  
               , i.isenabled  
               , 'A' As status  
               , i.eventdate  
               , i.name 
               , 0 As hasstats  
               , i.minbettreble --Is this field in mapping.dbo.events? Unclear as @MinBetTreble is never declared...  
               , 0 As disporder  
          FROM   (  
                  SELECT *  
                  FROM   inserted  
                  WHERE  categoryid IS NOT NULL 
                  AND    championshipid IS NOT NULL 
                  AND    isenabled IS NOT NULL 
                  AND    typeid IS NOT NULL 
                  AND    name IS NOT NULL 
                  AND    filename IS NOT NULL 
                  AND    tag IS NOT NULL 
                 ) As i  
           LEFT 
            JOIN sports.dbo.events As e  
              ON i.eventid = e.eventid --Wondering if we should swap this logic to be on championshipid and eventdate..?  
          WHERE  e.eventid IS NULL 
     
          --Update mapping with new eventid  
          UPDATE dbo.events  
          SET    eventid = x.eventid  
          FROM   dbo.events As e  
           INNER 
            JOIN @new_event_ids As x  
              ON e.championshipid = x.championshipid  
             AND e.eventdate = x.eventdate  
     
        SET NOCOUNT OFF 
     
      END 
    GO 
    Let us know how you get on :)
    George
    • Marked as answer by sean_mufc Monday, February 23, 2009 11:52 AM
    Thursday, February 19, 2009 10:46 AM
    Answerer

All replies

  • The error is not caused by your statement, it's caused by a trigger on the Events table called "EventInserter". The trigger is poorly written and will only work when updating one value at a time. Post the trigger code and we'll help you make it work properly :)
    George
    Wednesday, February 18, 2009 1:32 PM
    Answerer
  • Hi,


    Thanks for the reply. Trigger code is below.

    The table (and database) that this trigger runs on is a sort of mapping database, where values are imported one by one from a web service (don't ask why, because I have no idea, I didnt design it). When all required values are present, the record is imported into the the live database.

    Like you say this trigger works fine with one row, but when mutiple rows are updated it fails. The query in my original post is fired from another trigger that runs on the Championship table, where a Championship can have many events. The fileName and tag fields are a composite key that link the Championship Id to the Event.

    As you can probably tell the database i'm trying to map from is poorly structured to say the least. The update query in the previous post should be::

    update Events set ChampionshipId= 301 where FileName = 'i2wHrswq' and Tag = 0


    ALTER TRIGGER [dbo].[EventInserter]  
       ON  [dbo].[Events]  
       after updateinsert 
    AS   
    begin     
        declare @CanInsert bit, @IsInserted bit, @IsEnabled bit 
        declare @ExtEventId bigint, @ChampionshipId bigint, @EventId bigint, @MarketId bigint 
        declare @CategoryId int, @TypeId int, @Tag int 
        declare @EventDate datetime   
        declare @Name varchar(50), @FileName varchar(50)  
          
        select @IsInserted = IsInserted, @ExtEventId = ExtEventId, @ChampionshipId = ChampionshipId    
        from inserted  
          
        if @ChampionshipId is null 
            begin     
                set @ChampionshipId = (select ChampionshipId from Championships where "FileName" = @FileName and Tag = @Tag)  
            end 
          
        if @IsInserted = 1  
            begin 
                select @EventId = EventId, @IsEnabled = IsEnabled, @ExtEventId = ExtEventId from inserted  
                  
                update Sports.dbo.Events set IsEnabled = @IsEnabled where EventId = @EventId      
            end 
        else 
            begin             
                select @CategoryId = CategoryId, @ChampionshipId = ChampionshipId,   
                    @EventId = EventId, @EventDate = EventDate, @IsEnabled = IsEnabled, @TypeId = TypeId,   
                    @Name = Name, @FileName = "FileName", @Tag = Tag from inserted                        
                      
                 if  @CategoryId is not null and @ChampionshipId is not null and  @EventDate is not null and   
                     @IsEnabled is not null and @TypeId is not null and @Name is not null and   
                     @FileName is not null and @Tag is not null and 
                     @CategoryId in (select categoryId from Codes)  
                    begin 
                        insert into Sports.dbo.Events (EventType, ChampionshipId, IsEnabled, "Status", EventDate, Name, HasStats, MinBetTreble, DispOrder)  
                        values ('O', @ChampionshipId, @IsEnabled, 'A', @EventDate, @Name, 0, @MinBetTreble, 0)  
                          
                        set @EventId = (select EventId from Sports.dbo.Events   
                        where ChampionshipId = @ChampionshipId and  EventDate = @EventDate)                   
          
                        update Events set EventId = @EventId, IsInserted = 1   
                        where ChampionshipId = @ChampionshipId and EventDate = @EventDate     
                    end                                
            end 
    end 

    Wednesday, February 18, 2009 2:02 PM
  • There seems to be a lot of code in that trigger that doesn't do very much... E.g. @tagid is populated and checked whether it has a value, but then not used. Is this part necessary? Same applies to a number of different variables.

    Is sports.dbo.events different to events (i.e. are we going across database)? Based on what you've described above I assume so but want to be sure.

    Because the code is quite obfuscated, it's proving a little difficult to dissect everything that is necessary to the process - any chance you know what is supposed to be going on exactly? I know you've explained  the outline above, but it's more the nitty gritty that we are interested in... I could write you my best guess, but I don't want to hand something over that's half-baked that doesn't cover all the necessary bases.
    George
    Wednesday, February 18, 2009 3:17 PM
    Answerer
  •  Hi George,

    Thanks for the reply.

    We are going across DB's, so you can assume Events = Mapping.dbo.Events

    The "obfuscation" of the trigger is due to the frankly terrible deisgn of the database i'm mapping from.

    For example, the Events table, which has a N-1 relationship with the Championships table, but it is not linked through a FK to the ChampionshipId as per normal practice, but through a composite of the FileName and Tag fields (I know, makes no sense), which is why the FileName and Tag is used only to try to retireive the ChampionshipId from Championships table, so that a proper FK constraint can be used in the new database.

    I'm also stuck with a web service that sends fragmented records (i.e. some but not all the fields of a record), so I need to piece the records together in this nullable mapping table, before importing complete records into the new database tables (i.e. the Sports.dbo.<table_name>) using numerous triggers similar to this one.

    I have a look at the trigger and i'm fairly certain everything is needed here, as at any given time any number or all of the fields could be NULL, which is why there is a need to check every required field to ensure that it has a value before importing into new database table

    I know it's a bit of a mess but this is what i'm stuck with. You help is greatly appreciated.
    Wednesday, February 18, 2009 4:10 PM
  • Sean Mc said:

    We are going across DB's, so you can assume Events = Mapping.dbo.Events


    I find that statement very contradictory. For final clarification: is the trigger created on mapping.dbo.events or another_db.dbo.events?
    George
    Wednesday, February 18, 2009 4:51 PM
    Answerer
  • apologies.

    the trigger is on Mapping.dbo.Events, and the table on which the trigger populates when all required fields in Mapping.dbo.Events are not null is on Sports.dbo.Events
    • Proposed as answer by gveeEditor Wednesday, February 18, 2009 5:37 PM
    • Unproposed as answer by gveeEditor Wednesday, February 18, 2009 5:38 PM
    Wednesday, February 18, 2009 4:57 PM
  • Just had a step through your code and added comments - can you give them a read and make sure we're on the same wave-length

    Assuming I've understood everything so far you'll be happy to hear that the new trigger(s) are easy enough to write up :)

    ALTER TRIGGER [dbo].[EventInserter]     
       ON  [dbo].[Events]     
       after updateinsert    
    AS      
    begin        
        declare @CanInsert bit, @IsInserted bit, @IsEnabled bit    
        declare @ExtEventId bigint, @ChampionshipId bigint, @EventId bigint, @MarketId bigint    
        declare @CategoryId int, @TypeId int, @Tag int    
        declare @EventDate datetime      
        declare @Name varchar(50), @FileName varchar(50)     
     
        --This is fine if we only ever access one result.  
        --  While it will not fail when there are more than one record affected (I'd argue that it should (read SET vs SELECT), it will not give the correct result  
        --  Think of if this way - we insert two records with championshipid 1 and 2 - what value will @championshipid take?  
        select @IsInserted = IsInserted  
             , @ExtEventId = ExtEventId  
             , @ChampionshipId = ChampionshipId       
        from   inserted     
     
        --Redundant as the select statement will never return a result as @filename and @tag are NULL at this point  
        --  i.e. if @championshipid is null, then set it to null ;)  
        if @ChampionshipId is null    
            begin        
                set @ChampionshipId = (select ChampionshipId from Championships where "FileName" = @FileName and Tag = @Tag)     
            end    
             
        --Not convinved this field is required...  
        --  Surely we know what has been inserted because we can INNER JOIN inserted with the destination table.  
        --  What are the common fields? To me it looks like mapping.dbo.events.eventid = sports.dbo.events.eventid  
        if @IsInserted = 1     
            begin    
                select @EventId = EventId, @IsEnabled = IsEnabled, @ExtEventId = ExtEventId from inserted     
                --This update only relies on data from inserted (i.e. mapping.dbo.events)  
                update Sports.dbo.Events set IsEnabled = @IsEnabled where EventId = @EventId         
            end    
        --So instead of splitting inserts and updates in to separate triggers, the developer has opted for a conditional split  
        --  There isnt anything wrong with this per-sé, but it may make more sense (i.e. easier to maintain) if the logic was split  
        --  This is your call; there is no real difference in terms of difficulty in writing the thing  
        else    
            begin                
                -- @filename and @tag are finally initiallised (assuming not null in inserted)!  
                select @CategoryId = CategoryId, @ChampionshipId = ChampionshipId,      
                    @EventId = EventId, @EventDate = EventDate, @IsEnabled = IsEnabled, @TypeId = TypeId,      
                    @Name = Name, @FileName = "FileName", @Tag = Tag from inserted                           
     
                 --You have covered this logic in your explanation, so I'm happy with this bit  
                 --  We will just move it to the WHERE clause instead of a conditional IF  
                 if  @CategoryId is not null and @ChampionshipId is not null and  @EventDate is not null and      
                     @IsEnabled is not null and @TypeId is not null and @Name is not null and      
                     @FileName is not null and @Tag is not null and    
                     --And this will become an INNER JOIN to the codes table instead of an IN operator  
                     @CategoryId in (select categoryId from Codes)     
                    begin    
                        insert into Sports.dbo.Events (EventType, ChampionshipId, IsEnabled, "Status", EventDate, Name, HasStats, MinBetTreble, DispOrder)     
                        values ('O', @ChampionshipId, @IsEnabled, 'A', @EventDate, @Name, 0, @MinBetTreble, 0)     
     
                        --To confirm, the purpose of the next two lines is to update the mapping.dbo.events table with the new eventid from   
                        --  sports.dbo.events (which I also assume is an identity field)?  
                        set @EventId = (select EventId from Sports.dbo.Events      
                        where ChampionshipId = @ChampionshipId and  EventDate = @EventDate)                      
             
                        update Events set EventId = @EventId, IsInserted = 1      
                        where ChampionshipId = @ChampionshipId and EventDate = @EventDate        
                    end                                   
            end    
    end   

    George
    Wednesday, February 18, 2009 5:39 PM
    Answerer
  • Yea thats pretty much the situation i'm in at the minute.

    "--  What are the common fields? To me it looks like mapping.dbo.events.eventid = sports.dbo.events.eventid"

    Correct

    "--To confirm, the purpose of the next two lines is to update the mapping.dbo.events table with the new eventid from   
    -- sports.dbo.events (which I also assume is an identity field)? "

    Correct

    "--You have covered this logic in your explanation, so I'm happy with this bit  
    --  We will just move it to the WHERE clause instead of a conditional IF "

    So you're suggesting     insert into(...) select ... where.... - how would I know the success of this insert, and whether to query Sports.dbo.Events for the new EventId (the Identity field)


    Apart from the structural/best practice problems with the trigger, how do I get over the multiple insert/update problem? Use a cursor over inserted at the beginning of the trigger and encapsulate all the remaining logic within the fetch next.... ?

    Wednesday, February 18, 2009 6:03 PM
  • TEST TEST TEST TEST TEST this :)
    USE mapping  
     
    ALTER TRIGGER dbo.eventinserter  
      ON dbo.events  
      AFTER UPDATEINSERT 
    AS 
      BEGIN 
     
        SET NOCOUNT ON 
     
          --Holder table for eventid identity values  
          DECLARE @new_event_ids table (  
             eventid        int 
           , championshipid int 
           , eventdate      datetime  
          )  
     
          --Updates  
          UPDATE sports.dbo.events  
          SET    isenabled = i.isenabled  
          FROM   sports.dbo.events As e  
           INNER 
            JOIN inserted As i  
              ON e.eventid = i.eventid  
     
          --Inserts  
          INSERT INTO sports.dbo.events (eventtype, championshipid, isenabled, status, eventdate, name, hasstats, minbettreble, disporder)  
          OUTPUT inserted.eventid  
               , inserted.championshipid  
               , inserted.eventdate  
          INTO   @new_event_ids (eventid, championshipid, eventdate)  
          SELECT '0' As eventtype  
               , i.championshipid  
               , i.isenabled  
               , 'A' As status  
               , i.eventdate  
               , i.name 
               , 0 As hasstats  
               , i.minbettreble --Is this field in mapping.dbo.events? Unclear as @MinBetTreble is never declared...  
               , 0 As disporder  
          FROM   (  
                  SELECT *  
                  FROM   inserted  
                  WHERE  categoryid IS NOT NULL 
                  AND    championshipid IS NOT NULL 
                  AND    isenabled IS NOT NULL 
                  AND    typeid IS NOT NULL 
                  AND    name IS NOT NULL 
                  AND    filename IS NOT NULL 
                  AND    tag IS NOT NULL 
                 ) As i  
           LEFT 
            JOIN sports.dbo.events As e  
              ON i.eventid = e.eventid --Wondering if we should swap this logic to be on championshipid and eventdate..?  
          WHERE  e.eventid IS NULL 
     
          --Update mapping with new eventid  
          UPDATE dbo.events  
          SET    eventid = x.eventid  
          FROM   dbo.events As e  
           INNER 
            JOIN @new_event_ids As x  
              ON e.championshipid = x.championshipid  
             AND e.eventdate = x.eventdate  
     
        SET NOCOUNT OFF 
     
      END 
    GO 
    Let us know how you get on :)
    George
    • Marked as answer by sean_mufc Monday, February 23, 2009 11:52 AM
    Thursday, February 19, 2009 10:46 AM
    Answerer
  • Hi Sean,

    I'm really interested to know how you got on with this - does the trigger I wrote work in the desired manner?
    George
    Monday, February 23, 2009 8:44 AM
    Answerer
  • Hi Geroge,

    Sorry I haven't had a chance to reply, i'm totally snowed under at the minute.

    You trigger works great and without any modifications at all. It's a hell of a lot more elegant and efficient than my version, as im sure you know. I have another 6 triggers that are doing pretty much the same thing, and im going to re-write them using your trigger as a template.

    Thanks for all the assistance.
    Monday, February 23, 2009 11:51 AM
  • No need to apologise, I was just eager to know how we'd got on - it was a fun little challenge :)
    Glad I could help
    George
    Monday, February 23, 2009 12:38 PM
    Answerer