Zeoslib Portal

Code: Select all

CREATE TABLE ADRESSSTAMM (
  ID BIGINT NOT NULL,
  IANREDE INTEGER,
  IPLZORT1 BIGINT,
  IPLZORT2 BIGINT,
  BMITARBEITER T_BOOLEAN_CHAR NOT NULL,
  BLIEFERANT T_BOOLEAN_CHAR NOT NULL,
  BALTLIEFERANT T_BOOLEAN_CHAR NOT NULL,
  BKUNDE T_BOOLEAN_CHAR NOT NULL,
  BWIRTH T_BOOLEAN_CHAR NOT NULL,
  IPREISKLASSERAMPE BIGINT,
  IPREISKLASSEKUNDENKARTE BIGINT,
  IPREISKLASSEHAUSLIEFERDIENST BIGINT,
  IPREISKLASSERESTAURLIEFERDIENST BIGINT,
  BWIR T_BOOLEAN_CHAR NOT NULL,
  IWIRPROZENT INTEGER,
  IBCKONTO INTEGER,
  BOLMWSTPFLICHTIG T_BOOLEAN_CHAR NOT NULL,
  SKREDITLIMITE VARCHAR(500) CHARACTER SET UTF8 COLLATE UTF8,
  IZAHLUNGSKONDITION BIGINT,
  BGESCHAEFTSPARTNERID BLOB SUB_TYPE 2 SEGMENT SIZE 1,
  BGESPERT T_BOOLEAN_CHAR NOT NULL,
  BOLEIGENERGESCHAEFTSORT T_BOOLEAN_CHAR NOT NULL,
  BOLMONATSRECHNUNGBEWILLIGT T_BOOLEAN_CHAR NOT NULL,
  BOLGELOESCHT T_BOOLEAN_CHAR NOT NULL,
  IZAHLUNGSTEXT BIGINT,
  DGEBURTSTAG DATE,
  DMONATSRECHNUNGLIMITE DECIMAL(12, 2) DEFAULT 0.0,
  BOLKEINEDETAILMONATSRECHNUNG T_BOOLEAN_CHAR NOT NULL,
  IRECHNUNGSADRBEIBESTELLUNG BIGINT,
  IVERTRETERPROZENTSATZ SMALLINT,
  IKUNDENNR VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8,
  SFIRMENNAME VARCHAR(134) CHARACTER SET UTF8 COLLATE UTF8,
  SNAME1 VARCHAR(74) CHARACTER SET UTF8 COLLATE UTF8,
  SVORNAME1 VARCHAR(46) CHARACTER SET UTF8 COLLATE UTF8,
  STRASSENR1 VARCHAR(70) CHARACTER SET UTF8 COLLATE UTF8,
  STELNR1 VARCHAR(76) CHARACTER SET UTF8 COLLATE UTF8,
  SNATELNR1 VARCHAR(68) CHARACTER SET UTF8 COLLATE UTF8,
  SEMAILADRESSE1 VARCHAR(74) CHARACTER SET UTF8 COLLATE UTF8,
  STRASSENR2 VARCHAR(26) CHARACTER SET UTF8 COLLATE UTF8,
  STELNR2 VARCHAR(74) CHARACTER SET UTF8 COLLATE UTF8,
  SEMAILADRESSE2 VARCHAR(72) CHARACTER SET UTF8 COLLATE UTF8,
  SFAXNR VARCHAR(46) CHARACTER SET UTF8 COLLATE UTF8,
  SPCKONTO VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8,
  SBANKKONTONR VARCHAR(42) CHARACTER SET UTF8 COLLATE UTF8,
  STEILNEHMERNR VARCHAR(42) CHARACTER SET UTF8 COLLATE UTF8,
  IMWSTNUMMER VARCHAR(36) CHARACTER SET UTF8 COLLATE UTF8,
  SLAENDERKENNZEICHEN VARCHAR(4) CHARACTER SET UTF8 COLLATE UTF8,
  SIBANNR VARCHAR(56) CHARACTER SET UTF8 COLLATE UTF8,
  SPOSTFACH VARCHAR(36) CHARACTER SET UTF8 COLLATE UTF8,
  SHOMEPAGE VARCHAR(72) CHARACTER SET UTF8 COLLATE UTF8,
  SESRZKBVORGABE VARCHAR(12) CHARACTER SET UTF8 COLLATE UTF8);


ALTER TABLE ADRESSSTAMM ADD PRIMARY KEY (ID);

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_ANREDE FOREIGN KEY (IANREDE) REFERENCES ANREDEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PKHAUSLDIENST FOREIGN KEY (IPREISKLASSEHAUSLIEFERDIENST) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PKKUNDENKARTE FOREIGN KEY (IPREISKLASSEKUNDENKARTE) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PKRESTLIEFER FOREIGN KEY (IPREISKLASSERESTAURLIEFERDIENST) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PLRORT2 FOREIGN KEY (IPLZORT2) REFERENCES ORTSANGABEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PLZORT FOREIGN KEY (IPLZORT1) REFERENCES ORTSANGABEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PREISKLASSERAMPE FOREIGN KEY (IPREISKLASSERAMPE) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_ZAHLUNGSKOND FOREIGN KEY (IZAHLUNGSKONDITION) REFERENCES ZAHLUNGSKONDITIONEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_ZAHLUNGSTEXT FOREIGN KEY (IZAHLUNGSTEXT) REFERENCES ZAHLUNGSTEXTKUNDE(ID) ON DELETE SET NULL ON UPDATE CASCADE;


SET TERM ^ ;

CREATE TRIGGER BI_ADRESSSTAMM_ID FOR ADRESSSTAMM
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(ADRESSSTAMM_ID_GEN, 1);
END^

SET TERM ; ^