/******************************************************************************/
/***          Generated by IBExpert 2022.3.4.1 22.08.2022 15:24:04          ***/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES UTF8;

SET CLIENTLIB 'D:\Portable_program\Firebird_server\Firebird_3_0_7_x32\fbclient.dll';

CREATE DATABASE 'localhost/31064:D:\Archive\Databases\test\TEST_2.FDB'
USER 'SYSDBA' PASSWORD 'cooladmin'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE SEQUENCE GEN_LAB_1_ID START WITH 0 INCREMENT BY 1;
ALTER SEQUENCE GEN_LAB_1_ID RESTART WITH 5;

CREATE SEQUENCE GEN_LAB_2_ID START WITH 0 INCREMENT BY 1;
ALTER SEQUENCE GEN_LAB_2_ID RESTART WITH 3;

CREATE SEQUENCE GEN_PEOPLE_ID START WITH 0 INCREMENT BY 1;
ALTER SEQUENCE GEN_PEOPLE_ID RESTART WITH 3;



/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/



CREATE TABLE LAB_1 (
    ID          INTEGER NOT NULL,
    FK_PEOLPLE  SMALLINT,
    FLD_DATE    TIMESTAMP DEFAULT current_timestamp NOT NULL,
    FLD_STR1    VARCHAR(10)
);

CREATE TABLE LAB_2 (
    ID         INTEGER NOT NULL,
    FK_PEOPLE  SMALLINT NOT NULL,
    FLD_DATE   TIMESTAMP DEFAULT current_timestamp NOT NULL,
    FLD_STR2   VARCHAR(10)
);

CREATE TABLE PEOPLE (
    ID   INTEGER NOT NULL,
    FIO  VARCHAR(50)
);

INSERT INTO PEOPLE (ID, FIO) VALUES (1, 'Иванов');
REINSERT (2, 'Петров');
REINSERT (3, 'Сидоров');


COMMIT WORK;

INSERT INTO LAB_1 (ID, FK_PEOLPLE, FLD_DATE, FLD_STR1) VALUES (1, 1, timestamp '2022-08-22 15:21:37', '111');
REINSERT (2, 1, timestamp '2022-08-16 00:00:00', '112');
REINSERT (3, 2, timestamp '2022-08-22 15:22:08', '222');
REINSERT (4, 2, timestamp '2022-08-03 00:00:00', '223');
REINSERT (5, 3, timestamp '2022-08-22 15:22:33', '333');


COMMIT WORK;

INSERT INTO LAB_2 (ID, FK_PEOPLE, FLD_DATE, FLD_STR2) VALUES (1, 3, timestamp '2022-08-22 15:22:48', 'aaaa');
REINSERT (2, 3, timestamp '2022-08-17 00:00:00', 'ssss');
REINSERT (3, 1, timestamp '2022-08-26 00:00:00', 'ffff');


COMMIT WORK;



/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE LAB_1 ADD CONSTRAINT PK_LAB_1 PRIMARY KEY (ID);
ALTER TABLE LAB_2 ADD CONSTRAINT PK_LAB_2 PRIMARY KEY (ID);
ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY (ID);


/******************************************************************************/
/***                              Foreign keys                              ***/
/******************************************************************************/

ALTER TABLE LAB_1 ADD CONSTRAINT FK_LAB_1_1 FOREIGN KEY (FK_PEOLPLE) REFERENCES PEOPLE (ID);
ALTER TABLE LAB_2 ADD CONSTRAINT FK_LAB_2_1 FOREIGN KEY (FK_PEOPLE) REFERENCES PEOPLE (ID);


/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: LAB_1_BI */
CREATE OR ALTER TRIGGER LAB_1_BI FOR LAB_1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_LAB_1_ID,1);
END
^

/* Trigger: LAB_2_BI */
CREATE OR ALTER TRIGGER LAB_2_BI FOR LAB_2
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_LAB_2_ID,1);
END
^

/* Trigger: PEOPLE_BI */
CREATE OR ALTER TRIGGER PEOPLE_BI FOR PEOPLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
    NEW.ID = GEN_ID(GEN_PEOPLE_ID,1);
END
^
SET TERM ; ^