Логирование и отчетность

CREATE OR ALTER TRIGGER USER$EXCHANGELOG_WEB FOR EXCHANGELOG
active BEFORE INSERT OR UPDATE POSITION 0
AS
DECLARE p1 INT;
DECLARE p2 INT;
--
DECLARE pcode TYPE OF COLUMN clients.pcode;
DECLARE dcode TYPE OF COLUMN doctor.dcode;
DECLARE logintype INT;
DECLARE checkmode INT;
DECLARE func ttext255;
BEGIN
  -- новое сообщение от сервиса
  IF (NEW.logtype=1) THEN
  BEGIN
    SELECT avalue FROM xml_get_node_value(NEW.msgtext, 'MSH.3')
      INTO NEW.method;
    p1 = POSITION('<WEB_', NEW.msgtext);
    IF (p1>0) THEN
    BEGIN
      p2 = POSITION(' ', NEW.msgtext, p1+1);
      IF (p2>0) THEN
        NEW.action = TRIM(SUBSTRING(NEW.msgtext FROM p1+1 FOR p2-p1));
    END
 
    -- пользовательская функция
    SELECT avalue FROM xml_get_node_value(NEW.msgtext, 'FUNCTION') INTO func;
    IF (NEW.action = 'WEB_USER_DEFINED_HANDLER') THEN
      NEW.action = 'USER$WEB_'||func;
 
    -- доп данные
    SELECT avalue FROM xml_get_node_value_integer(NEW.msgtext, 'PCODE') INTO pcode;
    SELECT avalue FROM xml_get_node_value_integer(NEW.msgtext, 'DCODE') INTO dcode;
    SELECT avalue FROM xml_get_node_value_integer(NEW.msgtext, 'LOGINTYPE') INTO logintype;
    SELECT avalue FROM xml_get_node_value_integer(NEW.msgtext, 'CHECKMODE') INTO checkmode;
 
    IF (COALESCE(NEW.logtext,'')='') THEN
    BEGIN
      NEW.logtext = '';
      IF (pcode IS NOT NULL) THEN
        NEW.logtext = NEW.logtext||'&PCODE='||pcode;
      IF (dcode IS NOT NULL) THEN
        NEW.logtext = NEW.logtext||'&DCODE='||dcode;
      IF (logintype IS NOT NULL) THEN
        NEW.logtext = NEW.logtext||'&CHECKTYPE='||logintype;
      IF (checkmode IS NOT NULL) THEN
        NEW.logtext = NEW.logtext||'&CHECKTYPE='||checkmode;
      --
      IF (CHAR_LENGTH(NEW.logtext)>0) THEN
        NEW.logtext = SUBSTRING(NEW.logtext FROM 2);
    END
  END
END