CREATE GENERATOR GEN_TRANSACTION_TRANS_ID;
SET GENERATOR GEN_TRANSACTION_TRANS_ID TO  0 ;

CREATE TABLE TRANSACTION (
    TRANS_ID INTEGER NOT NULL,
    TRANS_DATE DATE,
    ACC_NAME_ACC_ID INTEGER NOT NULL,
    ACC_TYPE_ACC_TYPE_ID INTEGER NOT NULL,
    CATSTRING_CATSTRING_ID INTEGER NOT NULL,
    TRANS_OPERTN_OPERTN_ID INTEGER NOT NULL,
    TRANS_TYPE_TRANS_TYPE_ID INTEGER NOT NULL);

ALTER TABLE TRANSACTION ADD CONSTRAINT PK_TRANSACTION PRIMARY KEY (TRANS_ID);

ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_1 FOREIGN KEY (ACC_NAME_ACC_ID) REFERENCES ACC_NAME (ACC_ID);
ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_2 FOREIGN KEY (ACC_TYPE_ACC_TYPE_ID) REFERENCES ACC_TYPE (ACC_TYPE_ID);
ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_4 FOREIGN KEY (TRANS_OPERTN_OPERTN_ID) REFERENCES TRANS_OPERTN (OPERTN_ID);
ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_5 FOREIGN KEY (CATSTRING_CATSTRING_ID) REFERENCES CATSTRING (CATSTRING_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TRANSACTION ADD CONSTRAINT FK_TRANSACTION_3 FOREIGN KEY (TRANS_TYPE_TRANS_TYPE_ID) REFERENCES TRANS_TYPE (TRANS_TYPE_ID);

CREATE TRIGGER TRANSACTION_BI FOR TRANSACTION
ACTIVE BEFORE INSERT POSITION  0 
AS
BEGIN
  IF (TRANS_ID IS NULL) THEN
    NEW.TRANS_ID = GEN_ID(GEN_TRANSACTION_TRANS_ID,  1 );
END^

execute ibeblock (
        HModel variant comment 'Current model handle',
        HTable variant comment 'Current table handle',
        HColumn variant comment 'Current column handle')
    returns (
        GenScript variant,
        TrgScript variant,
        ProcScript variant)
    as
    begin
      LF = ibec_CRLF();

      TblName = ibec_dbd_GetObjectProp(HTable, 'NAME');
      FldName = ibec_dbd_GetObjectProp(HColumn, 'NAME');

      GenName = 'GEN_' || TblName || '_' || FldName;
      GenName = ibec_AnsiUpperCase(GenName);

      TrgName = TblName || '_BI';
      TrgName = ibec_AnsiUpperCase(TrgName);

      GenScript = 'CREATE GENERATOR ' || GenName || ';' || LF ||
                  'SET GENERATOR ' || GenName || ' TO 0;' || LF;

      TrgScript = 'CREATE TRIGGER ' || TrgName || ' FOR ' || TblName || LF ||
             'ACTIVE BEFORE INSERT POSITION 0' || LF ||
             'AS' || LF ||
             'BEGIN' || LF ||
             '  IF (' || FldName || ' IS NULL) THEN' || LF ||
             '    NEW.' || FldName || ' = GEN_ID(' || GenName || ', 1);' || LF ||
             'END^' || LF || LF;
    end

CREATE TRIGGER ACCNAME_BI FOR ACCNAME
ACTIVE BEFORE INSERT POSITION  0 
AS
BEGIN
  IF (ACC_ID IS NULL) THEN
    NEW.ACC_ID = GEN_ID(GEN_ACCNAME_ACC_ID,  1 );
END^



/*******************************************************************************
The next statement causes the following error:

Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
ACCTYPE_ID.
At line 5, column 18.
*******************************************************************************/

/*******************************************************************************
The next statement causes the following error:

Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
ACCTYPE_ID.
At line 5, column 18.
*******************************************************************************/
CREATE TRIGGER ACCTYPE_BI FOR ACCTYPE
ACTIVE BEFORE INSERT POSITION  0 
AS
BEGIN
  IF (ACCTYPE_ID IS NULL) THEN
    NEW.ACCTYPE_ID = GEN_ID(GEN_ACCTYPE_ACCTYPE_ID,  1 );
END^

CREATE TABLE ACCTYPE (
    ACCTYPE_ID BIGINT NOT NULL,
    ACCTYPE VARCHAR( 15 ));