CODEWITHSUNDEEP
sqlsql-servert-sql
user1292656
user1292656

Reputation: 2560

Handle multiple rows in trigger (BULK insert)

I do a bulk insert and i have a trigger which makes some checks and inserts accordingly: Let's say that my Bulk insert have two columns HomeTeam AwayTeam.

HomeTeam     AwayTeam
Team1         Team3
Team6         Team9
Team3         Team5

My database has two tables, tblTeamsList(Id,TeamName) which contains the teams and tblPremierLeague(id,HomeTeam,AwayTeam) which i do the bulk insert. I have an INSTEAD OF INSERT trigger which checks if HomeTeam is in tblTeamsList if not then it inserts it inside the tblteams, then it checks the AwayTeam and do the same. And finally i want to insert them into tblPremierleague with their foreign keys and not by name. Here is my trigger.

   instead of INSERT
AS
BEGIN
    DECLARE @homeTeamId INT
    DECLARE @awayTeamId INT
    DECLARE @maxTeamId INT
    DECLARE @matchId INT
    DECLARE @home nvarchar(100)
    DECLARE @away nvarchar(100)

    DECLARE cur CURSOR FOR
    Select HomeTeam,AwayTeam from inserted
    OPEN cur
    FETCH NEXT FROM cur INTO @home, @away

    --- Check if home team has already been inserted into the table.
    SELECT @homeTeamId = -1
    SELECT 
        @homeTeamId = teamId 
    FROM 
        [dbo].[tblTeamslist] t
        JOIN inserted i
        ON t.teamName = @home
    IF (@homeTeamId = -1) 
    BEGIN

        INSERT INTO [dbo].[tblTeamslist] (teamname) SELECT  i.hometeam FROM inserted i

    END

    --- Check if away team has already been inserted into the table.
     SELECT @maxTeamId = 0
    SELECT @maxTeamId = ISNULL(MAX(teamId), 0) from [dbo].[tblTeamslist]


    SELECT @awayTeamId = -1
    SELECT 
        @awayTeamId = teamId 
    FROM 
        [dbo].[tblTeamslist] t
        JOIN inserted i
        ON t.teamName = @away
    IF (@awayTeamId = -1) 
    BEGIN

        INSERT INTO [dbo].[tblTeamslist]  (teamname) SELECT  i.awayteam FROM inserted i

    END

    -- insert a record into the matches table with the home team ID and away team ID.

    INSERT INTO [dbo].[tblpremierLeague] 
    SELECT  @homeTeamId, @awayTeamId
    FROM inserted i
FETCH NEXT FROM cur INTO @home, @away

    END
    CLOSE cur
    DEALLOCATE cur

The above trigger does not work correctly and i get 300 same rows in tblPremierLeague, all rows have the same values as follow:

HomeTeam        AwayTeam
 -1                1

The awayteamId is correct. Moreover into tblTeamsList inserts the same team many times. Any help how to correct my trigger please?

Upvotes: 0

Views: 576

Answers (1)

HABO
HABO

Reputation: 15816

Assuming that the id's are IDENTITY columns:

-- Add all of the home teams we haven't heard of before.
insert into dbo.tblTeamsList
  select i.HomeTeam
    from inserted as i left outer join
      tblTeamsList as TL on TL.TeamName = i.HomeName
    where TL.Id is NULL

-- Add all of the alien teams we haven't heard of before.
insert into dbo.tblTeamsList
  select i.AwayTeam
    from inserted as i left outer join
      tblTeamsList as TL on TL.TeamName = i.AwayTeam
    where TL.Id is NULL

-- Add any **new** pairs to the league.
insert into tblPremierLeague
  select H.Id, A.Id
    from inserted as i inner join
      tblTeamsList as H on H.TeamName = i.HomeTeam inner join
      tblTeamsList as A on A.TeamName = i.AwayTeam left outer join
      tblPremierLeague as TL on TL.HomeTeam = H.Id and TL.AwayTeam = A.Id
    where TL.Id is NULL

Please don't do this Row By Agonizing Row (RBAR). Use set operations.

And many people don't care for the faux-Hungarian notation of naming tables tblMumble. I'd use Mumbles.

Upvotes: 1

Related Questions