appserver/tests/v2-tests/appserv-tests/devtests/cmp/rosterVC/sql/RosterVCApp_ifx.sql

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;