Check new value in before trigger : Before Event Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Before Event Trigger

SQL>
SQL> CREATE TABLE game_player
  2  (player_id    NUMBER,
  3   game_id      NUMBER,
  4   group_number NUMBER,
  5   marked       VARCHAR2(1) DEFAULT 'N',
  6   pcmac        VARCHAR2(1) DEFAULT 'N',
  7   score        NUMBER,
  8   CONSTRAINT game_player_pk
  9   PRIMARY KEY (player_id, game_id, group_number));

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER set_score
  2  BEFORE INSERT ON game_player
  3  FOR EACH ROW
  4  WHEN ( new.score IS NULL )
  5  BEGIN
  6    IF :NEW.marked = 'Y' THEN
  7      :NEW.score := 10;
  8    ELSIF :NEW.pcmac = 'Y' THEN
  9         :NEW.score := 5;
 10    END IF;
 11  END;
 12  /

Trigger created.

SQL>
SQL>
SQL>
SQL> drop table game_player;

Table dropped.