Изменения представления — различия между версиями

Материал из wiki.standart-n.ru
Перейти к: навигация, поиск
(→‎Установка)
 
(не показано 11 промежуточных версии 2 участников)
Строка 3: Строка 3:
 
*изменяет view,таблицы<br>
 
*изменяет view,таблицы<br>
 
пока что без добавления join'ов во view - там всё не так однозначно<br><br>
 
пока что без добавления join'ов во view - там всё не так однозначно<br><br>
−
Пример:
+
 
 +
==Пример==
 
<PRE>  
 
<PRE>  
−
--создание генератора
+
/* создание генератора */
 
execute procedure PR_ALTER_VIEW('','','gen1111','generator');
 
execute procedure PR_ALTER_VIEW('','','gen1111','generator');
−
--создание индекса
+
/* создание или добавление в конец триггера */
 +
execute procedure pr_alter_view('SELECTMODES','SELECTMODES_BI','--test','TRIGGER','BEFORE INSERT')
 +
/* создание индекса */
 
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100500','index');
 
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100500','index');
 
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100501','DESCENDING INDEX');
 
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100501','DESCENDING INDEX');
 
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100502','ASCENDING INDEX');
 
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100502','ASCENDING INDEX');
−
--первичный ключ
+
/* первичный ключ */
 
execute procedure PR_ALTER_VIEW('TEMP_MNN','ID','','PRIMARY KEY');
 
execute procedure PR_ALTER_VIEW('TEMP_MNN','ID','','PRIMARY KEY');
−
--изменение таблицы
+
/* изменение таблицы */
 
execute procedure PR_ALTER_VIEW('DOC_DETAIL','MGN_ID1','DM_DATETIME','TABLE');
 
execute procedure PR_ALTER_VIEW('DOC_DETAIL','MGN_ID1','DM_DATETIME','TABLE');
−
--изменение вьюхи
+
/* изменение вьюхи */
 
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','VIEW');
 
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','VIEW');
 
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');
 
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');
 
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','');
 
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','');
−
--создание домена
+
/* создание домена */
 
execute procedure PR_ALTER_VIEW('DM_BIGINT1','','BIGINT','DOMAIN');
 
execute procedure PR_ALTER_VIEW('DM_BIGINT1','','BIGINT','DOMAIN');
  
 
</PRE>
 
</PRE>
−
Установка:
+
 
 +
==Установка==
 
<PRE>
 
<PRE>
  
 
SET TERM ^ ;
 
SET TERM ^ ;
−
--создание доменов если их нет
 
−
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_ID','','BIGINT NOT NULL','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_STATUS','','INTEGER','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_DATETIME','','TIMESTAMP','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_TEXT1024','','VARCHAR(1024) CHARACTER SET WIN1251 COLLATE WIN1251','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_DOUBLE','','DOUBLE PRECISION','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
 
−
execute procedure PR_ALTER_VIEW('DM_ID_NULL','','BIGINT','DOMAIN');^
 
  
−
--таблица ELNAKLS
+
create or alter procedure PR_ALTER_VIEW (
−
EXECUTE BLOCK AS BEGIN
+
    VW_NAME DM_TEXT,
−
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ELNAKLS')) then
+
    NEW_VW_FIELD DM_TEXT,
−
execute statement 'CREATE TABLE ELNAKLS(FILENAME DM_TEXT1024);';
+
    NEW_VW_VALUE DM_TEXT,
−
END^
+
    NEW_VW_JOIN DM_TEXT = null,
 +
    NEW_EVENT DM_TEXT = null)
 +
as
 +
declare variable I DM_ID;
 +
declare variable VW_BODY DM_TEXT_BIG;
 +
declare variable VW_FIELDS DM_TEXT_BIG;
 +
declare variable J DM_ID;
 +
declare variable STR DM_TEXT_BIG;
 +
declare variable L integer;
 +
begin
 +
  if ((:NEW_VW_JOIN is null) or (:NEW_VW_JOIN = '') or (upper(:NEW_VW_JOIN) = 'VIEW'))  then
 +
  begin
 +
    select list(trim(RDB$FIELD_NAME))
 +
    from (select RC.RDB$FIELD_NAME
 +
            from RDB$RELATION_FIELDS RC
 +
            where RC.RDB$RELATION_NAME = upper(:VW_NAME)
 +
            order by RC.RDB$FIELD_POSITION)
 +
    into :VW_FIELDS;
 +
    select R.RDB$VIEW_SOURCE
 +
    from RDB$RELATIONS R
 +
    where R.RDB$RELATION_NAME = upper(:VW_NAME)
 +
    into :VW_BODY;
  
−
--таблица EXT_DATA
+
    I = 0;
−
EXECUTE BLOCK AS BEGIN
+
    J = 0;
−
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'EXT_DATA')) then
+
    if ((select count(*)
−
execute statement 'CREATE TABLE EXT_DATA(ID DM_ID NOT NULL);';
+
            from RDB$RELATION_FIELDS RF
−
END^
+
            where RF.RDB$RELATION_NAME = upper(:VW_NAME) and
−
execute procedure PR_ALTER_VIEW('EXT_DATA','SNAME','DM_TEXT1024','TABLE');^
+
            RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then
−
execute procedure PR_ALTER_VIEW('EXT_DATA','ORIG_NAME','DM_TEXT1024','TABLE');^
+
    begin
−
execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_SOURCE','DM_TEXT1024','TABLE'); ^
+
        while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do
−
execute procedure PR_ALTER_VIEW('EXT_DATA','QUANT','DM_DOUBLE','TABLE'); ^
+
        begin
−
execute procedure PR_ALTER_VIEW('EXT_DATA','PRICE','DM_DOUBLE','TABLE');^
+
            J = position('FROM', UPPER(:VW_BODY), :J + 1);
−
execute procedure PR_ALTER_VIEW('EXT_DATA','SUMMA','DM_DOUBLE','TABLE');^
+
            I = :I + 1;
−
execute procedure PR_ALTER_VIEW('EXT_DATA','SDOC_TYPE','DM_TEXT1024','TABLE');^
+
        end
−
execute procedure PR_ALTER_VIEW('EXT_DATA','COMMITDATE','DM_DATETIME','TABLE'); ^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','DOC_TYPE','DM_STATUS','TABLE'); ^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','STATUS','DM_STATUS','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_ID','DM_ID_NULL','TABLE'); ^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','INSERTDT','DM_DATETIME','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('','','GEN_EXT_DATA_ID','generator');^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','ID','EXT_DATA_IDX1','INDEX');^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','SNAME','EXT_DATA_IDX2','INDEX');^
+
−
execute procedure PR_ALTER_VIEW('EXT_DATA','MGN_ID','EXT_DATA_IDX3','INDEX'); ^
+
  
−
CREATE OR ALTER TRIGGER EXT_DATA_BI FOR EXT_DATA
+
        if (:I = 1) then /* один основной from, т.е. скрипт без подзапросов */
−
ACTIVE BEFORE INSERT POSITION 0
+
        begin
−
as
+
            execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
−
begin
+
        end
−
  new.insertdt = 'now';
+
        else
−
  if (new.id is null) then
+
        begin /* есть подзапросы - нужно распарсить и найти основной from */
−
    new.id = gen_id(gen_ext_data_id,1);
+
            J = 0;
−
end;^
+
            while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do /* находим сколько всего from'ов в скрипте */
 +
            begin
 +
                I = 0;
 +
                J = position('FROM', UPPER(:VW_BODY), :J + 1);
 +
                L = 0;
 +
                STR = substring(:VW_BODY from L + 1 for :J - 1);
 +
                while (position('(', STR, :L + 1) <> 0) do /* считаем количество открывающих скобок */
 +
                begin
 +
                    L = position('(', STR, :L + 1);
 +
                    I = :I + 1;
 +
                end
  
−
--добавляем колонки в таблицу WARES
+
                L = 0;
−
execute procedure PR_ALTER_VIEW('WARES','SNAME','DM_TEXT1024','TABLE');^
+
                while (position(')', STR, :L + 1) <> 0) do /* считаем количество закрывающих скобок */
−
execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','DM_TEXT1024','TABLE');^
+
                begin
−
execute procedure PR_ALTER_VIEW('WARES','MGN_ID','DM_ID_NULL','TABLE'); ^
+
                    L = position(')', STR, :L + 1);
−
execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^
+
                    I = :I - 1;
−
execute procedure PR_ALTER_VIEW('WARES','MGN_SOURCE','DM_BLOBTEXT','TABLE');^
+
                end
−
--индексы в таблицу wares
+
−
execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX3','INDEX');^
+
−
execute procedure PR_ALTER_VIEW('WARES','MGN_NAME','WARES_IDX4','INDEX');^
+
−
execute procedure PR_ALTER_VIEW('WARES','MGN_ID','WARES_IDX7','INDEX'); ^
+
  
−
CREATE OR ALTER TRIGGER WARES_BU_MGN FOR WARES
+
                if (:I = 0) then /* если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок */
−
ACTIVE BEFORE UPDATE POSITION 0
+
                    execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
−
AS
+
            end
−
begin
+
        end
−
   if (new.name_id <> old.name_id) then
+
    end
 +
  end
 +
   if (upper(:NEW_VW_JOIN) = 'TABLE') then
 
   begin
 
   begin
−
     new.mgn_name = null;
+
     if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = :VW_NAME and rf.RDB$FIELD_NAME = :NEW_VW_FIELD)) then
−
     new.mgn_id = 0;
+
        execute statement 'ALTER '||:NEW_VW_JOIN||' '||:VW_NAME||' ADD '||:NEW_VW_FIELD||' '||:NEW_VW_VALUE||';';
 +
  end
 +
  if ((upper(:NEW_VW_JOIN) = 'INDEX') or (upper(:NEW_VW_JOIN) = 'DESCENDING INDEX') or (upper(:NEW_VW_JOIN) = 'ASCENDING INDEX'))  then
 +
  begin
 +
     if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME=:NEW_VW_VALUE)) then
 +
        execute statement 'CREATE '||:NEW_VW_JOIN||' '||:NEW_VW_VALUE||' ON '||:VW_NAME||' ('||:NEW_VW_FIELD||');';
 +
  end
 +
  if (upper(:NEW_VW_JOIN) = 'GENERATOR') then
 +
  begin
 +
    if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = :NEW_VW_VALUE)) then
 +
        execute statement 'CREATE GENERATOR '||:NEW_VW_VALUE||';';
 +
  end
 +
  if (upper(:NEW_VW_JOIN) = 'PRIMARY KEY') then
 +
  begin
 +
    if (not exists(SELECT s.rdb$field_name FROM rdb$index_segments AS s LEFT JOIN rdb$relation_constraints AS rc ON (rc.rdb$index_name = s.rdb$index_name) WHERE rc.rdb$constraint_name = 'PK_'||:VW_NAME AND rc.rdb$constraint_type = 'PRIMARY KEY')) then
 +
        execute statement 'ALTER TABLE '||:VW_NAME||' ADD CONSTRAINT PK_'||:VW_NAME||' PRIMARY KEY ('||:NEW_VW_FIELD||');';
 +
  end
 +
  if (upper(:NEW_VW_JOIN) = 'DOMAIN') then
 +
  begin
 +
    if (not exists(select f.rdb$field_name, t.rdb$type_name from    rdb$fields f, rdb$types t where f.rdb$field_name=:VW_NAME and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then
 +
        execute statement 'CREATE DOMAIN '||:VW_NAME||' AS '||:NEW_VW_VALUE||';';
 
   end
 
   end
−
  new.sname = (select svalue from vals where id = new.name_id);
 
−
end;^
 
−
CREATE OR ALTER TRIGGER WARES_BI_SNAME FOR WARES
 
−
ACTIVE BEFORE INSERT POSITION 0
 
−
AS
 
−
begin
 
−
    new.sname = (select svalue from vals where id = new.name_id);
 
−
end;^
 
  
−
-- таблица ORDER_DATA
+
  if (upper(:NEW_VW_JOIN) = 'TRIGGER') then
−
EXECUTE BLOCK AS BEGIN
+
  begin
−
if (not exists(select 1 from rdb$relations where rdb$relation_name = 'ORDER_DATA')) then
+
    if ((select r.rdb$trigger_source from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD) not containing :NEW_VW_VALUE) then
−
execute statement 'CREATE TABLE ORDER_DATA (ID DM_ID NOT NULL);';
+
    execute statement '
−
END^
+
        CREATE OR ALTER TRIGGER '||:NEW_VW_FIELD||' FOR '||:VW_NAME||'
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','','PRIMARY KEY');^
+
        ACTIVE '||:NEW_EVENT||' POSITION 0
 +
        '||
 +
        (select substring(r.rdb$trigger_source from 1 for CHAR_LENGTH(r.rdb$trigger_source)-3) from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD)||:NEW_VW_VALUE
 +
        ||'
 +
        end;';
 +
  end
 +
end^
  
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','DM_TEXT1024','TABLE');^
+
SET TERM ; ^
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ORDER_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','SHORT_ORDER_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','OPTIMAL_ORDER_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ASSORT_ORDER_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','REALQUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_DATE','DM_DATETIME','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PRIHOD_PRICE','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_WEEK_SUM','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_2WEEKS_SUM','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_PERIOD_SUM','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_MONTH_SUM','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','SEASON_SUM','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','RASHOD_DOC_QUANT','DM_STATUS','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','PRIHOD_DOC_QUANT','DM_STATUS','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_DATE','DM_DATETIME','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ELNAKL_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_PRIHOD_DOC_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_OZ_PRICE','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','LAST_RASHOD_DATE','DM_DATETIME','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ACTIVE_ORDER_QUANT','DM_DOUBLE','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_ID','DM_ID_NULL','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','BAD_FLAG','DM_STATUS','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','INSERTDT','DM_DATETIME','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^
+
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','G$PROFILE_ID','DM_ID_NULL','TABLE');^
+
  
−
execute procedure PR_ALTER_VIEW('','','GEN_ORDER_DATA_ID','generator');^
 
−
 
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX1','INDEX');^
 
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','MGN_NAME','ORDER_DATA_IDX2','INDEX');^
 
−
execute procedure PR_ALTER_VIEW('ORDER_DATA','ID','ORDER_DATA_IDX3','INDEX');^
 
−
 
−
CREATE OR ALTER TRIGGER ORDER_DATA_BI FOR ORDER_DATA
 
−
ACTIVE BEFORE INSERT POSITION 0
 
−
as
 
−
begin
 
−
  if (new.id is null) then
 
−
    new.id = gen_id(gen_order_data_id,1);
 
−
end;^
 
−
 
−
--
 
−
create or alter procedure PR_INSTALL_UPDPR_REPARWARES
 
−
as
 
−
begin
 
−
 
−
  if ((
 
−
    select
 
−
    upper(rf.rdb$field_source)
 
−
    from RDB$RELATION_FIELDS rf
 
−
    where 1=1
 
−
    and rf.rdb$field_name='ID'
 
−
    and rf.rdb$relation_name='WARES')='DM_UUID'
 
−
  ) then
 
−
  EXECUTE STATEMENT ('
 
−
    create or alter procedure UPDPR_REPARWARES
 
−
    returns (
 
−
        ID DM_TEXT)
 
−
    as
 
−
    declare variable NAME_ID DM_UUID;
 
−
    declare variable IZG_ID DM_TEXT;
 
−
    declare variable COUNTRY_ID DM_TEXT;
 
−
    declare variable ORIG_CODE DM_TEXT;
 
−
    declare variable ORIG_NAME_ID DM_TEXT;
 
−
    declare variable ORIG_IZG_ID DM_TEXT;
 
−
    declare variable ORIG_COUNTRY_ID DM_TEXT;
 
−
    declare variable T_ID DM_UUID;
 
−
    begin
 
−
    --BASE WITH GLOBAL ID
 
−
    delete from DOC_DETAIL_ACTIVE_LOG;
 
−
    for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares
 
−
    group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID
 
−
    having count(1)>1
 
−
    into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do
 
−
    begin
 
−
    for
 
−
    select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE
 
−
    and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id into :t_id do
 
−
    begin
 
−
    update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id;
 
−
    update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id;
 
−
    -- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id;
 
−
    update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id;
 
−
    update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id;
 
−
    if ((
 
−
      select
 
−
      count(1)
 
−
      from RDB$RELATIONS r
 
−
      where 1=1
 
−
      and upper(r.rdb$relation_name)=''DOC_DETAIL_ACTIVE_TREB''
 
−
      )>0
 
−
    ) THEN
 
−
      EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id);
 
−
    if ((
 
−
      select
 
−
      count(1)
 
−
      from RDB$RELATIONS r
 
−
      where 1=1
 
−
      and upper(r.rdb$relation_name)=''OUT$ZAKAZ''
 
−
      )>0
 
−
    ) THEN
 
−
      EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id);
 
−
   
 
−
    update PARTS set ware_id=:id where ware_id=:t_id;
 
−
    update PARTS_LOG set ware_id=:id where ware_id=:t_id;
 
−
    update WAREBASE set ware_id=:id where ware_id=:t_id;
 
−
    update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id;
 
−
    update WAREBASENAMEID set ware_id=:id where ware_id=:t_id;
 
−
    delete from wares where id=:t_id;
 
−
    end
 
−
    --    suspend;
 
−
    /*    delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE
 
−
    and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id;
 
−
    suspend;
 
−
    */
 
−
    end
 
−
    --  suspend;
 
−
    end;
 
−
  ');
 
−
else
 
−
  EXECUTE STATEMENT ('
 
−
    create or alter procedure UPDPR_REPARWARES
 
−
    returns (
 
−
        ID DM_TEXT)
 
−
    as
 
−
    declare variable NAME_ID DM_ID;
 
−
    declare variable IZG_ID DM_TEXT;
 
−
    declare variable COUNTRY_ID DM_TEXT;
 
−
    declare variable ORIG_CODE DM_TEXT;
 
−
    declare variable ORIG_NAME_ID DM_TEXT;
 
−
    declare variable ORIG_IZG_ID DM_TEXT;
 
−
    declare variable ORIG_COUNTRY_ID DM_TEXT;
 
−
    declare variable T_ID DM_ID_NULL;
 
−
    begin
 
−
    --BASE WITHOUT GLOBAL ID
 
−
    delete from DOC_DETAIL_ACTIVE_LOG;
 
−
    for select min(id), NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID from wares
 
−
    group by NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID
 
−
    having count(1)>1
 
−
    into id, NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID do
 
−
    begin
 
−
    for
 
−
    select id from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE
 
−
    and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id into :t_id do
 
−
    begin
 
−
    update DOC_DETAIL_ACTIVE set ware_id=:id where ware_id=:t_id;
 
−
    update DOC_DETAIL_ACTIVE_CANCELED set ware_id=:id where ware_id=:t_id;
 
−
    -- update DOC_DETAIL_ACTIVE_LOG set ware_id=:id where ware_id=:t_id;
 
−
    update DOC_DETAIL_ACTIVE_NAMEID set ware_id=:id where ware_id=:t_id;
 
−
    if ((
 
−
      select
 
−
      count(1)
 
−
      from RDB$RELATIONS r
 
−
      where 1=1
 
−
      and upper(r.rdb$relation_name)=''DOC_DETAIL_ACTIVE_TREB''
 
−
      )>0
 
−
    ) THEN
 
−
      EXECUTE STATEMENT (''update DOC_DETAIL_ACTIVE_TREB set ware_id=''||:id||'' where ware_id=''||:t_id);
 
−
    if ((
 
−
      select
 
−
      count(1)
 
−
      from RDB$RELATIONS r
 
−
      where 1=1
 
−
      and upper(r.rdb$relation_name)=''OUT$ZAKAZ''
 
−
      )>0
 
−
    ) THEN
 
−
      EXECUTE STATEMENT (''update OUT$ZAKAZ set ware_id=''||:id||'' where ware_id=''||:t_id);
 
−
    update DOC_DETAIL_DELETED set ware_id=:id where ware_id=:t_id;
 
−
    update PARTS set ware_id=:id where ware_id=:t_id;
 
−
    update PARTS_LOG set ware_id=:id where ware_id=:t_id;
 
−
    update WAREBASE set ware_id=:id where ware_id=:t_id;
 
−
    update WAREBASEFOLDERS set ware_id=:id where ware_id=:t_id;
 
−
    update WAREBASENAMEID set ware_id=:id where ware_id=:t_id;
 
−
    delete from wares where id=:t_id;
 
−
    end
 
−
    --    suspend;
 
−
    /*    delete from wares where NAME_ID=:NAME_ID and IZG_ID=:IZG_ID and COUNTRY_ID=:COUNTRY_ID and ORIG_CODE=:ORIG_CODE
 
−
    and ORIG_NAME_ID=:ORIG_NAME_ID and ORIG_IZG_ID=:ORIG_IZG_ID and ORIG_COUNTRY_ID=:ORIG_COUNTRY_ID and id<>:id;
 
−
    suspend;
 
−
    */
 
−
    end
 
−
    --  suspend;
 
−
    end;
 
−
  ');
 
−
end;^
 
 
/* Existing privileges on this procedure */
 
/* Existing privileges on this procedure */
−
GRANT EXECUTE ON PROCEDURE PR_INSTALL_UPDPR_REPARWARES TO SYSDBA;^
 
−
execute procedure PR_INSTALL_UPDPR_REPARWARES;^
 
−
DROP PROCEDURE PR_INSTALL_UPDPR_REPARWARES;^
 
  
−
/* PR_GET_WARE */
+
GRANT EXECUTE ON PROCEDURE PR_ALTER_VIEW TO SYSDBA;
−
create or alter procedure PR_INSTALL_PR_GET_WARE
+
−
as
+
−
begin
+
−
  if ((
+
−
    select
+
−
    upper(rf.rdb$field_source)
+
−
    from RDB$RELATION_FIELDS rf
+
−
    where 1=1
+
−
    and rf.rdb$field_name='ID'
+
−
    and rf.rdb$relation_name='WARES')='DM_UUID'
+
−
  ) then
+
−
  EXECUTE STATEMENT ('
+
−
    create or alter procedure PR_GET_WARE (
+
−
        SNAME type of DM_TEXT,
+
−
        SIZG type of DM_TEXT,
+
−
        SCOUNTRY type of DM_TEXT,
+
−
        ORIG_CODE type of DM_TEXT,
+
−
        SORIG_NAME type of DM_TEXT,
+
−
        SORIG_IZG type of DM_TEXT,
+
−
        SORIG_COUNTRY type of DM_TEXT,
+
−
        BARCODE type of DM_TEXT,
+
−
        Z_ID type of DM_ID,
+
−
        SKLAD_ID DM_TEXT,
+
−
        ALTTYPE DM_STATUS,
+
−
        MNN DM_TEXT = '''')
+
−
    returns (
+
−
        W_ID type of DM_UUID_NULL)
+
−
    as
+
−
    declare variable OLD_BARCODE DM_TEXT1024;
+
−
    declare variable NAME_ID type of DM_UUID_NULL;
+
−
    declare variable IZG_ID type of DM_UUID_NULL;
+
−
    declare variable COUNTRY_ID type of DM_UUID_NULL;
+
−
    declare variable ORIG_NAME_ID type of DM_UUID_NULL;
+
−
    declare variable ORIG_IZG_ID type of DM_UUID_NULL;
+
−
    declare variable ORIG_COUNTRY_ID type of DM_UUID_NULL;
+
−
    begin
+
−
    --BASE WITH GLOBAL ID
+
−
    if (orig_code is null) then orig_code='''';
+
−
    select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id;
+
−
    select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
+
−
    select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
+
−
    select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
+
−
    select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
+
−
    select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
+
−
    select id, barcode from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
+
−
    ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
+
−
    ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id into :w_id, :old_barcode;
+
−
    if (w_id is null) then
+
−
    begin
+
−
    --    exception EX_WRONG_OPER;
+
−
    --w_id=gen_id(gen_wares_id,1);
+
−
    w_id=UUID_TO_CHAR(GEN_UUID());
+
−
    insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID)
+
−
    values
+
−
    (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID);
+
−
    end
+
−
    else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
+
−
    suspend;
+
−
    end;
+
−
  ');
+
−
else
+
−
  EXECUTE STATEMENT ('
+
−
create or alter procedure PR_GET_WARE (
+
−
        SNAME type of DM_TEXT,
+
−
        SIZG type of DM_TEXT,
+
−
        SCOUNTRY type of DM_TEXT,
+
−
        ORIG_CODE type of DM_TEXT,
+
−
        SORIG_NAME type of DM_TEXT,
+
−
        SORIG_IZG type of DM_TEXT,
+
−
        SORIG_COUNTRY type of DM_TEXT,
+
−
        BARCODE type of DM_TEXT,
+
−
        Z_ID type of DM_ID,
+
−
        SKLAD_ID DM_TEXT,
+
−
        ALTTYPE DM_STATUS,
+
−
        MNN DM_TEXT = '''')
+
−
    returns (
+
−
        W_ID type of DM_ID)
+
−
    as
+
−
    declare variable OLD_BARCODE DM_TEXT1024;
+
−
    declare variable NAME_ID type of DM_ID;
+
−
    declare variable IZG_ID type of DM_ID;
+
−
    declare variable COUNTRY_ID type of DM_ID;
+
−
    declare variable ORIG_NAME_ID type of DM_ID;
+
−
    declare variable ORIG_IZG_ID type of DM_ID;
+
−
    declare variable ORIG_COUNTRY_ID type of DM_ID;
+
−
    begin
+
−
    --BASE WITHOUT GLOBAL ID
+
−
    if (orig_code is null) then orig_code='''';
+
−
    select val_id from pr_getval_id(:sname,0,:alttype,:mnn) into :name_id;
+
−
    select val_id from pr_getval_id(:sizg,3,:alttype) into :izg_id;
+
−
    select val_id from pr_getval_id(:scountry,2,:alttype) into :country_id;
+
−
    select val_id from pr_getval_id(:sorig_name,1,:alttype) into :orig_name_id;
+
−
    select val_id from pr_getval_id(:sorig_izg,6,:alttype) into :orig_izg_id;
+
−
    select val_id from pr_getval_id(:sorig_country,5,:alttype) into :orig_country_id;
+
−
    select id, barcode from wares where NAME_ID=:name_id and IZG_ID=:izg_id and COUNTRY_ID=:country_id and
+
−
    ORIG_CODE=:orig_code and ORIG_NAME_ID=:orig_name_id and
+
−
    ORIG_IZG_ID=:orig_izg_id and ORIG_COUNTRY_ID=:orig_country_id into :w_id, :old_barcode;
+
−
    if (w_id is null) then
+
−
    begin
+
−
    --    exception EX_WRONG_OPER;
+
−
    w_id=gen_id(gen_wares_id,1);
+
−
    --w_id=UUID_TO_CHAR(GEN_UUID());
+
−
    insert into wares (ID,NAME_ID,IZG_ID,COUNTRY_ID,ORIG_CODE,ORIG_NAME_ID,ORIG_IZG_ID,ORIG_COUNTRY_ID,BARCODE,Z_ID,SKLAD_ID)
+
−
    values
+
−
    (:W_ID,:NAME_ID,:IZG_ID,:COUNTRY_ID,:ORIG_CODE,:ORIG_NAME_ID,:ORIG_IZG_ID,:ORIG_COUNTRY_ID,:BARCODE,:Z_ID,:SKLAD_ID);
+
−
    end
+
−
    else if (old_barcode <> barcode) then update wares set barcode = :barcode where id = :w_id;
+
−
    suspend;
+
−
    end;
+
−
  ');
+
−
end;^
+
−
/* Existing privileges on this procedure */
+
−
GRANT EXECUTE ON PROCEDURE PR_INSTALL_PR_GET_WARE TO SYSDBA;^
+
−
execute procedure PR_INSTALL_PR_GET_WARE;^
+
−
DROP PROCEDURE PR_INSTALL_PR_GET_WARE;^
+
−
 
+
−
EXECUTE BLOCK AS BEGIN
+
−
if(not exists(select id from DOC_TYPES where id = 36)) then
+
−
execute statement 'INSERT INTO DOC_TYPES (ID, CAPTION, BASE_TYPE, STATINI, REPORTS, VTYPE) VALUES (36, ''Заказ оптовику'', 4, ''
+
−
[main]
+
−
koef=0
+
−
 
+
−
[freereport]
+
−
enabled=1
+
−
caption=Печать пакета документов
+
−
tmplpath=zakaz_opt.cffr3
+
−
 
+
−
[dbf]
+
−
inipath=dbf.ini
+
−
'', NULL, 1);';
+
−
END;^
+
−
 
+
−
--колонки в вьюхи
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_NAME','w.mgn_name');^
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_ID','w.mgn_id');^
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^
+
−
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_NAME','w.mgn_name');^
+
−
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');^
+
−
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_SOURCE','cast(w.mgn_source as dm_text_big)');^
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_NAME','(select mgn_name from wares where id = da.ware_id) as mgn_name');^
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_ID','(select mgn_id from wares where id = da.ware_id) as mgn_id');^
+
−
execute procedure PR_ALTER_VIEW('VW_DOC_DETAIL_ACTIVE','MGN_SOURCE','(select mgn_source from wares where id = da.ware_id) as mgn_source');^
+
−
execute procedure PR_ALTER_VIEW('VW_WAREBASE','MGN_NAME','(select mgn_name from wares where id = wb.ware_id)');^
+
−
 
+
−
SET TERM ; ^
+
  
 
</PRE>
 
</PRE>

Текущая версия на 15:54, 14 июля 2016

Изменение VIEW

  • изменяет view,таблицы

пока что без добавления join'ов во view - там всё не так однозначно

Пример

 
/* создание генератора */
execute procedure PR_ALTER_VIEW('','','gen1111','generator');
/* создание или добавление в конец триггера */
execute procedure pr_alter_view('SELECTMODES','SELECTMODES_BI','--test','TRIGGER','BEFORE INSERT')
/* создание индекса */
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100500','index');
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100501','DESCENDING INDEX');
execute procedure PR_ALTER_VIEW('wares','name_id','wares_idx100502','ASCENDING INDEX');
/* первичный ключ */
execute procedure PR_ALTER_VIEW('TEMP_MNN','ID','','PRIMARY KEY');
/* изменение таблицы */
execute procedure PR_ALTER_VIEW('DOC_DETAIL','MGN_ID1','DM_DATETIME','TABLE');
/* изменение вьюхи */
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','VIEW');
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id');
execute procedure PR_ALTER_VIEW('VW_WARES','MGN_ID','w.mgn_id','');
/* создание домена */
execute procedure PR_ALTER_VIEW('DM_BIGINT1','','BIGINT','DOMAIN');

Установка


SET TERM ^ ;

create or alter procedure PR_ALTER_VIEW (
    VW_NAME DM_TEXT,
    NEW_VW_FIELD DM_TEXT,
    NEW_VW_VALUE DM_TEXT,
    NEW_VW_JOIN DM_TEXT = null,
    NEW_EVENT DM_TEXT = null)
as
declare variable I DM_ID;
declare variable VW_BODY DM_TEXT_BIG;
declare variable VW_FIELDS DM_TEXT_BIG;
declare variable J DM_ID;
declare variable STR DM_TEXT_BIG;
declare variable L integer;
begin
  if ((:NEW_VW_JOIN is null) or (:NEW_VW_JOIN = '') or (upper(:NEW_VW_JOIN) = 'VIEW'))  then
  begin
    select list(trim(RDB$FIELD_NAME))
    from (select RC.RDB$FIELD_NAME
            from RDB$RELATION_FIELDS RC
            where RC.RDB$RELATION_NAME = upper(:VW_NAME)
            order by RC.RDB$FIELD_POSITION)
    into :VW_FIELDS;
    select R.RDB$VIEW_SOURCE
    from RDB$RELATIONS R
    where R.RDB$RELATION_NAME = upper(:VW_NAME)
    into :VW_BODY;

    I = 0;
    J = 0;
    if ((select count(*)
            from RDB$RELATION_FIELDS RF
            where RF.RDB$RELATION_NAME = upper(:VW_NAME) and
             RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then
    begin
        while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do
        begin
            J = position('FROM', UPPER(:VW_BODY), :J + 1);
            I = :I + 1;
        end

        if (:I = 1) then /* один основной from, т.е. скрипт без подзапросов */
        begin
            execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
        end
        else
        begin /* есть подзапросы - нужно распарсить и найти основной from */
            J = 0;
            while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do /* находим сколько всего from'ов в скрипте */
            begin
                I = 0;
                J = position('FROM', UPPER(:VW_BODY), :J + 1);
                L = 0;
                STR = substring(:VW_BODY from L + 1 for :J - 1);
                while (position('(', STR, :L + 1) <> 0) do /* считаем количество открывающих скобок */
                begin
                    L = position('(', STR, :L + 1);
                    I = :I + 1;
                end

                L = 0;
                while (position(')', STR, :L + 1) <> 0) do /* считаем количество закрывающих скобок */
                begin
                    L = position(')', STR, :L + 1);
                    I = :I - 1;
                end

                if (:I = 0) then /* если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок */
                    execute statement 'CREATE OR ALTER VIEW ' || upper(:VW_NAME) || '( ' || :VW_FIELDS || ',' || upper(:NEW_VW_FIELD) || ' ) as ' || substring(:VW_BODY from 1 for :J - 1) || ' ,' || upper(:NEW_VW_VALUE) || ' ' || substring(:VW_BODY from :J for char_length(:VW_BODY)) || '';
            end
        end
    end
  end
  if (upper(:NEW_VW_JOIN) = 'TABLE') then
  begin
    if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = :VW_NAME and rf.RDB$FIELD_NAME = :NEW_VW_FIELD)) then
        execute statement 'ALTER '||:NEW_VW_JOIN||' '||:VW_NAME||' ADD '||:NEW_VW_FIELD||' '||:NEW_VW_VALUE||';';
  end
  if ((upper(:NEW_VW_JOIN) = 'INDEX') or (upper(:NEW_VW_JOIN) = 'DESCENDING INDEX') or (upper(:NEW_VW_JOIN) = 'ASCENDING INDEX'))  then
  begin
    if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME=:NEW_VW_VALUE)) then
        execute statement 'CREATE '||:NEW_VW_JOIN||' '||:NEW_VW_VALUE||' ON '||:VW_NAME||' ('||:NEW_VW_FIELD||');';
  end
  if (upper(:NEW_VW_JOIN) = 'GENERATOR') then
  begin
    if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = :NEW_VW_VALUE)) then
        execute statement 'CREATE GENERATOR '||:NEW_VW_VALUE||';';
  end
  if (upper(:NEW_VW_JOIN) = 'PRIMARY KEY') then
  begin
    if (not exists(SELECT s.rdb$field_name FROM rdb$index_segments AS s LEFT JOIN rdb$relation_constraints AS rc ON (rc.rdb$index_name = s.rdb$index_name) WHERE rc.rdb$constraint_name = 'PK_'||:VW_NAME AND rc.rdb$constraint_type = 'PRIMARY KEY')) then
        execute statement 'ALTER TABLE '||:VW_NAME||' ADD CONSTRAINT PK_'||:VW_NAME||' PRIMARY KEY ('||:NEW_VW_FIELD||');';
  end
  if (upper(:NEW_VW_JOIN) = 'DOMAIN') then
  begin
    if (not exists(select f.rdb$field_name, t.rdb$type_name from     rdb$fields f, rdb$types t where f.rdb$field_name=:VW_NAME and f.rdb$field_type=t.rdb$type and t.rdb$field_name='RDB$FIELD_TYPE')) then
        execute statement 'CREATE DOMAIN '||:VW_NAME||' AS '||:NEW_VW_VALUE||';';
  end

  if (upper(:NEW_VW_JOIN) = 'TRIGGER') then
  begin
    if ((select r.rdb$trigger_source from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD) not containing :NEW_VW_VALUE) then
    execute statement '
        CREATE OR ALTER TRIGGER '||:NEW_VW_FIELD||' FOR '||:VW_NAME||'
        ACTIVE '||:NEW_EVENT||' POSITION 0
        '||
        (select substring(r.rdb$trigger_source from 1 for CHAR_LENGTH(r.rdb$trigger_source)-3) from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD)||:NEW_VW_VALUE
        ||'
         end;';
  end
end^

SET TERM ; ^

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_ALTER_VIEW TO SYSDBA;
Источник — «http://wiki.standart-n.ru/index.php?title=Изменения_представления&oldid=4315»