| DROP TRIGGER T_LEAGUE ; | |
| DROP TRIGGER T_PLAYER ; | |
| DROP TRIGGER T_TEAM ; | |
| DROP TABLE TEAMPLAYER ; | |
| DROP TABLE PLAYER ; | |
| DROP TABLE TEAM ; | |
| DROP TABLE LEAGUE ; | |
| CREATE TABLE PLAYER | |
| ( | |
| PLAYER_ID VARCHAR(127) PRIMARY KEY, | |
| NAME VARCHAR(127), | |
| POSITION VARCHAR(127), | |
| SALARY DOUBLE PRECISION NOT NULL , | |
| VERSION DECIMAL(19) NOT NULL | |
| ); | |
| CREATE TABLE LEAGUE | |
| ( | |
| LEAGUE_ID VARCHAR(127) PRIMARY KEY, | |
| NAME VARCHAR(127), | |
| SPORT VARCHAR(127), | |
| VERSION DECIMAL(19) NOT NULL | |
| ); | |
| CREATE TABLE TEAM | |
| ( | |
| TEAM_ID VARCHAR(127) PRIMARY KEY, | |
| CITY VARCHAR(127), | |
| NAME VARCHAR(127), | |
| LEAGUE_ID VARCHAR(127), | |
| VERSION DECIMAL(19) NOT NULL , | |
| FOREIGN KEY (LEAGUE_ID) REFERENCES LEAGUE (LEAGUE_ID) | |
| ); | |
| CREATE TABLE TEAMPLAYER | |
| ( | |
| PLAYER_ID VARCHAR(127), | |
| TEAM_ID VARCHAR(127), | |
| PRIMARY KEY (PLAYER_ID, TEAM_ID), | |
| FOREIGN KEY (TEAM_ID) REFERENCES TEAM (TEAM_ID), | |
| FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER (PLAYER_ID) | |
| ); | |
| commit; | |
| CREATE TRIGGER T_LEAGUE | |
| UPDATE ON LEAGUE | |
| REFERENCING OLD AS old NEW AS new | |
| FOR EACH ROW | |
| WHEN (new.VERSION = old.VERSION) | |
| ( | |
| UPDATE LEAGUE SET VERSION = old.VERSION + 1 | |
| ); | |
| / | |
| CREATE TRIGGER T_PLAYER | |
| UPDATE ON PLAYER | |
| REFERENCING OLD AS old NEW AS new | |
| FOR EACH ROW | |
| WHEN (new.VERSION = old.VERSION) | |
| ( | |
| UPDATE PLAYER SET VERSION = old.VERSION + 1 | |
| ); | |
| / | |
| CREATE TRIGGER T_TEAM | |
| UPDATE ON TEAM | |
| REFERENCING OLD AS old NEW AS new | |
| FOR EACH ROW | |
| WHEN (new.VERSION = old.VERSION) | |
| ( | |
| UPDATE TEAM SET VERSION = old.VERSION + 1 | |
| ); | |
| / | |
| commit; | |
| quit; |