| /* "cascade" will drop the triggers */ |
| DROP TABLE TEAMPLAYER ; |
| DROP TABLE PLAYER cascade ; |
| DROP TABLE TEAM cascade ; |
| DROP TABLE LEAGUE cascade ; |
| |
| commit; |
| |
| CREATE TABLE PLAYER |
| ( |
| PLAYER_ID VARCHAR(255) PRIMARY KEY, |
| NAME VARCHAR(255), |
| POSITION VARCHAR(255), |
| SALARY DOUBLE PRECISION NOT NULL, |
| VERSION NUMBER(19) NOT NULL |
| ); |
| |
| commit; |
| |
| CREATE TABLE LEAGUE |
| ( |
| LEAGUE_ID VARCHAR(255) PRIMARY KEY, |
| NAME VARCHAR(255), |
| SPORT VARCHAR(255), |
| VERSION NUMBER(19) NOT NULL |
| ); |
| |
| commit; |
| |
| CREATE TABLE TEAM |
| ( |
| TEAM_ID VARCHAR(255) PRIMARY KEY, |
| CITY VARCHAR(255), |
| NAME VARCHAR(255), |
| LEAGUE_ID VARCHAR(255), |
| VERSION NUMBER(19) NOT NULL, |
| CONSTRAINT FK_1 FOREIGN KEY (LEAGUE_ID) REFERENCES LEAGUE (LEAGUE_ID) |
| ); |
| |
| commit; |
| |
| CREATE TABLE TEAMPLAYER |
| ( |
| PLAYER_ID VARCHAR(255), |
| TEAM_ID VARCHAR(255), |
| CONSTRAINT FK_2 FOREIGN KEY (TEAM_ID) REFERENCES TEAM (TEAM_ID), |
| CONSTRAINT FK_3 FOREIGN KEY (PLAYER_ID) REFERENCES PLAYER (PLAYER_ID) |
| ); |
| |
| commit; |
| |
| CREATE TRIGGER T_LEAGUE |
| BEFORE UPDATE ON LEAGUE |
| REFERENCING |
| NEW AS N_ROW |
| OLD AS O_ROW |
| FOR EACH ROW |
| WHEN (N_ROW.VERSION = O_ROW.VERSION) |
| SET N_ROW.VERSION = O_ROW.VERSION + 1 |
| ; |
| |
| commit; |
| |
| CREATE TRIGGER T_PLAYER |
| BEFORE UPDATE ON PLAYER |
| REFERENCING |
| NEW AS N_ROW |
| OLD AS O_ROW |
| FOR EACH ROW |
| WHEN (N_ROW.VERSION = O_ROW.VERSION) |
| SET N_ROW.VERSION = O_ROW.VERSION + 1 |
| ; |
| |
| commit; |
| |
| CREATE TRIGGER T_TEAM |
| BEFORE UPDATE ON TEAM |
| REFERENCING |
| NEW AS N_ROW |
| OLD AS O_ROW |
| FOR EACH ROW |
| WHEN (N_ROW.VERSION = O_ROW.VERSION) |
| SET N_ROW.VERSION = O_ROW.VERSION + 1 |
| ; |
| |
| commit; |
| |
| quit; |