Search code examples
sqloracle-databasetriggerssummary

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

Solution

  • 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;