SET NAMES WIN1251;

SET SQL DIALECT 3;

CONNECT 'сервер/порт:/база.gdb' USER 'SYSDBA' PASSWORD '*******';

SET AUTODDL ON;

/******************************************************************************/
/****              Creating tables (without computed fields)               ****/
/******************************************************************************/
CREATE TABLE USERS_CODE_FILIAL (
    USERS NUMERIC(18) NOT NULL,
    CODE_FILIAL VARCHAR(10) DEFAULT '' NOT NULL /* COLLATE WIN1251 - default */,
    TIMEP TIMESTAMP DEFAULT current_timestamp NOT NULL);


/******************************************************************************/
/****                          Creating triggers                           ****/
/******************************************************************************/
SET TERM ^ ;


CREATE TRIGGER TAI_USERS_LOG_CODE_FILIAL FOR USERS_LOG
INACTIVE
AFTER INSERT
POSITION 0 
AS
declare variable code_filial varchar(10);
declare variable id_users numeric(18,0);
begin
  if (user <> 'REPLICATOR') then
  begin
    select c.code_filial
      from config c
      into :code_filial;
    for select u.id
      from users u
      where u.login = current_user
      into :id_users
    do begin
      if (not exists (select uf.users
                        from users_code_filial uf
                        where uf.users = :id_users
                          and uf.code_filial = :code_filial)) then
      begin
        update or insert into users_code_filial(users, code_filial) values(:id_users, :code_filial);
      end
    end
  end
end
^

CREATE TRIGGER TAU_USERS_LOG_CODE_FILIAL FOR USERS_LOG
INACTIVE
AFTER UPDATE
POSITION 0 
AS
declare variable code_filial varchar(10);
declare variable id_users numeric(18,0);
begin
  if (user <> 'REPLICATOR') then
  begin
    select c.code_filial
      from config c
      into :code_filial;
    for select u.id
      from users u
      where u.login = current_user
      into :id_users
    do begin
      if (not exists (select uf.users
                        from users_code_filial uf
                        where uf.users = :id_users
                          and uf.code_filial = :code_filial)) then
      begin
        update or insert into users_code_filial(users, code_filial) values(:id_users, :code_filial);
      end
    end
  end
end
^


/******************************************************************************/
/****                   Creating primary key constraints                   ****/
/******************************************************************************/
SET TERM ; ^

RECONNECT;

ALTER TABLE USERS_CODE_FILIAL ADD CONSTRAINT PK_USERS_CODE_FILIAL PRIMARY KEY (USERS);


/******************************************************************************/
/****                     Granting missing privileges                      ****/
/******************************************************************************/