1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
|
CREATE DOMAIN D_CHAR10 AS CHAR(10);
CREATE DOMAIN D_CHAR15 AS VARCHAR(15);
CREATE DOMAIN D_CHAR50 AS VARCHAR(50);
CREATE DOMAIN D_LCLE AS INTEGER NOT NULL;
CREATE DOMAIN D_CP AS INTEGER
default 101
check (VALUE >100 AND VALUE <1000) NOT NULL;
CREATE TABLE T_VILLES
(
VL_CP D_CP,
VL_VILLE D_CHAR50 NOT NULL,
CONSTRAINT PK_VILLES PRIMARY KEY (VL_CP)
);
CREATE TABLE T_CLIENTS
(
CL_NO D_LCLE,
CL_DT DATE DEFAULT 'NOW',
CL_ID D_CHAR10,
CL_INTITULE D_CHAR50 NOT NULL,
CL_CONTACT D_CHAR50,
CL_ADDR D_CHAR50 NOT NULL,
CL_ADDR1 D_CHAR50,
CL_CP D_CP NOT NULL,
CL_TEL D_CHAR15,
CL_FAX D_CHAR15,
CL_MAIL D_CHAR50,
CL_NIF D_CHAR15,
CL_RC D_CHAR15,
CL_STAT D_CHAR15,
CONSTRAINT PK_CLIENTS PRIMARY KEY (CL_NO)
);
ALTER TABLE T_CLIENTS ADD CONSTRAINT FK_CLIENTS FOREIGN KEY (CL_CP) REFERENCES T_VILLES (VL_CP) ON UPDATE CASCADE ON DELETE SET DEFAULT;
CREATE GENERATOR GEN_CLIENTS_NO;
SET TERM !!
CREATE TRIGGER TRG_CLIENTS_BI FOR T_CLIENTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (NEW.CL_NO is null) then NEW.CL_NO = GEN_ID(GEN_CLIENTS_NO,1);
end;
!!
SET TERM ;!! |
Partager