Author: Dann Corbit
Date: 19:29:04 03/18/05
/*
The notion of the chess engine database model is:
1. We have a consistent, fast repository for information
2. The information is standardized and has a single user interface
3. The information can be maniupluated by SQL queries
4. The information is platform independent
5. The information carries data needed by chess engines for game play setup
*/
DROP TABLE IntegerParameters
go
CREATE TABLE IntegerParameters (
ProgramVersionID int NOT NULL,
ProgramID int NOT NULL,
IntegerParameterID int NOT NULL,
Name varchar(255) NULL,
MinimumValue int NULL,
MaximumValue int NULL,
DefaultValue int NULL,
CurrentValue int NULL,
PreviousValue int NULL,
Description varchar(255) NULL
)
go
DROP INDEX IntegerParameters.XPKIntegerParameters
go
DROP INDEX IntegerParameters.XIF35IntegerParameters
go
CREATE INDEX XIF35IntegerParameters ON IntegerParameters
(
ProgramVersionID,
ProgramID
)
go
ALTER TABLE IntegerParameters
ADD PRIMARY KEY (ProgramVersionID, ProgramID,
IntegerParameterID)
go
DROP TABLE EgdbAuthor
go
CREATE TABLE EgdbAuthor (
EgdbTypeID int NOT NULL,
AuthorID int NOT NULL
)
go
DROP INDEX EgdbAuthor.XPKEgdbAuthor
go
DROP INDEX EgdbAuthor.XIF31EgdbAuthor
go
DROP INDEX EgdbAuthor.XIF32EgdbAuthor
go
CREATE INDEX XIF31EgdbAuthor ON EgdbAuthor
(
EgdbTypeID
)
go
CREATE INDEX XIF32EgdbAuthor ON EgdbAuthor
(
AuthorID
)
go
ALTER TABLE EgdbAuthor
ADD PRIMARY KEY (EgdbTypeID, AuthorID)
go
DROP TABLE BookAuthor
go
CREATE TABLE BookAuthor (
OpeningBookID int NOT NULL,
AuthorID int NOT NULL
)
go
DROP INDEX BookAuthor.XPKBookAuthor
go
DROP INDEX BookAuthor.XIF33BookAuthor
go
DROP INDEX BookAuthor.XIF34BookAuthor
go
CREATE INDEX XIF33BookAuthor ON BookAuthor
(
OpeningBookID
)
go
CREATE INDEX XIF34BookAuthor ON BookAuthor
(
AuthorID
)
go
ALTER TABLE BookAuthor
ADD PRIMARY KEY (OpeningBookID, AuthorID)
go
DROP TABLE ProgramAuthor
go
CREATE TABLE ProgramAuthor (
AuthorID int NOT NULL,
ProgramID int NOT NULL
)
go
DROP INDEX ProgramAuthor.XPKProgramAuthor
go
DROP INDEX ProgramAuthor.XIF29ProgramAuthor
go
DROP INDEX ProgramAuthor.XIF30ProgramAuthor
go
CREATE INDEX XIF29ProgramAuthor ON ProgramAuthor
(
AuthorID
)
go
CREATE INDEX XIF30ProgramAuthor ON ProgramAuthor
(
ProgramID
)
go
ALTER TABLE ProgramAuthor
ADD PRIMARY KEY (AuthorID, ProgramID)
go
DROP TABLE StringParameters
go
CREATE TABLE StringParameters (
StringParameterID int IDENTITY,
ProgramVersionID int NOT NULL,
ProgramID int NOT NULL,
Name varchar(255) NULL,
Description varchar(255) NULL,
CurrentValue varchar(255) NULL
)
go
DROP INDEX StringParameters.StringParameters_INDEX_1
go
DROP INDEX StringParameters.StringParameters_INDEX_0
go
DROP INDEX StringParameters.XIF22StringParameters
go
DROP INDEX StringParameters.StringParameters_INDEX_2
go
CREATE INDEX XIF22StringParameters ON StringParameters
(
ProgramVersionID,
ProgramID
)
go
CREATE INDEX StringParameters_INDEX_2 ON StringParameters
(
Name
)
go
ALTER TABLE StringParameters
ADD PRIMARY KEY NONCLUSTERED (StringParameterID,
ProgramVersionID, ProgramID)
go
DROP TABLE BooleanParameters
go
CREATE TABLE BooleanParameters (
BooleanParameterID int IDENTITY,
ProgramVersionID int NOT NULL,
ProgramID int NOT NULL,
Name varchar(255) NULL,
Description varchar(255) NULL,
DefaultValue bit,
CurrentValue bit,
PreviousValue bit
)
go
DROP INDEX BooleanParameters.BooleanParameters_INDEX_1
go
DROP INDEX BooleanParameters.BooleanParameters_INDEX_0
go
DROP INDEX BooleanParameters.XIF23BooleanParameters
go
DROP INDEX BooleanParameters.BooleanParameters_INDEX_2
go
CREATE INDEX XIF23BooleanParameters ON BooleanParameters
(
ProgramVersionID,
ProgramID
)
go
CREATE INDEX BooleanParameters_INDEX_2 ON BooleanParameters
(
Name
)
go
ALTER TABLE BooleanParameters
ADD PRIMARY KEY NONCLUSTERED (BooleanParameterID,
ProgramVersionID, ProgramID)
go
DROP TABLE NumericParameters
go
CREATE TABLE NumericParameters (
NumericParameterID int IDENTITY,
ProgramVersionID int NOT NULL,
ProgramID int NOT NULL,
Name varchar(255) NULL,
Description varchar(255) NULL,
MinimumValue float NULL,
MaximumValue float NULL,
DefaultValue float NULL,
CurrentValue float NULL,
PreviousValue float NULL
)
go
DROP INDEX NumericParameters.NumericParameters_INDEX_1
go
DROP INDEX NumericParameters.NumericParameters_INDEX_0
go
DROP INDEX NumericParameters.XIF21NumericParameters
go
DROP INDEX NumericParameters.NumericParameters_INDEX_2
go
CREATE INDEX XIF21NumericParameters ON NumericParameters
(
ProgramVersionID,
ProgramID
)
go
CREATE INDEX NumericParameters_INDEX_2 ON NumericParameters
(
Name
)
go
ALTER TABLE NumericParameters
ADD PRIMARY KEY NONCLUSTERED (NumericParameterID,
ProgramVersionID, ProgramID)
go
DROP TABLE ProgramVersion
go
CREATE TABLE ProgramVersion (
ProgramID int NOT NULL,
ProgramVersionID int IDENTITY,
ProgramVersionOrDate varchar(255) NULL,
OpeningBookID int NULL,
PhysicalLocation varchar(255) NULL,
EgdbTypeID int NULL,
PhysicalName varchar(255) NULL,
LogicalName varchar(255) NULL
)
go
DROP INDEX ProgramVersion.ProgramVersion_INDEX_1
go
DROP INDEX ProgramVersion.ProgramVersion_INDEX_2
go
DROP INDEX ProgramVersion.ProgramVersion_INDEX_4
go
DROP INDEX ProgramVersion.XIF24ProgramVersion
go
DROP INDEX ProgramVersion.XIF25ProgramVersion
go
DROP INDEX ProgramVersion.ProgramVersion_INDEX_3
go
CREATE INDEX XIF24ProgramVersion ON ProgramVersion
(
EgdbTypeID
)
go
CREATE INDEX XIF25ProgramVersion ON ProgramVersion
(
OpeningBookID
)
go
CREATE INDEX ProgramVersion_INDEX_3 ON ProgramVersion
(
PhysicalName
)
go
ALTER TABLE ProgramVersion
ADD PRIMARY KEY NONCLUSTERED (ProgramVersionID, ProgramID)
go
DROP TABLE Authors
go
CREATE TABLE Authors (
AuthorID int NOT NULL,
Firstname varchar(255) NULL,
CountryID int NULL,
Middlename varchar(255) NULL,
Lastname varchar(255) NULL,
title varchar(255) NULL,
notes text NULL
)
go
DROP INDEX Authors.Programmers_INDEX_0
go
DROP INDEX Authors.XIF16Authors
go
DROP INDEX Authors.Programmers_INDEX_3
go
DROP INDEX Authors.Programmers_INDEX_4
go
CREATE INDEX XIF16Authors ON Authors
(
CountryID
)
go
CREATE INDEX Programmers_INDEX_3 ON Authors
(
Firstname
)
go
CREATE INDEX Programmers_INDEX_4 ON Authors
(
Lastname
)
go
ALTER TABLE Authors
ADD PRIMARY KEY (AuthorID)
go
DROP TABLE AuthorInfo
go
CREATE TABLE AuthorInfo (
AuthorID int IDENTITY,
Picture image NULL,
Greeting image NULL,
Biography text NULL
)
go
DROP INDEX AuthorInfo.ProgrammerInfo_INDEX_1
go
DROP INDEX AuthorInfo.ProgrammerInfo_INDEX_0
go
ALTER TABLE AuthorInfo
ADD PRIMARY KEY NONCLUSTERED (AuthorID)
go
DROP TABLE Program
go
CREATE TABLE Program (
ProgramID int IDENTITY,
ProgramName varchar(255) NULL
)
go
DROP INDEX Program.Program_INDEX_3
go
DROP INDEX Program.Program_INDEX_0
go
ALTER TABLE Program
ADD PRIMARY KEY NONCLUSTERED (ProgramID)
go
DROP TABLE OpeningBook
go
CREATE TABLE OpeningBook (
OpeningBookID int IDENTITY,
FormatID int NULL,
BookName varchar(255) NULL,
BookPath varchar(255) NULL
)
go
DROP INDEX OpeningBook.OpeningBook_INDEX_1
go
DROP INDEX OpeningBook.OpeningBook_INDEX_0
go
DROP INDEX OpeningBook.OpeningBook_INDEX_2
go
DROP INDEX OpeningBook.OpeningBook_INDEX_3
go
DROP INDEX OpeningBook.OpeningBook_INDEX_4
go
CREATE INDEX OpeningBook_INDEX_2 ON OpeningBook
(
BookName
)
go
CREATE INDEX OpeningBook_INDEX_3 ON OpeningBook
(
FormatID
)
go
CREATE INDEX OpeningBook_INDEX_4 ON OpeningBook
(
FormatID
)
go
ALTER TABLE OpeningBook
ADD PRIMARY KEY NONCLUSTERED (OpeningBookID)
go
DROP TABLE OpeningBookFormats
go
CREATE TABLE OpeningBookFormats (
FormatID int IDENTITY,
Name varchar(255) NULL,
TechnicalDescription text NULL
)
go
DROP INDEX OpeningBookFormats.OpeningBookFormats_INDEX_1
go
DROP INDEX OpeningBookFormats.OpeningBookFormats_INDEX_0
go
ALTER TABLE OpeningBookFormats
ADD PRIMARY KEY (FormatID)
go
DROP TABLE EndGameDataBase
go
CREATE TABLE EndGameDataBase (
EgdbTypeID int IDENTITY,
EgdbPath varchar(255) NULL,
IsBitbase bit,
IsDTM bit,
IsDTC bit,
IsDTZ bit,
IsDTR bit,
EgdbDllPath varchar(255) NULL,
EgdbDescription varchar(255) NULL,
EgdbInterfaceDescription text NULL
)
go
DROP INDEX EndGameDataBase.EndGameDataBase_INDEX_1
go
DROP INDEX EndGameDataBase.EndGameDataBase_INDEX_0
go
ALTER TABLE EndGameDataBase
ADD PRIMARY KEY NONCLUSTERED (EgdbTypeID)
go
DROP TABLE CountryCodes
go
CREATE TABLE CountryCodes (
CountryID int NOT NULL,
abbreviation char(2) NOT NULL,
countryname varchar(255) NULL
)
go
DROP INDEX CountryCodes.CountryCodes_INDEX_3
go
DROP INDEX CountryCodes.CountryCodes_INDEX_0
go
DROP INDEX CountryCodes.CountryCodes_INDEX_1
go
DROP INDEX CountryCodes.CountryCodes_INDEX_2
go
ALTER TABLE CountryCodes
ADD PRIMARY KEY (CountryID)
go
ALTER TABLE IntegerParameters
ADD FOREIGN KEY (ProgramVersionID, ProgramID)
REFERENCES ProgramVersion
go
ALTER TABLE EgdbAuthor
ADD FOREIGN KEY (AuthorID)
REFERENCES Authors
go
ALTER TABLE EgdbAuthor
ADD FOREIGN KEY (EgdbTypeID)
REFERENCES EndGameDataBase
go
ALTER TABLE BookAuthor
ADD FOREIGN KEY (AuthorID)
REFERENCES Authors
go
ALTER TABLE BookAuthor
ADD FOREIGN KEY (OpeningBookID)
REFERENCES OpeningBook
go
ALTER TABLE ProgramAuthor
ADD FOREIGN KEY (ProgramID)
REFERENCES Program
go
ALTER TABLE ProgramAuthor
ADD FOREIGN KEY (AuthorID)
REFERENCES Authors
go
ALTER TABLE StringParameters
ADD FOREIGN KEY (ProgramVersionID, ProgramID)
REFERENCES ProgramVersion
go
ALTER TABLE BooleanParameters
ADD FOREIGN KEY (ProgramVersionID, ProgramID)
REFERENCES ProgramVersion
go
ALTER TABLE NumericParameters
ADD FOREIGN KEY (ProgramVersionID, ProgramID)
REFERENCES ProgramVersion
go
ALTER TABLE ProgramVersion
ADD FOREIGN KEY (OpeningBookID)
REFERENCES OpeningBook
go
ALTER TABLE ProgramVersion
ADD FOREIGN KEY (EgdbTypeID)
REFERENCES EndGameDataBase
go
ALTER TABLE ProgramVersion
ADD FOREIGN KEY (ProgramID)
REFERENCES Program
go
ALTER TABLE Authors
ADD FOREIGN KEY (AuthorID)
REFERENCES AuthorInfo
go
ALTER TABLE Authors
ADD FOREIGN KEY (CountryID)
REFERENCES CountryCodes
go
ALTER TABLE OpeningBook
ADD FOREIGN KEY (FormatID)
REFERENCES OpeningBookFormats
go
create trigger tI_IntegerParameters on IntegerParameters for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on IntegerParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/35 IntegerParameters ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT IntegerParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_IntegerParameters on IntegerParameters for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on IntegerParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insProgramVersionID int,
@insProgramID int,
@insIntegerParameterID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/35 IntegerParameters ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE IntegerParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_EgdbAuthor on EgdbAuthor for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on EgdbAuthor */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/32 EgdbAuthor ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Authors
where
/* %JoinFKPK(inserted,Authors) */
inserted.AuthorID = Authors.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT EgdbAuthor because Authors does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* EndGameDataBase R/31 EgdbAuthor ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(EgdbTypeID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,EndGameDataBase
where
/* %JoinFKPK(inserted,EndGameDataBase) */
inserted.EgdbTypeID = EndGameDataBase.EgdbTypeID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT EgdbAuthor because EndGameDataBase does
not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_EgdbAuthor on EgdbAuthor for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on EgdbAuthor */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insEgdbTypeID int,
@insAuthorID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/32 EgdbAuthor ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Authors
where
/* %JoinFKPK(inserted,Authors) */
inserted.AuthorID = Authors.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE EgdbAuthor because Authors does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* EndGameDataBase R/31 EgdbAuthor ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(EgdbTypeID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,EndGameDataBase
where
/* %JoinFKPK(inserted,EndGameDataBase) */
inserted.EgdbTypeID = EndGameDataBase.EgdbTypeID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE EgdbAuthor because EndGameDataBase does
not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BookAuthor on BookAuthor for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on BookAuthor */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/34 BookAuthor ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Authors
where
/* %JoinFKPK(inserted,Authors) */
inserted.AuthorID = Authors.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BookAuthor because Authors does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/33 BookAuthor ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(OpeningBookID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,OpeningBook
where
/* %JoinFKPK(inserted,OpeningBook) */
inserted.OpeningBookID = OpeningBook.OpeningBookID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BookAuthor because OpeningBook does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BookAuthor on BookAuthor for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on BookAuthor */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insOpeningBookID int,
@insAuthorID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/34 BookAuthor ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Authors
where
/* %JoinFKPK(inserted,Authors) */
inserted.AuthorID = Authors.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BookAuthor because Authors does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/33 BookAuthor ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(OpeningBookID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,OpeningBook
where
/* %JoinFKPK(inserted,OpeningBook) */
inserted.OpeningBookID = OpeningBook.OpeningBookID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BookAuthor because OpeningBook does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_ProgramAuthor on ProgramAuthor for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on ProgramAuthor */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/30 ProgramAuthor ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Program
where
/* %JoinFKPK(inserted,Program) */
inserted.ProgramID = Program.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT ProgramAuthor because Program does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/29 ProgramAuthor ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Authors
where
/* %JoinFKPK(inserted,Authors) */
inserted.AuthorID = Authors.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT ProgramAuthor because Authors does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_ProgramAuthor on ProgramAuthor for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on ProgramAuthor */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insAuthorID int,
@insProgramID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/30 ProgramAuthor ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Program
where
/* %JoinFKPK(inserted,Program) */
inserted.ProgramID = Program.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE ProgramAuthor because Program does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/29 ProgramAuthor ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Authors
where
/* %JoinFKPK(inserted,Authors) */
inserted.AuthorID = Authors.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE ProgramAuthor because Authors does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_StringParameters on StringParameters for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on StringParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/22 StringParameters ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT StringParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_StringParameters on StringParameters for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on StringParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insStringParameterID int,
@insProgramVersionID int,
@insProgramID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/22 StringParameters ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE StringParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_BooleanParameters on BooleanParameters for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on BooleanParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/23 BooleanParameters ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT BooleanParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_BooleanParameters on BooleanParameters for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on BooleanParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insBooleanParameterID int,
@insProgramVersionID int,
@insProgramID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/23 BooleanParameters ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE BooleanParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_NumericParameters on NumericParameters for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on NumericParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/21 NumericParameters ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT NumericParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_NumericParameters on NumericParameters for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on NumericParameters */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insNumericParameterID int,
@insProgramVersionID int,
@insProgramID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/21 NumericParameters ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,ProgramVersion
where
/* %JoinFKPK(inserted,ProgramVersion) */
inserted.ProgramVersionID = ProgramVersion.ProgramVersionID and
inserted.ProgramID = ProgramVersion.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE NumericParameters because ProgramVersion
does not exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_ProgramVersion on ProgramVersion for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* DELETE trigger on ProgramVersion */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/35 IntegerParameters ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,IntegerParameters
where
/* %JoinFKPK(IntegerParameters,deleted," = "," and") */
IntegerParameters.ProgramVersionID = deleted.ProgramVersionID and
IntegerParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE ProgramVersion because IntegerParameters
exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/23 BooleanParameters ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BooleanParameters
where
/* %JoinFKPK(BooleanParameters,deleted," = "," and") */
BooleanParameters.ProgramVersionID = deleted.ProgramVersionID and
BooleanParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE ProgramVersion because BooleanParameters
exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/22 StringParameters ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,StringParameters
where
/* %JoinFKPK(StringParameters,deleted," = "," and") */
StringParameters.ProgramVersionID = deleted.ProgramVersionID and
StringParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE ProgramVersion because StringParameters
exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/21 NumericParameters ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,NumericParameters
where
/* %JoinFKPK(NumericParameters,deleted," = "," and") */
NumericParameters.ProgramVersionID = deleted.ProgramVersionID and
NumericParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE ProgramVersion because NumericParameters
exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_ProgramVersion on ProgramVersion for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on ProgramVersion */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/25 ProgramVersion ON CHILD INSERT SET NULL */
if
/* %ChildFK(" or",update) */
update(OpeningBookID)
begin
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.OpeningBookID = NULL
from ProgramVersion,inserted
where
/* %JoinPKPK(ProgramVersion,inserted," = "," and") */
ProgramVersion.ProgramVersionID = inserted.ProgramVersionID and
ProgramVersion.ProgramID = inserted.ProgramID and
not exists (
select * from OpeningBook
where
/* %JoinFKPK(inserted,OpeningBook," = "," and") */
inserted.OpeningBookID = OpeningBook.OpeningBookID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* EndGameDataBase R/24 ProgramVersion ON CHILD INSERT SET NULL */
if
/* %ChildFK(" or",update) */
update(EgdbTypeID)
begin
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.EgdbTypeID = NULL
from ProgramVersion,inserted
where
/* %JoinPKPK(ProgramVersion,inserted," = "," and") */
ProgramVersion.ProgramVersionID = inserted.ProgramVersionID and
ProgramVersion.ProgramID = inserted.ProgramID and
not exists (
select * from EndGameDataBase
where
/* %JoinFKPK(inserted,EndGameDataBase," = "," and") */
inserted.EgdbTypeID = EndGameDataBase.EgdbTypeID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/4 ProgramVersion ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Program
where
/* %JoinFKPK(inserted,Program) */
inserted.ProgramID = Program.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT ProgramVersion because Program does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_ProgramVersion on ProgramVersion for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on ProgramVersion */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insProgramVersionID int,
@insProgramID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/35 IntegerParameters ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
if exists (
select * from deleted,IntegerParameters
where
/* %JoinFKPK(IntegerParameters,deleted," = "," and") */
IntegerParameters.ProgramVersionID = deleted.ProgramVersionID and
IntegerParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE ProgramVersion because IntegerParameters
exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/23 BooleanParameters ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
if exists (
select * from deleted,BooleanParameters
where
/* %JoinFKPK(BooleanParameters,deleted," = "," and") */
BooleanParameters.ProgramVersionID = deleted.ProgramVersionID and
BooleanParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE ProgramVersion because BooleanParameters
exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/22 StringParameters ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
if exists (
select * from deleted,StringParameters
where
/* %JoinFKPK(StringParameters,deleted," = "," and") */
StringParameters.ProgramVersionID = deleted.ProgramVersionID and
StringParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE ProgramVersion because StringParameters
exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* ProgramVersion R/21 NumericParameters ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ProgramVersionID) or
update(ProgramID)
begin
if exists (
select * from deleted,NumericParameters
where
/* %JoinFKPK(NumericParameters,deleted," = "," and") */
NumericParameters.ProgramVersionID = deleted.ProgramVersionID and
NumericParameters.ProgramID = deleted.ProgramID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE ProgramVersion because NumericParameters
exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/25 ProgramVersion ON CHILD UPDATE SET NULL */
if
/* %ChildFK(" or",update) */
update(OpeningBookID)
begin
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.OpeningBookID = NULL
from ProgramVersion,inserted
where
/* %JoinPKPK(ProgramVersion,inserted," = "," and") */
ProgramVersion.ProgramVersionID = inserted.ProgramVersionID and
ProgramVersion.ProgramID = inserted.ProgramID and
not exists (
select * from OpeningBook
where
/* %JoinFKPK(inserted,OpeningBook," = "," and") */
inserted.OpeningBookID = OpeningBook.OpeningBookID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* EndGameDataBase R/24 ProgramVersion ON CHILD UPDATE SET NULL */
if
/* %ChildFK(" or",update) */
update(EgdbTypeID)
begin
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.EgdbTypeID = NULL
from ProgramVersion,inserted
where
/* %JoinPKPK(ProgramVersion,inserted," = "," and") */
ProgramVersion.ProgramVersionID = inserted.ProgramVersionID and
ProgramVersion.ProgramID = inserted.ProgramID and
not exists (
select * from EndGameDataBase
where
/* %JoinFKPK(inserted,EndGameDataBase," = "," and") */
inserted.EgdbTypeID = EndGameDataBase.EgdbTypeID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/4 ProgramVersion ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ProgramID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,Program
where
/* %JoinFKPK(inserted,Program) */
inserted.ProgramID = Program.ProgramID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE ProgramVersion because Program does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_Authors on Authors for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* DELETE trigger on Authors */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/34 BookAuthor ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BookAuthor
where
/* %JoinFKPK(BookAuthor,deleted," = "," and") */
BookAuthor.AuthorID = deleted.AuthorID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Authors because BookAuthor exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/32 EgdbAuthor ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,EgdbAuthor
where
/* %JoinFKPK(EgdbAuthor,deleted," = "," and") */
EgdbAuthor.AuthorID = deleted.AuthorID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Authors because EgdbAuthor exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/29 ProgramAuthor ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ProgramAuthor
where
/* %JoinFKPK(ProgramAuthor,deleted," = "," and") */
ProgramAuthor.AuthorID = deleted.AuthorID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Authors because ProgramAuthor exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_Authors on Authors for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on Authors */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* AuthorInfo R/27 Authors ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,AuthorInfo
where
/* %JoinFKPK(inserted,AuthorInfo) */
inserted.AuthorID = AuthorInfo.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30002,
@errmsg = 'Cannot INSERT Authors because AuthorInfo does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* CountryCodes R/16 Authors ON CHILD INSERT SET NULL */
if
/* %ChildFK(" or",update) */
update(CountryID)
begin
update Authors
set
/* %SetFK(Authors,NULL) */
Authors.CountryID = NULL
from Authors,inserted
where
/* %JoinPKPK(Authors,inserted," = "," and") */
Authors.AuthorID = inserted.AuthorID and
not exists (
select * from CountryCodes
where
/* %JoinFKPK(inserted,CountryCodes," = "," and") */
inserted.CountryID = CountryCodes.CountryID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_Authors on Authors for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on Authors */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insAuthorID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/34 BookAuthor ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(AuthorID)
begin
if exists (
select * from deleted,BookAuthor
where
/* %JoinFKPK(BookAuthor,deleted," = "," and") */
BookAuthor.AuthorID = deleted.AuthorID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Authors because BookAuthor exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/32 EgdbAuthor ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(AuthorID)
begin
if exists (
select * from deleted,EgdbAuthor
where
/* %JoinFKPK(EgdbAuthor,deleted," = "," and") */
EgdbAuthor.AuthorID = deleted.AuthorID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Authors because EgdbAuthor exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Authors R/29 ProgramAuthor ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(AuthorID)
begin
if exists (
select * from deleted,ProgramAuthor
where
/* %JoinFKPK(ProgramAuthor,deleted," = "," and") */
ProgramAuthor.AuthorID = deleted.AuthorID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Authors because ProgramAuthor exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* AuthorInfo R/27 Authors ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(AuthorID)
begin
select @nullcnt = 0
select @validcnt = count(*)
from inserted,AuthorInfo
where
/* %JoinFKPK(inserted,AuthorInfo) */
inserted.AuthorID = AuthorInfo.AuthorID
/* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted
where"," and") */
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE Authors because AuthorInfo does not
exist.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* CountryCodes R/16 Authors ON CHILD UPDATE SET NULL */
if
/* %ChildFK(" or",update) */
update(CountryID)
begin
update Authors
set
/* %SetFK(Authors,NULL) */
Authors.CountryID = NULL
from Authors,inserted
where
/* %JoinPKPK(Authors,inserted," = "," and") */
Authors.AuthorID = inserted.AuthorID and
not exists (
select * from CountryCodes
where
/* %JoinFKPK(inserted,CountryCodes," = "," and") */
inserted.CountryID = CountryCodes.CountryID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_AuthorInfo on AuthorInfo for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* DELETE trigger on AuthorInfo */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* AuthorInfo R/27 Authors ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,Authors
where
/* %JoinFKPK(Authors,deleted," = "," and") */
Authors.AuthorID = deleted.AuthorID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE AuthorInfo because Authors exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_AuthorInfo on AuthorInfo for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on AuthorInfo */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insAuthorID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* AuthorInfo R/27 Authors ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(AuthorID)
begin
if exists (
select * from deleted,Authors
where
/* %JoinFKPK(Authors,deleted," = "," and") */
Authors.AuthorID = deleted.AuthorID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE AuthorInfo because Authors exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_Program on Program for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* DELETE trigger on Program */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/30 ProgramAuthor ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ProgramAuthor
where
/* %JoinFKPK(ProgramAuthor,deleted," = "," and") */
ProgramAuthor.ProgramID = deleted.ProgramID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Program because ProgramAuthor exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/4 ProgramVersion ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ProgramVersion
where
/* %JoinFKPK(ProgramVersion,deleted," = "," and") */
ProgramVersion.ProgramID = deleted.ProgramID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE Program because ProgramVersion exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_Program on Program for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* UPDATE trigger on Program */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insProgramID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/30 ProgramAuthor ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ProgramID)
begin
if exists (
select * from deleted,ProgramAuthor
where
/* %JoinFKPK(ProgramAuthor,deleted," = "," and") */
ProgramAuthor.ProgramID = deleted.ProgramID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Program because ProgramAuthor exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* Program R/4 ProgramVersion ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ProgramID)
begin
if exists (
select * from deleted,ProgramVersion
where
/* %JoinFKPK(ProgramVersion,deleted," = "," and") */
ProgramVersion.ProgramID = deleted.ProgramID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE Program because ProgramVersion exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_OpeningBook on OpeningBook for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* DELETE trigger on OpeningBook */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/33 BookAuthor ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,BookAuthor
where
/* %JoinFKPK(BookAuthor,deleted," = "," and") */
BookAuthor.OpeningBookID = deleted.OpeningBookID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE OpeningBook because BookAuthor exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/25 ProgramVersion ON PARENT DELETE SET NULL */
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.OpeningBookID = NULL
from ProgramVersion,deleted
where
/* %JoinFKPK(ProgramVersion,deleted," = "," and") */
ProgramVersion.OpeningBookID = deleted.OpeningBookID
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tI_OpeningBook on OpeningBook for INSERT as
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* INSERT trigger on OpeningBook */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBookFormats R/6 OpeningBook ON CHILD INSERT SET NULL */
if
/* %ChildFK(" or",update) */
update(FormatID)
begin
update OpeningBook
set
/* %SetFK(OpeningBook,NULL) */
OpeningBook.FormatID = NULL
from OpeningBook,inserted
where
/* %JoinPKPK(OpeningBook,inserted," = "," and") */
OpeningBook.OpeningBookID = inserted.OpeningBookID and
not exists (
select * from OpeningBookFormats
where
/* %JoinFKPK(inserted,OpeningBookFormats," = "," and") */
inserted.FormatID = OpeningBookFormats.FormatID
)
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_OpeningBook on OpeningBook for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* UPDATE trigger on OpeningBook */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insOpeningBookID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/33 BookAuthor ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(OpeningBookID)
begin
if exists (
select * from deleted,BookAuthor
where
/* %JoinFKPK(BookAuthor,deleted," = "," and") */
BookAuthor.OpeningBookID = deleted.OpeningBookID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE OpeningBook because BookAuthor exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBook R/25 ProgramVersion ON PARENT UPDATE SET NULL */
if
/* %ParentPK(" or",update) */
update(OpeningBookID)
begin
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.OpeningBookID = NULL
from ProgramVersion,deleted
where
/* %JoinFKPK(ProgramVersion,deleted," = "," and") */
ProgramVersion.OpeningBookID = deleted.OpeningBookID
end
/* ERwin Builtin Fri Mar 18 19:11:36 2005 */
/* OpeningBookFormats R/6 OpeningBook ON CHILD UPDATE SET NULL */
if
/* %ChildFK(" or",update) */
update(FormatID)
begin
update OpeningBook
set
/* %SetFK(OpeningBook,NULL) */
OpeningBook.FormatID = NULL
from OpeningBook,inserted
where
/* %JoinPKPK(OpeningBook,inserted," = "," and") */
OpeningBook.OpeningBookID = inserted.OpeningBookID and
not exists (
select * from OpeningBookFormats
where
/* %JoinFKPK(inserted,OpeningBookFormats," = "," and") */
inserted.FormatID = OpeningBookFormats.FormatID
)
end
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_OpeningBookFormats on OpeningBookFormats for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* DELETE trigger on OpeningBookFormats */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* OpeningBookFormats R/6 OpeningBook ON PARENT DELETE SET NULL */
update OpeningBook
set
/* %SetFK(OpeningBook,NULL) */
OpeningBook.FormatID = NULL
from OpeningBook,deleted
where
/* %JoinFKPK(OpeningBook,deleted," = "," and") */
OpeningBook.FormatID = deleted.FormatID
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_OpeningBookFormats on OpeningBookFormats for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* UPDATE trigger on OpeningBookFormats */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insFormatID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* OpeningBookFormats R/6 OpeningBook ON PARENT UPDATE SET NULL */
if
/* %ParentPK(" or",update) */
update(FormatID)
begin
update OpeningBook
set
/* %SetFK(OpeningBook,NULL) */
OpeningBook.FormatID = NULL
from OpeningBook,deleted
where
/* %JoinFKPK(OpeningBook,deleted," = "," and") */
OpeningBook.FormatID = deleted.FormatID
end
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_EndGameDataBase on EndGameDataBase for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* DELETE trigger on EndGameDataBase */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* EndGameDataBase R/31 EgdbAuthor ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,EgdbAuthor
where
/* %JoinFKPK(EgdbAuthor,deleted," = "," and") */
EgdbAuthor.EgdbTypeID = deleted.EgdbTypeID
)
begin
select @errno = 30001,
@errmsg = 'Cannot DELETE EndGameDataBase because EgdbAuthor
exists.'
goto error
end
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* EndGameDataBase R/24 ProgramVersion ON PARENT DELETE SET NULL */
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.EgdbTypeID = NULL
from ProgramVersion,deleted
where
/* %JoinFKPK(ProgramVersion,deleted," = "," and") */
ProgramVersion.EgdbTypeID = deleted.EgdbTypeID
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_EndGameDataBase on EndGameDataBase for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* UPDATE trigger on EndGameDataBase */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insEgdbTypeID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* EndGameDataBase R/31 EgdbAuthor ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(EgdbTypeID)
begin
if exists (
select * from deleted,EgdbAuthor
where
/* %JoinFKPK(EgdbAuthor,deleted," = "," and") */
EgdbAuthor.EgdbTypeID = deleted.EgdbTypeID
)
begin
select @errno = 30005,
@errmsg = 'Cannot UPDATE EndGameDataBase because EgdbAuthor
exists.'
goto error
end
end
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* EndGameDataBase R/24 ProgramVersion ON PARENT UPDATE SET NULL */
if
/* %ParentPK(" or",update) */
update(EgdbTypeID)
begin
update ProgramVersion
set
/* %SetFK(ProgramVersion,NULL) */
ProgramVersion.EgdbTypeID = NULL
from ProgramVersion,deleted
where
/* %JoinFKPK(ProgramVersion,deleted," = "," and") */
ProgramVersion.EgdbTypeID = deleted.EgdbTypeID
end
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tD_CountryCodes on CountryCodes for DELETE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* DELETE trigger on CountryCodes */
begin
declare @errno int,
@errmsg varchar(255)
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* CountryCodes R/16 Authors ON PARENT DELETE SET NULL */
update Authors
set
/* %SetFK(Authors,NULL) */
Authors.CountryID = NULL
from Authors,deleted
where
/* %JoinFKPK(Authors,deleted," = "," and") */
Authors.CountryID = deleted.CountryID
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
create trigger tU_CountryCodes on CountryCodes for UPDATE as
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* UPDATE trigger on CountryCodes */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insCountryID int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
/* CountryCodes R/16 Authors ON PARENT UPDATE SET NULL */
if
/* %ParentPK(" or",update) */
update(CountryID)
begin
update Authors
set
/* %SetFK(Authors,NULL) */
Authors.CountryID = NULL
from Authors,deleted
where
/* %JoinFKPK(Authors,deleted," = "," and") */
Authors.CountryID = deleted.CountryID
end
/* ERwin Builtin Fri Mar 18 19:11:37 2005 */
return
error:
raiserror @errno @errmsg
rollback transaction
end
go
This page took 0 seconds to execute
Last modified: Thu, 15 Apr 21 08:11:13 -0700
Current Computer Chess Club Forums at Talkchess. This site by Sean Mintz.