CREATE TABLE LOG_HEADER
(
  ID_LOG_HEADER    NUMBER( 18 ),
  TBL_NAME         VARCHAR2( 255  BYTE)           NOT NULL,
  ACTION           VARCHAR2( 15  BYTE)            NOT NULL,
  DT_CHANGE        DATE                         DEFAULT SYSDATE               NOT NULL,
  USER_CHANGE      VARCHAR2( 255  BYTE)           DEFAULT USER                  NOT NULL,
  HOST_CHANGE      VARCHAR2( 255  BYTE),
  TERMINAL_CHANGE  VARCHAR2( 255  BYTE),
  IP_CHANGE        VARCHAR2( 15  BYTE),
  OS_USER_CHANGE   VARCHAR2( 255  BYTE),
  MODULE_CHANGE    VARCHAR2( 255  BYTE),
  ID               NUMBER( 18 )
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE LOG_HEADER IS 'Логирование изменений данных';

COMMENT ON COLUMN LOG_HEADER.ID_LOG_HEADER IS 'Код записи';

COMMENT ON COLUMN LOG_HEADER.TBL_NAME IS 'Изменяемая таблица';

COMMENT ON COLUMN LOG_HEADER.ACTION IS 'Действие (insert, update, delete)';

COMMENT ON COLUMN LOG_HEADER.DT_CHANGE IS 'Дата изменения';

COMMENT ON COLUMN LOG_HEADER.USER_CHANGE IS 'Кто изменил';

COMMENT ON COLUMN LOG_HEADER.HOST_CHANGE IS 'Машина';

COMMENT ON COLUMN LOG_HEADER.TERMINAL_CHANGE IS 'Терминал';

COMMENT ON COLUMN LOG_HEADER.IP_CHANGE IS 'IP адрес';

COMMENT ON COLUMN LOG_HEADER.OS_USER_CHANGE IS 'Пользователь';

COMMENT ON COLUMN LOG_HEADER.MODULE_CHANGE IS 'Программа';

COMMENT ON COLUMN LOG_HEADER.ID IS 'Код измененной записи';

ALTER TABLE LOG_HEADER ADD (
  CONSTRAINT LOG_HEADER_PK
  PRIMARY KEY
  (ID_LOG_HEADER)
  USING INDEX LOG_HEADER_PK);

CREATE TABLE LOG_ITEM
(
  ID_LOG_ITEM    NUMBER( 18 ),
  ID_LOG_HEADER  NUMBER( 18 )                     NOT NULL,
  COL_NAME       VARCHAR2( 255  BYTE)             NOT NULL,
  OLD_VALUE      VARCHAR2( 2000  CHAR),
  NEW_VALUE      VARCHAR2( 2000  CHAR),
  COL_COMMENT    VARCHAR2( 255  CHAR)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN LOG_ITEM.ID_LOG_ITEM IS 'Код строки изменения';

COMMENT ON COLUMN LOG_ITEM.ID_LOG_HEADER IS 'Заголовок';

COMMENT ON COLUMN LOG_ITEM.COL_NAME IS 'Поле';

COMMENT ON COLUMN LOG_ITEM.OLD_VALUE IS 'Старое значение';

COMMENT ON COLUMN LOG_ITEM.NEW_VALUE IS 'Новое значение';

COMMENT ON COLUMN LOG_ITEM.COL_COMMENT IS 'Наименование поля';

ALTER TABLE LOG_ITEM ADD (
  FOREIGN KEY (ID_LOG_HEADER) 
  REFERENCES LOG_HEADER (ID_LOG_HEADER));


CREATE OR REPLACE  PROCEDURE LOG_INSERT
    (
     aID_LOG_HEADER   IN LOG_HEADER.ID_LOG_HEADER%TYPE
    ,aTBL_NAME        IN LOG_HEADER.TBL_NAME%TYPE
    ,aACTION          IN LOG_HEADER.ACTION%TYPE
    ,aID              IN LOG_HEADER.ID%TYPE
    ) IS
lDT_CHANGE       LOG_HEADER.DT_CHANGE%TYPE;
lUSER_CHANGE     LOG_HEADER.USER_CHANGE%TYPE;
lHOST_CHANGE     LOG_HEADER.HOST_CHANGE%TYPE;
lTERMINAL_CHANGE LOG_HEADER.TERMINAL_CHANGE%TYPE;
lIP_CHANGE       LOG_HEADER.IP_CHANGE%TYPE;
lOS_USER_CHANGE  LOG_HEADER.OS_USER_CHANGE%TYPE;
lMODULE_CHANGE   LOG_HEADER.MODULE_CHANGE%TYPE;
  BEGIN
  SELECT sysdate
  ,sys_context('USERENV', 'SESSION_USER')
  ,sys_context('USERENV', 'HOST') 
  ,sys_context('USERENV', 'TERMINAL')
  ,sys_context('USERENV', 'IP_ADDRESS')
  ,sys_context('USERENV', 'OS_USER')
  ,sys_context('USERENV', 'MODULE') 
  into
lDT_CHANGE        
,lUSER_CHANGE      
,lHOST_CHANGE      
,lTERMINAL_CHANGE  
,lIP_CHANGE        
,lOS_USER_CHANGE   
,lMODULE_CHANGE    
  FROM dual;
    INSERT INTO LOG_HEADER
      (
       ID_LOG_HEADER
      ,TBL_NAME
      ,ACTION
      ,DT_CHANGE
      ,USER_CHANGE
      ,HOST_CHANGE
      ,TERMINAL_CHANGE
      ,IP_CHANGE
      ,OS_USER_CHANGE
      ,MODULE_CHANGE
      ,ID
      )
    VALUES
      (
       aID_LOG_HEADER
      ,aTBL_NAME
      ,aACTION
      ,lDT_CHANGE
      ,lUSER_CHANGE
      ,lHOST_CHANGE
      ,lTERMINAL_CHANGE
      ,lIP_CHANGE
      ,lOS_USER_CHANGE
      ,lMODULE_CHANGE
      ,aID
      );
  END LOG_INSERT;


CREATE OR REPLACE TRIGGER BIUD_TBL_ITEM_LOG
BEFORE INSERT OR UPDATE
ON TBL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE

	lID_Header  NUMBER;
lAction varchar2( 20 );
begin
if INSERTING THEN lAction:='INSERT' ;
elsif UPDATING then lAction:='UPDATE';
end if;
select SQ.nextval into lID_Header from dual;
 
LOG_INSERT
    (
     aID_LOG_HEADER   => lID_Header
    ,aTBL_NAME        => 'TBL'
    ,aACTION          =>  lAction  
    ,aID              => :new.ID_TBL
    );


    INSERT INTO LOG_ITEM (ID_LOG_ITEM, ID_LOG_HEADER, COL_NAME, OLD_VALUE, NEW_VALUE, COL_COMMENT)VALUES (SQ.nextval, lID_Header,'ID_TBL' , to_char (lr_doc_old.ID_ADDITION), to_char (lr_doc.ID_ADDITION), 'Код');
    INSERT INTO LOG_ITEM (ID_LOG_ITEM, ID_LOG_HEADER, COL_NAME, OLD_VALUE, NEW_VALUE, COL_COMMENT)VALUES (SQ.nextval, lID_Header,'NUM' , lr_doc_old.NUM, lr_doc.NUM, 'Номер');
    INSERT INTO LOG_ITEM (ID_LOG_ITEM, ID_LOG_HEADER, COL_NAME, OLD_VALUE, NEW_VALUE, COL_COMMENT)VALUES (SQ.nextval, lID_Header,'DT' , to_char (lr_doc_old.DT), to_char (lr_doc.DT), 'Дата');
    INSERT INTO LOG_ITEM (ID_LOG_ITEM, ID_LOG_HEADER, COL_NAME, OLD_VALUE, NEW_VALUE, COL_COMMENT)VALUES (SQ.nextval, lID_Header,'DTYPE' , to_char (lr_doc_old.DTYPE), to_char (lr_doc.DTYPE), 'Тип документа: 1-дополнение, 2-спецификация');
    INSERT INTO LOG_ITEM (ID_LOG_ITEM, ID_LOG_HEADER, COL_NAME, OLD_VALUE, NEW_VALUE, COL_COMMENT)VALUES (SQ.nextval, lID_Header,'STATUS' , to_char (lr_doc_old.STATUS), to_char (lr_doc.STATUS), 'Статус');

    delete from log_item where ID_LOG_HEADER = lID_Header and (OLD_VALUE = NEW_VALUE or (NEW_VALUE is null and OLD_VALUE is null));
END BIUD_TBL_ITEM_LOG;