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