Inserting with Triggers

Question

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?

Aaron Bertrand · Accepted Answer · 2013-12-17 04:33:13Z

1

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.

answered Dec 17, 2013 at 4:33

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Upendra Chaudhari · Accepted Answer · 2013-12-17 04:35:34Z

0

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

answered Dec 17, 2013 at 4:35

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

1 Comment

user3109653 Over a year ago

Thank you for your help. I am still very new to SQL, and I was pretty proud that I got the trigger to work to the extent it did :). Thank you for your help. I'll do this next time.

Collectives™ on Stack Overflow

Inserting with Triggers

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related