-
Notifications
You must be signed in to change notification settings - Fork 0
/
06loc_create_hist_trigger.txt
76 lines (61 loc) · 3.26 KB
/
06loc_create_hist_trigger.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
DECLARE
v_tab CHAR := chr(9);
v_line_break VARCHAR2(2 CHAR) := chr(10);
v_main_dataowner VARCHAR2(255 CHAR); --Defaults to CURRENT_SCHEMA if left empty
v_main_tbl VARCHAR2(255 CHAR) := 'WEIRDO_TBL';
v_main_tbl_pkey_name VARCHAR2(255 CHAR);
v_hist_tbl_dataowner VARCHAR2(255 CHAR); --Defaults to v_main_dataowner if left empty
v_hist_tbl VARCHAR2(255 CHAR) := v_main_tbl || '_DH_HIST_TBL';
v_hist_tbl_attr_ls VARCHAR2(255 CHAR) := '("MODIFICATION_SRC_TYPE", "MODIFICATION_SRC", "TARGET_OID", "TARGET_ATTRIBUTE", "TARGET_OLD_VAL", "TARGET_NEW_VAL")';
v_hist_tbl_attr_pmap_ls VARCHAR2(255 CHAR);
v_hist_trig_dataowner VARCHAR2(255 CHAR); --Defaults to v_main_dataowner if left empty
v_hist_trig_name VARCHAR2(255 CHAR) := v_main_tbl || '_DH_HIST_TRG';
v_sql_stmt CLOB;
BEGIN
IF v_main_dataowner IS NULL
THEN
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') INTO v_main_dataowner FROM dual;
END IF;
IF v_hist_tbl_dataowner IS NULL
THEN
v_hist_tbl_dataowner := v_main_dataowner;
END IF;
IF v_hist_trig_dataowner IS NULL
THEN
v_hist_trig_dataowner := v_main_dataowner;
END IF;
v_sql_stmt := 'CREATE OR REPLACE TRIGGER "' || v_hist_trig_dataowner || '"."' || v_hist_trig_name || '"' || v_line_break
|| 'AFTER UPDATE ON "' || v_main_dataowner || '"."' || v_main_tbl || '"' || v_line_break
|| 'FOR EACH ROW' || v_line_break
|| 'BEGIN';
IF v_main_tbl_pkey_name IS NULL
THEN
SELECT ACC."COLUMN_NAME" INTO v_main_tbl_pkey_name FROM ALL_CONS_COLUMNS ACC
WHERE (ACC."OWNER", ACC."CONSTRAINT_NAME") IN
(
SELECT AC."OWNER", AC."CONSTRAINT_NAME" FROM ALL_CONSTRAINTS AC
WHERE AC."OWNER" = v_main_dataowner AND AC."TABLE_NAME" = v_main_tbl AND AC."CONSTRAINT_TYPE" = 'P' AND AC."STATUS" = 'ENABLED'
);
END IF;
v_hist_tbl_attr_pmap_ls := ':NEW."CURRENT_MOD_SRC_TYPE", :NEW."CURRENT_MOD_SRC", :NEW."' || v_main_tbl_pkey_name || '"';
FOR DH_MT_ATTR IN
(
SELECT * FROM ALL_TAB_COLUMNS ATC
WHERE ATC."OWNER" = v_main_dataowner AND ATC."TABLE_NAME" = v_main_tbl AND ATC."COLUMN_NAME" NOT IN ('CURRENT_MOD_SRC_TYPE', 'CURRENT_MOD_SRC') AND ATC."DATA_TYPE" NOT IN ('CLOB', 'BLOB', 'LONG', 'XMLTYPE')
ORDER BY ATC."COLUMN_ID" ASC
)
LOOP
v_sql_stmt := v_sql_stmt || v_line_break
|| v_tab || 'IF UPDATING(''' || DH_MT_ATTR."COLUMN_NAME" || ''')' || v_line_break
|| v_tab || 'THEN' || v_line_break
|| v_tab || v_tab || 'INSERT INTO "' || v_hist_tbl_dataowner || '"."' || v_hist_tbl || '"' || v_line_break
|| v_tab || v_tab || v_tab || v_hist_tbl_attr_ls || v_line_break
|| v_tab || v_tab || v_tab || 'VALUES (' || v_hist_tbl_attr_pmap_ls || ', ''' || DH_MT_ATTR."COLUMN_NAME" || ''', :OLD."' || DH_MT_ATTR."COLUMN_NAME" || '", :NEW."' || DH_MT_ATTR."COLUMN_NAME" || '");' || v_line_break
|| v_tab || 'END IF;';
END LOOP;
v_sql_stmt := v_sql_stmt || v_line_break
|| ' END;';
dbms_output.put_line(v_sql_stmt);
dbms_output.put_line(NULL);
--EXECUTE IMMEDIATE v_sql_stmt;
END;