Изменения представления — различия между версиями
Материал из wiki.standart-n.ru
BeTePoK (обсуждение | вклад) |
Aleksnick (обсуждение | вклад) |
||
| Строка 5: | Строка 5: | ||
Пример: | Пример: | ||
<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('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'); | ||
| Строка 70: | Строка 70: | ||
end | end | ||
| − | if (:I = 1) then | + | if (:I = 1) then /* один основной from, т.е. скрипт без подзапросов */ |
begin | 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)) || ''; | 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 | ||
else | else | ||
| − | begin | + | begin /* есть подзапросы - нужно распарсить и найти основной from */ |
J = 0; | J = 0; | ||
| − | while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do | + | while (position('FROM', UPPER(:VW_BODY), :J + 1) <> 0) do /* находим сколько всего from'ов в скрипте */ |
begin | begin | ||
I = 0; | I = 0; | ||
| Строка 83: | Строка 83: | ||
L = 0; | L = 0; | ||
STR = substring(:VW_BODY from L + 1 for :J - 1); | STR = substring(:VW_BODY from L + 1 for :J - 1); | ||
| − | while (position('(', STR, :L + 1) <> 0) do | + | while (position('(', STR, :L + 1) <> 0) do /* считаем количество открывающих скобок */ |
begin | begin | ||
L = position('(', STR, :L + 1); | L = position('(', STR, :L + 1); | ||
| Строка 90: | Строка 90: | ||
L = 0; | L = 0; | ||
| − | while (position(')', STR, :L + 1) <> 0) do | + | while (position(')', STR, :L + 1) <> 0) do /* считаем количество закрывающих скобок */ |
begin | begin | ||
L = position(')', STR, :L + 1); | L = position(')', STR, :L + 1); | ||
| Строка 96: | Строка 96: | ||
end | end | ||
| − | if (:I = 0) then | + | 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)) || ''; | 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 | ||
| Строка 124: | Строка 124: | ||
if (upper(:NEW_VW_JOIN) = 'DOMAIN') then | if (upper(:NEW_VW_JOIN) = 'DOMAIN') then | ||
begin | begin | ||
| − | if (not exists(select f.rdb$field_name, t.rdb$type_name from rdb$fields f, rdb$types t where | + | 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||';'; | execute statement 'CREATE DOMAIN '||:VW_NAME||' AS '||:NEW_VW_VALUE||';'; | ||
end | end | ||
Версия 11:03, 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;
скрипт для G$TASK
SET TERM ^ ;'||ascii_char(13)||ascii_char(10)||'
create or alter procedure PR_ALTER_VIEW ('||ascii_char(13)||ascii_char(10)||'
VW_NAME DM_TEXT,'||ascii_char(13)||ascii_char(10)||'
NEW_VW_FIELD DM_TEXT,'||ascii_char(13)||ascii_char(10)||'
NEW_VW_VALUE DM_TEXT,'||ascii_char(13)||ascii_char(10)||'
NEW_VW_JOIN DM_TEXT = null,'||ascii_char(13)||ascii_char(10)||'
NEW_EVENT DM_TEXT = null)'||ascii_char(13)||ascii_char(10)||'
as'||ascii_char(13)||ascii_char(10)||'
declare variable I DM_ID;'||ascii_char(13)||ascii_char(10)||'
declare variable VW_BODY DM_TEXT_BIG;'||ascii_char(13)||ascii_char(10)||'
declare variable VW_FIELDS DM_TEXT_BIG;'||ascii_char(13)||ascii_char(10)||'
declare variable J DM_ID;'||ascii_char(13)||ascii_char(10)||'
declare variable STR DM_TEXT_BIG;'||ascii_char(13)||ascii_char(10)||'
declare variable L integer;'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
if ((:NEW_VW_JOIN is null) or (:NEW_VW_JOIN = '''') or (upper(:NEW_VW_JOIN) = ''VIEW'')) then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
select list(trim(RDB$FIELD_NAME))'||ascii_char(13)||ascii_char(10)||'
from (select RC.RDB$FIELD_NAME'||ascii_char(13)||ascii_char(10)||'
from RDB$RELATION_FIELDS RC'||ascii_char(13)||ascii_char(10)||'
where RC.RDB$RELATION_NAME = upper(:VW_NAME)'||ascii_char(13)||ascii_char(10)||'
order by RC.RDB$FIELD_POSITION)'||ascii_char(13)||ascii_char(10)||'
into :VW_FIELDS;'||ascii_char(13)||ascii_char(10)||'
select R.RDB$VIEW_SOURCE'||ascii_char(13)||ascii_char(10)||'
from RDB$RELATIONS R'||ascii_char(13)||ascii_char(10)||'
where R.RDB$RELATION_NAME = upper(:VW_NAME)'||ascii_char(13)||ascii_char(10)||'
into :VW_BODY;'||ascii_char(13)||ascii_char(10)||'
I = 0;'||ascii_char(13)||ascii_char(10)||'
J = 0;'||ascii_char(13)||ascii_char(10)||'
if ((select count(*)'||ascii_char(13)||ascii_char(10)||'
from RDB$RELATION_FIELDS RF'||ascii_char(13)||ascii_char(10)||'
where RF.RDB$RELATION_NAME = upper(:VW_NAME) and'||ascii_char(13)||ascii_char(10)||'
RF.RDB$FIELD_NAME = upper(:NEW_VW_FIELD)) = 0) then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
while (position(''FROM'', UPPER(:VW_BODY), :J + 1) <> 0) do'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
J = position(''FROM'', UPPER(:VW_BODY), :J + 1);'||ascii_char(13)||ascii_char(10)||'
I = :I + 1;'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (:I = 1) then --один основной from, т.е. скрипт без подзапросов'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
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));'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
else'||ascii_char(13)||ascii_char(10)||'
begin --есть подзапросы - нужно распарсить и найти основной from'||ascii_char(13)||ascii_char(10)||'
J = 0'||ascii_char(13)||ascii_char(10)||';
while (position(''FROM'', UPPER(:VW_BODY), :J + 1) <> 0) do --находим сколько всего fromов в скрипте'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
I = 0;'||ascii_char(13)||ascii_char(10)||'
J = position(''FROM'', UPPER(:VW_BODY), :J + 1);'||ascii_char(13)||ascii_char(10)||'
L = 0;'||ascii_char(13)||ascii_char(10)||'
STR = substring(:VW_BODY from L + 1 for :J - 1);'||ascii_char(13)||ascii_char(10)||'
while (position(''('', STR, :L + 1) <> 0) do --считаем количество открывающих скобок'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
L = position(''('', STR, :L + 1);'||ascii_char(13)||ascii_char(10)||'
I = :I + 1;'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
L = 0;'||ascii_char(13)||ascii_char(10)||'
while (position('')'', STR, :L + 1) <> 0) do --считаем количество закрывающих скобок'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
L = position('')'', STR, :L + 1);'||ascii_char(13)||ascii_char(10)||'
I = :I - 1;'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (:I = 0) then --если открывающих и закрывающих поровну - значит этот from основной, если конечно в комментах нет лишних скобок'||ascii_char(13)||ascii_char(10)||'
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));'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (upper(:NEW_VW_JOIN) = ''TABLE'') then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
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'||ascii_char(13)||ascii_char(10)||'
execute statement ''ALTER ''||:NEW_VW_JOIN||'' ''||:VW_NAME||'' ADD ''||:NEW_VW_FIELD||'' ''||:NEW_VW_VALUE;'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if ((upper(:NEW_VW_JOIN) = ''INDEX'') or (upper(:NEW_VW_JOIN) = ''DESCENDING INDEX'') or (upper(:NEW_VW_JOIN) = ''ASCENDING INDEX'')) then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
if (not exists(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$INDEX_NAME=:NEW_VW_VALUE)) then'||ascii_char(13)||ascii_char(10)||'
execute statement ''CREATE ''||:NEW_VW_JOIN||'' ''||:NEW_VW_VALUE||'' ON ''||:VW_NAME||'' (''||:NEW_VW_FIELD||'');'';'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (upper(:NEW_VW_JOIN) = ''GENERATOR'') then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
if (not exists(SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS WHERE RDB$SYSTEM_FLAG=0 and RDB$GENERATOR_NAME = :NEW_VW_VALUE)) then'||ascii_char(13)||ascii_char(10)||'
execute statement ''CREATE GENERATOR ''||:NEW_VW_VALUE;'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (upper(:NEW_VW_JOIN) = ''PRIMARY KEY'') then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
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'||ascii_char(13)||ascii_char(10)||'
execute statement ''ALTER TABLE ''||:VW_NAME||'' ADD CONSTRAINT PK_''||:VW_NAME||'' PRIMARY KEY (''||:NEW_VW_FIELD||'');'';'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (upper(:NEW_VW_JOIN) = ''DOMAIN'') then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
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'||ascii_char(13)||ascii_char(10)||'
execute statement ''CREATE DOMAIN ''||:VW_NAME||'' AS ''||:NEW_VW_VALUE;'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
if (upper(:NEW_VW_JOIN) = ''TRIGGER'') then'||ascii_char(13)||ascii_char(10)||'
begin'||ascii_char(13)||ascii_char(10)||'
if ((select r.rdb$trigger_source from rdb$triggers r where r.rdb$trigger_name = :NEW_VW_FIELD) not containing :NEW_VW_VALUE) then'||ascii_char(13)||ascii_char(10)||'
execute statement '''||ascii_char(13)||ascii_char(10)||'
CREATE OR ALTER TRIGGER ''||:NEW_VW_FIELD||'' FOR ''||:VW_NAME||'''||ascii_char(13)||ascii_char(10)||'
ACTIVE ''||:NEW_EVENT||'' POSITION 0 ''||'||ascii_char(13)||ascii_char(10)||'
(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||'''||ascii_char(13)||ascii_char(10)||'
end'';'||ascii_char(13)||ascii_char(10)||'
end'||ascii_char(13)||ascii_char(10)||'
end;'||ascii_char(13)||ascii_char(10)||'
^ '||ascii_char(13)||ascii_char(10)||'
SET TERM ; ^
