Процедура rpl_somefields

CREATE OR ALTER PROCEDURE rpl_somefields (
    TABLE_NAME my_docnumber,
    is_docheader my_boolean)
AS
DECLARE variable table_id INTEGER;
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(:TABLE_NAME);
  SELECT id FROM rpl_tables WHERE UPPER(TABLE_NAME) = :TABLE_NAME
    INTO table_id;
  SELECT COUNT(f.id)
    FROM rpl_fields f
    WHERE f.table_id = :table_id
    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 r, rpl_fields f
    WHERE rdb$relation_name = :TABLE_NAME
    AND rdb$field_name = UPPER(f.field_name)
    AND f.table_id = :table_id
    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 UPPER(rp.field_name), f.rdb$field_type, rf.rdb$null_flag
    FROM rpl_fields rp
    LEFT JOIN rdb$relation_fields rf ON UPPER(rp.field_name) = rf.rdb$field_name
    LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
    WHERE rdb$relation_name = :TABLE_NAME
    AND rp.table_id = :table_id
    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 UPPER(rp.field_name), f.rdb$field_type, rf.rdb$null_flag
    FROM rpl_fields rp
    LEFT JOIN rdb$relation_fields rf ON UPPER(rp.field_name) = rf.rdb$field_name
    LEFT JOIN rdb$fields f ON rf.rdb$field_source = f.rdb$field_name
    WHERE rdb$relation_name = :TABLE_NAME
    AND rp.table_id = :table_id
    ORDER BY rdb$field_position
    INTO field_name, field_type, field_null
  do BEGIN
    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 || ' = '''''' || new.' || :field_name || ' || '''''','';' || 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 || ' = '' || new.' || :field_name || ' || '','';' || 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