Introduction
Tracking changes in data over time is crucial for effective data management, compliance, and auditing. In this blog post, we’ll walk you through how to create a history table in your database. By combining SQL sequences and triggers, you can automatically log changes to your main table, ensuring a robust and reliable audit trail.
Why Use a History Table?
A history table allows you to track data modifications, such as changes to customer information or employee records. This feature is invaluable for:
- Data Auditing: Understand what changes occurred and who made them.
- Compliance: Meet regulatory requirements for data traceability.
- Trend Analysis: Analyze historical data for patterns and insights.
With a properly set up history table, you’ll always have a clear record of past states, providing transparency and accountability.
Table Structure
We’ll start by creating a main table to hold our core data. Here’s an example setup for a generic table called
TEST:SQL
xxxxxxxxxx25
1
CREATE TABLE TEST 2
( TEST_UPLOAD BLOB, 3
TEST_MIME_TYPE VARCHAR2(4000), 4
TEST_NAME VARCHAR2(4000), 5
TEST_ID NUMBER, 6
A NUMBER, 7
AA NUMBER, 8
AB NUMBER, 9
FDAS NUMBER, 10
AFDSAFDS NUMBER, 11
TEST_VALID_FROM NUMBER DEFAULT 5 NOT NULL ENABLE, 12
TEST_VALID_TO NUMBER DEFAULT 413
) ;CREATE TABLE TEST (14
TEST_UPLOAD BLOB,15
TEST_MIME_TYPE VARCHAR2(4000),16
TEST_NAME VARCHAR2(4000),17
TEST_ID NUMBER,18
A NUMBER,19
AA NUMBER,20
AB NUMBER,21
FDAS NUMBER,22
AFDSAFDS NUMBER,23
TEST_VALID_FROM NUMBER DEFAULT 5 NOT NULL ENABLE,24
TEST_VALID_TO NUMBER DEFAULT 425
);Package Specifications
Next, we define the sequence and trigger specifications to handle automatic ID generation and capturing data changes.
SQL
xxxxxxxxxx1
/*============================= 2
===== history trigger ===== 3
============================= */4
procedure pr_generate_history( 5
pi_table_name IN apex_t_varchar2 6
, pi_delete_yn IN varchar2 default 'NO'7
, pi_create_yn in varchar2 default 'NO' 8
, pi_trigger_yn in varchar2 default 'NO' 9
);Package Body
The body of the trigger will specify what happens when data changes—specifically how the history table captures these changes.
SQL
xxxxxxxxxx355
1
/*============================= 2
===== history trigger ===== 3
============================= */4
procedure pr_generate_history( 5
pi_table_name IN apex_t_varchar2 6
, pi_delete_yn IN varchar2 default 'NO' 7
, pi_create_yn in varchar2 default 'NO' 8
, pi_trigger_yn in varchar2 default 'NO' 9
)10
11
is12
-- global13
v_count_total_table number := pi_table_name.COUNT; 14
v_table_name varchar2(4000); 15
v_table_history_name varchar2(4000); 16
v_sequenz_name varchar2(4000); 17
v_trigger_name varchar2(4000); 18
19
-- 2. create history table20
v_main_table_name varchar2(4000); 21
v_check_history_table_exists_yn number; 22
v_check_sequence_exists_yn number; 23
v_count number; 24
v_sql varchar2(4000); 25
v_h_id_count number; 26
v_h_created_count number; 27
28
29
-- 3. trigger 30
v_original_table_name varchar2(4000); 31
v_primary_key_name_trg varchar2(4000);32
v_column_name user_tab_columns.column_name%TYPE;33
v_column_list VARCHAR2(4000) := '';34
v_new_column_list VARCHAR2(4000) := '';35
v_primary_key_count NUMBER;36
v_primary_key_name VARCHAR2(4000);37
begin 38
39
-- 1. delete =============================================================40
if pi_delete_yn = 'YES' then 41
42
-- 1. ========= delete table ======43
FOR i IN 1..v_count_total_table LOOP44
45
-- save table name46
v_table_name := pi_table_name(i); 47
v_table_history_name := 'H_'||pi_table_name(i); 48
49
BEGIN50
51
-- drop table52
EXECUTE IMMEDIATE 'DROP TABLE ' || v_table_history_name; 53
54
-- successfully deleted55
DBMS_OUTPUT.PUT_LINE('Table is successfully deleted: ' || v_table_history_name); 56
57
-- if error58
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed to delete Table: ' || v_table_history_name || '. Error: ' || SQLERRM); 59
60
END;61
62
END LOOP;63
64
-- 2. ========= delete seq ======65
FOR i IN 1..v_count_total_table LOOP66
67
-- save sequenz name68
v_table_history_name := 'H_'||pi_table_name(i); 69
v_sequenz_name := v_table_history_name||'_SEQ';70
71
BEGIN72
73
-- drop table74
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || v_sequenz_name; 75
76
-- successfully deleted77
DBMS_OUTPUT.PUT_LINE('Sequence is successfully deleted: ' || v_sequenz_name); 78
79
-- if error80
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed to delete Sequence: ' || v_sequenz_name || '. Error: ' || SQLERRM); 81
82
END;83
84
END LOOP;85
86
-- 3. ========= delete trigger ======87
FOR i IN 1..v_count_total_table LOOP88
89
-- save trigger name90
v_table_name := pi_table_name(i); 91
v_table_history_name := 'H_'||pi_table_name(i); 92
v_trigger_name := v_table_history_name||'_TRG'; 93
94
BEGIN95
96
-- drop table97
EXECUTE IMMEDIATE 'DROP TRIGGER ' || v_trigger_name; 98
99
-- successfully deleted100
DBMS_OUTPUT.PUT_LINE('trigger is successfully deleted: ' || v_trigger_name); 101
102
-- if error103
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed to delete trigger: ' || v_trigger_name || '. Error: ' || SQLERRM); 104
105
END;106
107
END LOOP;108
109
end if;110
111
112
if pi_create_yn = 'YES' then113
114
-- 2.1 create history table =============================================115
FOR i IN 1..v_count_total_table LOOP116
117
-- Zuweisung des Tabellennamens aus der Liste118
v_table_name := pi_table_name(i); 119
v_table_history_name := 'H_'||pi_table_name(i); 120
121
-- ===== create if not exists ======================================================122
SELECT COUNT(*)123
INTO v_check_history_table_exists_yn124
FROM user_tables125
WHERE LOWER(table_name) = LOWER(v_table_history_name);126
127
IF v_check_history_table_exists_yn = 0 THEN128
129
-- Erstellen der Historientabelle, wenn sie nicht existiert130
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_history_name || ' AS SELECT * FROM ' || v_table_name || ' WHERE 1=0';131
132
-- successfully message133
DBMS_OUTPUT.PUT_LINE('History table successfully created: ' || v_table_history_name);134
135
136
END IF;137
138
END LOOP;139
140
-- 2.2 create Sequence =============================================141
FOR i IN 1..v_count_total_table LOOP142
143
-- Zuweisung des Tabellennamens aus der Liste144
v_table_name := pi_table_name(i); 145
v_table_history_name := 'H_'||pi_table_name(i); 146
v_sequenz_name := v_table_history_name||'_SEQ'; 147
148
-- ===== create if not exists ======================================================149
SELECT COUNT(*)150
INTO v_check_sequence_exists_yn151
FROM user_sequences152
WHERE LOWER(sequence_name) = LOWER(v_sequenz_name);153
154
IF v_check_sequence_exists_yn = 0 THEN155
156
-- Erstellen der Sequenz für die Historientabelle157
EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || v_sequenz_name || ' START WITH 1 INCREMENT BY 1';158
159
-- successfully message160
DBMS_OUTPUT.PUT_LINE('Sequence successfully created: ' || v_sequenz_name);161
162
163
END IF;164
165
END LOOP;166
167
168
-- 2.3 add new columns =============================================169
FOR i IN 1..v_count_total_table LOOP170
171
-- Zuweisung des Tabellennamens aus der Liste172
v_table_name := pi_table_name(i); 173
v_table_history_name := 'H_'||pi_table_name(i); 174
175
--========= get columns176
FOR r IN (SELECT column_name177
, data_type178
, data_length 179
FROM user_tab_columns 180
WHERE LOWER(table_name) = LOWER(v_table_name)181
)182
LOOP183
184
-- check history table185
SELECT COUNT(*)186
INTO v_count187
FROM user_tab_columns188
WHERE LOWER(table_name) = LOWER(v_table_history_name) 189
AND LOWER(column_name) = LOWER(r.column_name);190
191
IF v_count = 0 THEN192
v_sql := 'ALTER TABLE ' || v_table_history_name || ' ADD ' || r.column_name || ' ' || r.data_type;193
IF r.data_type IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN194
v_sql := v_sql || '(' || r.data_length || ')';195
END IF;196
EXECUTE IMMEDIATE v_sql;197
DBMS_OUTPUT.PUT_LINE('New column ' || r.column_name || ' successfully added to table ' || v_table_history_name || '.');198
199
END IF;200
END LOOP;201
202
END LOOP;203
204
205
-- 2.4 add h_id and h_created =============================================206
FOR i IN 1..v_count_total_table LOOP207
208
-- Zuweisung des Tabellennamens aus der Liste209
v_table_name := pi_table_name(i); 210
v_table_history_name := 'H_'||pi_table_name(i); 211
212
--========= put h_id columns and h_created column to history table 213
SELECT COUNT(*)214
INTO v_h_id_count215
FROM user_tab_columns216
WHERE LOWER(table_name) = LOWER(v_table_history_name) AND LOWER(column_name) = 'h_id'217
;218
219
SELECT COUNT(*)220
INTO v_h_created_count221
FROM user_tab_columns222
WHERE LOWER(table_name) = LOWER(v_table_history_name) AND LOWER(column_name) = 'h_created'223
;224
225
-- Hinzufügen der Spalten 'h_id' und 'h_created', wenn sie nicht existieren226
IF v_h_id_count = 0 OR v_h_created_count = 0 THEN227
v_sql := 'ALTER TABLE ' || v_table_history_name || ' ADD (';228
IF v_h_id_count = 0 THEN229
v_sql := v_sql || 'h_id NUMBER DEFAULT ' || v_table_history_name || '_SEQ.NEXTVAL PRIMARY KEY';230
IF v_h_created_count = 0 THEN231
v_sql := v_sql || ', ';232
END IF;233
END IF;234
IF v_h_created_count = 0 THEN235
v_sql := v_sql || 'h_created TIMESTAMP DEFAULT SYSDATE';236
END IF;237
v_sql := v_sql || ')';238
EXECUTE IMMEDIATE v_sql;239
DBMS_OUTPUT.PUT_LINE('Columns h_id and/or h_created added to the table ' || v_table_history_name || '.');240
END IF;241
242
END LOOP;243
244
end if;245
246
247
248
249
250
-- 3. trigger ==================================================251
if pi_trigger_yn = 'YES' then 252
253
FOR i IN 1..v_count_total_table LOOP254
255
-- Assign the table name from the list256
v_table_name := pi_table_name(i); 257
v_table_history_name := 'H_'||pi_table_name(i); 258
v_trigger_name := v_table_history_name || '_TRG'; 259
260
-- Reset the column list for each iteration261
v_column_list := '';262
v_new_column_list := '';263
264
-- Query column information265
FOR r IN (SELECT column_name 266
FROM user_tab_columns 267
WHERE table_name = v_table_name268
ORDER BY column_id269
)270
LOOP271
IF v_column_list IS NOT NULL THEN272
v_column_list := v_column_list || ', ';273
v_new_column_list := v_new_column_list || ', ';274
END IF;275
276
v_column_list := v_column_list || r.column_name || chr(13);277
v_new_column_list := v_new_column_list || ':NEW.' || r.column_name || chr(13);278
END LOOP;279
280
-- Check if the history table contains the columns valid_to and valid_from281
SELECT COUNT(*)282
INTO v_count283
FROM user_tab_columns284
WHERE LOWER(table_name) = LOWER(v_table_name) 285
AND SUBSTR(LOWER(column_name), 6) IN ('valid_to', 'valid_from')286
;287
288
-- get primary key of table289
SELECT MIN(column_name)290
INTO v_primary_key_name_trg291
FROM user_tab_columns292
WHERE LOWER(table_name) = LOWER(v_table_name) 293
AND lower(column_name) LIKE '%\_id' ESCAPE '\'294
AND lower(column_name) != 'h_id' 295
AND lower(data_type) = 'number'296
;297
298
-- Create the trigger if conditions are met299
IF v_count = 2 THEN300
301
302
303
v_sql := 'CREATE OR REPLACE TRIGGER ' || v_trigger_name ||chr(13)||304
'AFTER INSERT OR UPDATE ON ' || v_table_name ||chr(13)||305
'FOR EACH ROW ' ||chr(13)||306
'DECLARE ' ||chr(13)||307
' v_check_get_old_h_id NUMBER; ' ||chr(13)||308
' v_get_old_h_id NUMBER; ' ||chr(13)|| 309
'BEGIN ' ||chr(13)|| chr(13)|| chr(13)|| 310
' ' ||chr(13)|| 311
' SELECT COUNT(*) ' ||chr(13)|| 312
' INTO v_check_get_old_h_id ' ||chr(13)|| 313
' FROM dual ' ||chr(13)|| 314
' WHERE EXISTS (SELECT * ' ||chr(13)|| 315
' FROM '||v_table_history_name ||chr(13)|| 316
' WHERE '||v_primary_key_name_trg||' = :new.'||v_primary_key_name_trg ||chr(13)|| 317
' ) ' ||chr(13)|| 318
' ; ' ||chr(13)|| 319
320
' ' ||chr(13)|| chr(13)|| 321
' IF v_check_get_old_h_id = 1 THEN ' ||chr(13)||chr(13)|| 322
' SELECT MAX(h_id) ' ||chr(13)||323
' INTO v_get_old_h_id ' ||chr(13)||324
' FROM '||v_table_history_name ||chr(13)||325
' WHERE '||v_primary_key_name_trg||' = :new.'||v_primary_key_name_trg ||chr(13)||326
' ; ' ||chr(13)||chr(13)||327
328
' UPDATE '||v_table_history_name ||chr(13) ||329
' SET '||SUBSTR(v_primary_key_name_trg, 1, 4)||'_valid_to = :new.'||SUBSTR(v_primary_key_name_trg, 1, 4)||'_valid_from - INTERVAL ''1'' SECOND' ||chr(13) ||330
' WHERE h_id = v_get_old_h_id ' ||chr(13) ||331
' ; ' ||chr(13) ||332
333
334
' END IF; ' ||chr(13) ||335
336
337
338
339
' ' ||chr(13)||chr(13)||340
' INSERT INTO ' || v_table_history_name || ' (' || v_column_list || ') ' ||chr(13)||341
' VALUES (' || v_new_column_list || '); ' ||chr(13)||342
'END;';343
344
EXECUTE IMMEDIATE v_sql;345
346
DBMS_OUTPUT.PUT_LINE('Trigger successfully created: ' || v_trigger_name);347
ELSE348
DBMS_OUTPUT.PUT_LINE('Required columns (valid_to, valid_from) not found in ' || v_table_name);349
END IF;350
351
END LOOP;352
353
end if;354
355
end pr_generate_history;Usage
After setting up the history table, sequence, and trigger, you should now be able to track all changes to the main table.
SQL
xxxxxxxxxx19
1
declare2
l_table_list apex_t_varchar2;3
begin4
l_table_list := apex_t_varchar2(5
'H_ARTICLE' 6
, 'H_CUSTOMER'7
, 'H_EMPLOYEE' 8
, 'H_TEST' 9
10
); 11
12
SH_UTIL_PKG.pr_generate_history(13
pi_table_name => l_table_list 14
, pi_delete_yn => 'NO'15
, pi_create_yn => 'YES'16
, pi_trigger_yn => 'YES'17
);18
19
end;Conclusion
Implementing a history table with sequences and triggers ensures that all data changes are logged automatically, reducing manual effort and improving traceability. This approach is essential for organizations that prioritize data integrity, transparency, and compliance.
Start setting up your history tables today to ensure a more robust and secure data management system. Let me know your thoughts or questions in the comments below!
{fullWidth}