Oracle Insert with mutli-valued column

Question

Hey, I am new to Oracle SQL and I am trying to make a trigger which has an insert dependent on columns received from a select query.

I have a basic fantasy baseball app and I am trying to update a table which correlates the player to the team he played on for a single day. The basic idea i want to do is to update the date for each team where the player played on when the batterStat table is changed. So for example (all the static variables are just :new values from the trigger on batterStat).

insert into onTeam(date, player, teamName) values ('newDate','Matt Holliday',(select teamName from onTeam where player = 'Matt Holliday'))

So this would be all fine if Matt Holliday didn't play for more than one team, so is there a way to make it insert for each value returned from that nested select along with the two static variables?

Answer 1

This will result in a "mutating trigger" (attempting DML on the same table on which it is defined) error and shouldn't be implemented as a trigger. It should be implemented as a SQL statement in the layer of your preference (PL/SQL package or application code).

From your description of the logic, there might be a redesign of the onTeam table necessary as well.

Answer 2

Try something like this:

insert into onTeam(date, player, teamName) 
select 'newDate','Matt Holliday', teamName 
from onTeam where player = 'Matt Holliday'