procedure-trigger

Last modified by Евгения Вердеш on 2024/09/20 14:10

Есть процедура генерации универсальных триггеров, которые логируют изменения в любой таблице, для всех столбцов, но развёрнуто по вертикали в универсальные таблицы логов

Пример процедуры:

create or replace procedure p$chglog_make_trigger (p_table varchar2)authid current_user as

procedure s(p_text varchar2)is

begin

DBMS_OUTPUT.PUT_LINE(p_text);

end;

procedure ins_cols(p_alias varchar2)is

v_new char(1):=case when LOWER(p_alias)='new' then '1' else '0' end;

v_col varchar2(40);

begin

for c in (select column_name n,data_type t from user_tab_columns

where table_name=upper(p_table)) loop

v_col:=':'||p_alias||'.'||c.n;

if c.t='NUMBER' then

S(' insert into x$chglog_value(id,col,new,chr,num)values(v_id,'''||c.n||''''||

','''||v_new||''',to_char('||v_col||'),'||v_col||');');

elsif c.t='DATE' then

S(' insert into x$chglog_value(id,col,new,chr,dat)values(v_id,'''||c.n||''''||

','''||v_new||''',to_char('||v_col||',''DD.MM.YYYY''||');

S(' case when '||v_col||'<>trunc('||v_col||') then '' HH24:MI:SS'' end),'||v_col||');');

elsif c.t in('CHAR','VARCHAR2') then

S(' insert into x$chglog_value(id,col,new,chr)values(v_id,'''||c.n||''''||

','''||v_new||''','||v_col||');');

end if;

end loop;

end;

begin

S('create or replace trigger '||p_table||'_cl');

S('before insert or update or delete on '||p_table);

S('for each row');

S('declare');

S(' v_cmd char(1):=case when inserting then ''I'' when updating then ''U'' else ''D'' end;');

S(' v_id int;');

S('begin');

S(' insert into x$chglog(tbl,cmd)values('''||p_table||''',v_cmd)');

S(' returning id into v_id;');

S(' if updating or deleting then');

ins_cols('old');

S(' end if;');

S(' if inserting or updating then');

ins_cols('new');

S(' end if;');

S('end;');

S('/');

end;

и триггер:

CREATE OR REPLACE TRIGGER "TMS_SYSS_CL"

BEFORE INSERT OR UPDATE OR DELETE ON TMS_SYSS

FOR EACH ROW

DECLARE

v_cmd CHAR(1):=CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;

v_id INT;

BEGIN

INSERT INTO X$CHGLOG(tbl,cmd)VALUES('TMS_SYSS',v_cmd)

RETURNING ID INTO v_id;

IF UPDATING OR DELETING THEN

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD1','0',TO_CHAR(:OLD.COD1),:OLD.COD1);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'DENUMIREA','0',:OLD.DENUMIREA);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'UM','0',:OLD.UM);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER1','0',TO_CHAR(:OLD.NUMBER1),:OLD.NUMBER1);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER2','0',TO_CHAR(:OLD.NUMBER2),:OLD.NUMBER2);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'PRET','0',TO_CHAR(:OLD.PRET),:OLD.PRET);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'TIP','0',:OLD.TIP);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD','0',TO_CHAR(:OLD.COD),:OLD.COD);

END IF;

IF INSERTING OR UPDATING THEN

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD1','1',TO_CHAR(:NEW.COD1),:NEW.COD1);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'DENUMIREA','1',:NEW.DENUMIREA);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'UM','1',:NEW.UM);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER1','1',TO_CHAR(:NEW.NUMBER1),:NEW.NUMBER1);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER2','1',TO_CHAR(:NEW.NUMBER2),:NEW.NUMBER2);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'PRET','1',TO_CHAR(:NEW.PRET),:NEW.PRET);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'TIP','1',:NEW.TIP);

INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD','1',TO_CHAR(:NEW.COD),:NEW.COD);

END IF;

END;