AFTER INSERT OR UPDATE ON tableName : After Event Trigger « Trigger « Oracle PL/SQL Tutorial

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

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>
SQL> CREATE OR REPLACE TRIGGER validate_score
  2  AFTER INSERT OR UPDATE ON game_player
  3  FOR EACH ROW
  4  BEGIN
  5    IF :NEW.marked = 'Y' AND
  6       :NEW.score < 10 THEN
  7         RAISE_APPLICATION_ERROR(-20001,'ERROR: Score For marked Must Be >= 10');
  8    ELSIF :NEW.pcmac = 'Y' AND
  9          :NEW.score < 10 THEN
 10         RAISE_APPLICATION_ERROR(-20001,'ERROR: Score For pcmac Must Be >= 10');
 11    ELSIF :NEW.marked = 'Y' AND
 12          :NEW.pcmac = 'Y' THEN
 13         RAISE_APPLICATION_ERROR(-20001,'ERROR: Cannot Enter pcmac And marked For Same game_player');
 14    END IF;
 15  END;
 16  /

Trigger created.

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

Table dropped.

SQL>