Процедура rpl_allfields

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(64);
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) ||
            '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 = :out_sql || TRIM(:field_name);
      ELSE out_sql = :out_sql || TRIM(:field_name) || ',' ;
    field_num = :field_num + 1;
  END
  out_sql = :out_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 = ''''' || new.' || :field_name || ' || ''''';
        ELSE field_name = 'coalesce('''''''' || 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 = :out_sql || '      ' || :field_name || '||'');'' ' || ');' || ASCII_CHAR(13) || ASCII_CHAR(10);
      ELSE out_sql = :out_sql || '      ' || :field_name || '||'',''||' || ASCII_CHAR(13) || ASCII_CHAR(10);
    field_num = :field_num + 1;
  END

  out_sql = :out_sql || '  end ' || ASCII_CHAR(13) || ASCII_CHAR(10);

/*
trigger after update
*/
  out_sql = :out_sql ||
            '  if (updating)';
  IF (:is_docheader = 1) THEN
    out_sql = :out_sql || ' then if (new.commited <> old.commited)';
  out_sql = :out_sql ||
            ' then begin ' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
            '    sql_text = ''update '|| :TABLE_NAME || ' set '';' || 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 = :out_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 = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '''''' || new.' || :field_name || ' || '''''','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
        ELSE out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce('''''''' || new.' || :field_name || ' || '''''''', ''null'') || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
    END ELSE BEGIN
    --clear digits
      IF (:field_null = 1)
        THEN out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || new.' || :field_name || ' || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
        ELSE out_sql = :out_sql || '      sql_text = :sql_text || '' ' || :field_name || ' = '' || coalesce(new.' || :field_name || ', ''null'') || '','';' || ASCII_CHAR(13) || ASCII_CHAR(10);
    END
  END
  out_sql = :out_sql || '    sql_text = substring(:sql_text from 1 for char_length(:sql_text) - 1);' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
                        '    sql_text = :sql_text || '' where id = '' || old.id || '';'' ;' || ASCII_CHAR(13) || ASCII_CHAR(10);
  out_sql = :out_sql || '    insert into rpl_log(RPL_SQL)' || ASCII_CHAR(13) || ASCII_CHAR(10) ||
                        '      values (:sql_text);' || ASCII_CHAR(13) || ASCII_CHAR(10);
  out_sql = :out_sql || '  end ' || ASCII_CHAR(13) || ASCII_CHAR(10);

/*
trigger after delete
*/

  out_sql =  :out_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 :out_sql;
END