Skip to Content

Wiki source code of procedure-trigger

Last modified by Евгения Вердеш on 2024/09/20 14:10
Show last authors
1 (((
2
3 )))
4
5 (((
6 Есть процедура генерации универсальных триггеров, которые логируют изменения в любой таблице, для всех столбцов, но развёрнуто по вертикали в универсальные таблицы логов
7 )))
8
9 (((
10
11 )))
12
13 (((
14 Пример процедуры:
15
16
17 create or replace procedure p$chglog_make_trigger (p_table varchar2)authid current_user as
18
19 ~-~-
20
21 procedure s(p_text varchar2)is
22
23 begin
24
25 DBMS_OUTPUT.PUT_LINE(p_text);
26
27 end;
28
29 ~-~-
30
31 procedure ins_cols(p_alias varchar2)is
32
33 v_new char(1):=case when LOWER(p_alias)='new' then '1' else '0' end;
34
35 v_col varchar2(40);
36
37 begin
38
39 for c in (select column_name n,data_type t from user_tab_columns
40
41 where table_name=upper(p_table)) loop
42
43 v_col:=':'||p_alias||'.'||c.n;
44
45 if c.t='NUMBER' then
46
47 S(' insert into x$chglog_value(id,col,new,chr,num)values(v_id,'''||c.n||''''||
48
49 ','''||v_new||''',to_char('||v_col||'),'||v_col||');');
50
51 elsif c.t='DATE' then
52
53 S(' insert into x$chglog_value(id,col,new,chr,dat)values(v_id,'''||c.n||''''||
54
55 ','''||v_new||''',to_char('||v_col||',''DD.MM.YYYY''||');
56
57 S(' case when '||v_col||'<>trunc('||v_col||') then '' HH24:MI:SS'' end),'||v_col||');');
58
59 elsif c.t in('CHAR','VARCHAR2') then
60
61 S(' insert into x$chglog_value(id,col,new,chr)values(v_id,'''||c.n||''''||
62
63 ','''||v_new||''','||v_col||');');
64
65 end if;
66
67 end loop;
68
69 end;
70
71 ~-~-
72
73 begin
74
75 S('create or replace trigger '||p_table||'_cl');
76
77 S('before insert or update or delete on '||p_table);
78
79 S('for each row');
80
81 S('declare');
82
83 S(' v_cmd char(1):=case when inserting then ''I'' when updating then ''U'' else ''D'' end;');
84
85 S(' v_id int;');
86
87 S('begin');
88
89 S(' insert into x$chglog(tbl,cmd)values('''||p_table||''',v_cmd)');
90
91 S(' returning id into v_id;');
92
93 S(' if updating or deleting then');
94
95 ins_cols('old');
96
97 S(' end if;');
98
99 S(' if inserting or updating then');
100
101 ins_cols('new');
102
103 S(' end if;');
104
105 S('end;');
106
107 S('/');
108
109 end;
110
111
112
113
114
115
116 /
117 )))
118
119 (((
120 и триггер:
121
122
123 CREATE OR REPLACE TRIGGER "TMS_SYSS_CL"
124
125 BEFORE INSERT OR UPDATE OR DELETE ON TMS_SYSS
126
127 FOR EACH ROW
128
129 DECLARE
130
131 v_cmd CHAR(1):=CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;
132
133 v_id INT;
134
135 BEGIN
136
137 INSERT INTO X$CHGLOG(tbl,cmd)VALUES('TMS_SYSS',v_cmd)
138
139 RETURNING ID INTO v_id;
140
141 IF UPDATING OR DELETING THEN
142
143 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD1','0',TO_CHAR(:OLD.COD1),:OLD.COD1);
144
145 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'DENUMIREA','0',:OLD.DENUMIREA);
146
147 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'UM','0',:OLD.UM);
148
149 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER1','0',TO_CHAR(:OLD.NUMBER1),:OLD.NUMBER1);
150
151 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER2','0',TO_CHAR(:OLD.NUMBER2),:OLD.NUMBER2);
152
153 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'PRET','0',TO_CHAR(:OLD.PRET),:OLD.PRET);
154
155 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'TIP','0',:OLD.TIP);
156
157 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD','0',TO_CHAR(:OLD.COD),:OLD.COD);
158
159 END IF;
160
161 IF INSERTING OR UPDATING THEN
162
163 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD1','1',TO_CHAR(:NEW.COD1),:NEW.COD1);
164
165 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'DENUMIREA','1',:NEW.DENUMIREA);
166
167 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'UM','1',:NEW.UM);
168
169 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER1','1',TO_CHAR(:NEW.NUMBER1),:NEW.NUMBER1);
170
171 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'NUMBER2','1',TO_CHAR(:NEW.NUMBER2),:NEW.NUMBER2);
172
173 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'PRET','1',TO_CHAR(:NEW.PRET),:NEW.PRET);
174
175 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR)VALUES(v_id,'TIP','1',:NEW.TIP);
176
177 INSERT INTO X$CHGLOG_VALUE(ID,COL,NEW,CHR,num)VALUES(v_id,'COD','1',TO_CHAR(:NEW.COD),:NEW.COD);
178
179 END IF;
180
181 END;
182
183
184 /
185 )))
186
187 (((
188
189 )))