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






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.








28.6.Before Event Trigger
28.6.1.BEFORE EVENT triggers, are for preventing the event from actually happening
28.6.2.Insert before trigger
28.6.3.Check and change new value in a before-insert trigger
28.6.4.Fire a trigger before an update of the table
28.6.5.Change data in a before insert or update trigger
28.6.6.BEFORE INSERT OR UPDATE OF id
28.6.7.Check new value in before insert trigger
28.6.8.Check new value in before trigger
28.6.9.convert character values to upper case
28.6.10.Use trigger to create autoincrement column
28.6.11.Implementing Autonumbering Functionality