szufi szufi - 6 months ago 21
SQL Question

TRIGGER For points summing

In my table Team which contains:


  • Name

  • Wins

  • Draws

  • Losses

  • Points



Now i want to do a trigger which sum wins, draws, losses and puts it into points column.
Win * 3, Draw * 1, loss * 0

I did something like that:

create or replace
trigger Summ_points
AFTER insert ON Team
FOR EACH ROW
ENABLE
DECLARE
tmp Team.Points%type;
BEGIN
select sum(Wins*3 + Draws + Losses*0) into tmp from Team;
Update Team
set Points=tmp;
END Summ_points;


but it does not work, because all records are updated :/

Language: PL SQl , Oracle

Answer

Inside teh trigger you can access and modify only the triggered record and only using :NEW and :OLD references.

Here's how to update a field on the triggered record of the table:

CREATE OR REPLACE TRIGGER sum_points
BEFORE INSERT OR UPDATE ON team
FOR EACH ROW
BEGIN
    :NEW.Points := :NEW.Wins * 3 + :NEW.Draws;
END;
Comments