/* "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;