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

DROP TRIGGER T_LEAGUE
DROP TRIGGER T_PLAYER
DROP TRIGGER T_TEAM
go

DROP TABLE TEAMPLAYER
DROP TABLE PLAYER
DROP TABLE TEAM
DROP TABLE LEAGUE
go

CREATE TABLE PLAYER
(
    PLAYER_ID VARCHAR(255) primary key not null,
    NAME VARCHAR(255),
    POSITION VARCHAR(255),
    SALARY double precision not null,
    VERSION NUMERIC(19) not null
)

create table LEAGUE
(
    LEAGUE_ID VARCHAR(255) primary key not null,
    NAME VARCHAR(255),
    SPORT VARCHAR(255),
    VERSION NUMERIC(19) not null
)

create table TEAM
(
    TEAM_ID VARCHAR(255) primary key not null,
    CITY VARCHAR(255),
    NAME VARCHAR(255),
    LEAGUE_ID VARCHAR(255),
    VERSION NUMERIC(19) not null,
    foreign key (LEAGUE_ID) references LEAGUE (LEAGUE_ID)
)

create table TEAMPLAYER
(
    PLAYER_ID VARCHAR(255) not null,
    TEAM_ID VARCHAR(255) not null,
    constraint PK_TEAMPLAYER primary key (PLAYER_ID, TEAM_ID),
    foreign key (TEAM_ID) references TEAM (TEAM_ID),
    foreign key (PLAYER_ID) references PLAYER (PLAYER_ID)
)

go

CREATE TRIGGER T_LEAGUE
   ON LEAGUE
   FOR UPDATE as
   begin
       UPDATE LEAGUE
           SET LEAGUE.VERSION = LEAGUE.VERSION + 1
           FROM inserted
       WHERE LEAGUE.LEAGUE_ID = inserted.LEAGUE_ID
           AND LEAGUE.VERSION = inserted.VERSION
   end
go

CREATE TRIGGER T_PLAYER
   ON PLAYER
   FOR UPDATE as
   begin
       UPDATE PLAYER
           SET PLAYER.VERSION = PLAYER.VERSION + 1
           FROM inserted
       WHERE PLAYER.PLAYER_ID = inserted.PLAYER_ID
           AND PLAYER.VERSION = inserted.VERSION
   end
go

CREATE TRIGGER T_TEAM
   ON TEAM
   FOR UPDATE as
   begin
       UPDATE TEAM
           SET TEAM.VERSION = TEAM.VERSION + 1
           FROM inserted
       WHERE TEAM.TEAM_ID = inserted.TEAM_ID
           AND TEAM.VERSION = inserted.VERSION
   end
go