create or alter procedure rpl_allfields (
table_name my_docnumber,
is_docheader my_boolean)
as
declare variable field_type integer;
declare variable field_null integer;
declare variable field_name varchar(128);
declare variable field_count integer;
declare variable field_num integer;
declare variable out_sql varchar(32000);
begin
/*
table rpl_log:
ID identifier
SESSION_ID fc to rpl_sessions
REC_SQL generated sql
*/
table_name = upper(trim(:table_name));
select count(*)
from rdb$relation_fields
where rdb$relation_name = :table_name
into field_count;
/*
trigger after insert
*/
out_sql = 'create or alter trigger ' || :table_name || '_REPL for ' || :table_name || ' ' || ascii_char(13) || ascii_char(10) || 'active after insert or update or delete position 255' || ascii_char(13) || ascii_char(10) || 'as' || ascii_char(13) || ascii_char(10) || 'declare variable sql_text varchar(1024);' || ascii_char(13) || ascii_char(10) || 'declare variable sql_length integer;' || ascii_char(13) || ascii_char(10) || 'begin' || ascii_char(13) || ascii_char(10) || ' if ((select rdb$get_context(''USER_SESSION'', ''replicating_now'') from rdb$database)is not null) then exit;' || ascii_char(13) || ascii_char(10) || ' if (inserting) then begin' || ascii_char(13) || ascii_char(10) || ' insert into rpl_log(RPL_SQL)' || ascii_char(13) || ascii_char(10) || ' values (''insert into ' || :table_name || ' (';
field_num = 1;
for select rdb$field_name
from rdb$relation_fields
where rdb$relation_name = :table_name
order by rdb$field_position
into field_name
do
begin
if (:field_num = :field_count) then
out_sql =  ut_sql || trim(:field_name);
else
out_sql = ut_sql || trim(:field_name) || ',';
field_num = :field_num + 1;
end
out_sql = ut_sql || ')values('' ||' || ascii_char(13) || ascii_char(10);
field_num = 1;
for select trim(rf.rdb$field_name), f.rdb$field_type, rf.rdb$null_flag
from rdb$relation_fields rf
left join rdb$fields f on rf.rdb$field_source = f.rdb$field_name
where rdb$relation_name = :table_name
order by rdb$field_position
into field_name, field_type, field_null
do
begin
if (:field_type in (12, 13, 35, 37)) then
begin
--needed quotes
if (:field_null = 1) then
field_name = ''''''''' || replace(new.' || :field_name || ', '''''''', '''''''''''') || ''''''''';
else
field_name = 'coalesce('''''''' || replace(new.' || :field_name || ','''''''', '''''''''''') || '''''''', ''null'')';
end
else
begin
--clear digits
if (:field_null = 1) then
field_name = 'new.' || :field_name;
else
field_name = 'coalesce(new.' || :field_name || ', ''null'')';
end
if (:field_num = :field_count) then
out_sql = ut_sql || ' ' || :field_name || '||'');'' ' || ');' || ascii_char(13) || ascii_char(10);
else
out_sql = ut_sql || ' ' || :field_name || '||'',''||' || ascii_char(13) || ascii_char(10);
field_num = :field_num + 1;
end
out_sql = ut_sql || ' end ' || ascii_char(13) || ascii_char(10);
/*
trigger after update
*/
out_sql = ut_sql || ' if (updating)';
if (:is_docheader = 1) then
out_sql = ut_sql || ' then if ((new.commited <> old.commited)or(new.deleted <> old.deleted))';
out_sql = ut_sql || ' then begin ' || ascii_char(13) || ascii_char(10) || ' sql_text = ''update ' || :table_name || ' set '';' || ascii_char(13) || ascii_char(10) || ' sql_length = char_length(:sql_text); ' || ascii_char(13) || ascii_char(10);
field_num = 1;
for select trim(rf.rdb$field_name), f.rdb$field_type, rf.rdb$null_flag
from rdb$relation_fields rf
left join rdb$fields f on rf.rdb$field_source = f.rdb$field_name
where rdb$relation_name = :table_name
order by rdb$field_position
into field_name, field_type, field_null
do
begin
if (:is_docheader = 0) then
out_sql = ut_sql || ' if (old.' || :field_name || ' is distinct from new.' || :field_name || ') then' || ascii_char(13) || ascii_char(10);
if (:field_type in (12, 13, 35, 37)) then
begin
--needed quotes
if (:field_null = 1) then
out_sql = ut_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '''''' || replace(new.' || :field_name || ','''''''', '''''''''''') || '''''','';' || ascii_char(13) || ascii_char(10);
else
out_sql = ut_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce('''''''' || replace(new.' || :field_name || ','''''''', '''''''''''') || '''''''', ''null'') || '','';' || ascii_char(13) || ascii_char(10);
end
else
begin
--clear digits
if (:field_null = 1) then
out_sql = ut_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ascii_char(13) || ascii_char(10);
else
out_sql = ut_sql || ' sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce(new.' || :field_name || ', ''null'') || '','';' || ascii_char(13) || ascii_char(10);
end
end
out_sql = ut_sql || ' sql_text = substring(:sql_text from 1 for char_length(:sql_text) - 1);' || ascii_char(13) || ascii_char(10) || ' if (char_length(:sql_text) > (:sql_length + 1)) then begin' || ascii_char(13) || ascii_char(10) || ' sql_text = :sql_text || '' where id = '' || old.id || '';'' ;' || ascii_char(13) || ascii_char(10);
out_sql = ut_sql || ' insert into rpl_log(RPL_SQL)' || ascii_char(13) || ascii_char(10) || ' values (:sql_text);' || ascii_char(13) || ascii_char(10);
out_sql = ut_sql || ' end ' || ascii_char(13) || ascii_char(10);
out_sql = ut_sql || ' end ' || ascii_char(13) || ascii_char(10);
/*
trigger after delete
*/
out_sql = ut_sql || ' if (deleting) then begin' || ascii_char(13) || ascii_char(10) || ' insert into rpl_log(RPL_SQL)' || ascii_char(13) || ascii_char(10) || ' values (''delete from ' || :table_name || ' where id = '' || old.id || '';'');' || ascii_char(13) || ascii_char(10) || ' end' || ascii_char(13) || ascii_char(10) || 'end' || ascii_char(13) || ascii_char(10);
execute statement ut_sql;
end
|
