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