Create a trigger

[drimades]

I have two tables "Penalties" and "Players" linked with the key "Player_ID". In the "Penalties" table I have several penalties refering to the same Player_ID and in the table "Players" I have a field "total_penalties" with the sum of the penalties for each Player_ID. I need a trigger that updates the field "total_penalties" for the player_ID concerned each time there is a new penalty entry in the table "Penalties". Any idea?

Does this work?

CREATE TRIGGER update_tot

AFTER INSERT ON Penalties

UPDATE Players
SET total_penalties = total_penalties + :new.penalty_value

 

[jaxtell]

I think you want something like this.

CREATE OR REPLACE TRIGGER penalties_AIR
AFTER INSERT
ON penalties
REFERENCING NEW AS NEW
FOR EACH ROW
begin
update players
 set total_penalties = total_penalties + :NEW.penalty_value
where player_id = :NEW.player_id;
--check number of rows updated, handle exceptions....
end;

Although, you could probably accomplish your goal with a view.

select plyr.player_name, pen.num_penalties from players plyr, (select count(*) num_penalties, player_id from penalties group by player_id) pen where pen.player_id = plyr.player_id;

Personally, I use triggers as a last resort. It probably depends on your circumstances if its the right solution for you.

Josh

 

[drimades]

Thank you! It is just for an exam to learn the use of triggers.
Why do I have to tell "FOR EACH ROW" if I'm just going to change just one row refering to the concerned player?

 

[jaxtell]

To tell you the truth, I'm not totally sure. A quick search says

If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.

I think its necessary to specify FOR EACH ROW, since it could be possible to insert multiple rows at once, and they might not all have the same player_id. But the real reason I included it is because it was in the trigger I copied and pasted from

 

[drimades]

Oh I see! Thank you anyway!

 

[Beilstwh]

As an extra point, you might want to tell your instructor that you would never keep a running total in the master record (players) table, you would simply do a sum against the penalties table when you wanted the count

select a.player_id,sum(nvl(b.penalty_value,0)) total_penalty
from penalties b, players a
where a.player_id = b.player_id;

Bill
Oracle DBA/Developer
New York State, USA