CODEWITHSUNDEEP
sql-servertriggers
user3109653
user3109653

Reputation: 351

Inserting with Triggers

I am writing a trigger. Whenever I insert multiple values into my table, NFL.Widereceivers, I want it to automatically insert these values into another table, AFC.North. I have written a trigger, and it works to an extent:

begin
declare 

@name varchar(30),

@team varchar(3),

@receptions int,

@yards int,

@touchdowns int

select @name = Name from inserted

select @team = Team from inserted

select @receptions = Receptions from inserted

select @yards = Yards from inserted

select @touchdowns = Touchdowns from inserted

if (@team = 'PIT' or @team = 'BAL' or @team = 'CIN' or @team = 'CLE')

begin
insert into AFC.North (Name, Team, Receptions, Yards, Touchdowns)

values (@name, @team, @receptions, @yards, @touchdowns);
end
end

However, this trigger does not work if I insert multiple values into NFL.Widereceivers, only the first row is inserted into AFC.North.

How can I make the trigger insert multiple rows of data?

Upvotes: 0

Views: 56

Answers (2)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

Why are you assigning values in variables in trigger, you can insert it directly in table like below. If you assign values in variables then it will store values for one row at a time. It will work fine if you insert records one by one, but not work in multiple.

insert into AFC.North (Name, Team, Receptions, Yards, Touchdowns) 
select Name, Team, Receptions, Yards, Touchdowns    
from inserted
where Team IN ('PIT','BAL','CIN','CLE')

Even your variable code also can be optimized in single query like 

select @name = Name, @team = Team, @receptions = Receptions, @yards = Yards, @touchdowns = Touchdowns from inserted

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Your trigger makes a common but unfortunate mistaken assumption that all statements that fire them will affect exactly one row. Unlike some other platforms, a trigger fires per statement, not per row. So, you need to treat inserted like a set, and therefore stop assigning individual values to variables.

INSERT AFC.North(Name,Team,Receptions,Yards,Touchdowns)
  SELECT Name,Team,Receptions,Yards,Touchdowns
  FROM inserted WHERE Team IN ('BAL','CIN','CLE','PIT');

You also need to decide what to do for the rows that are in inserted for the other divisions (hint: you will need an INSERT statement per division, I suspect). Of course a better design would have the division as a column, rather than have each division with its own table.

Upvotes: 1

Related Questions