.png)
Introduction
In this blog post, we will explore how to create a history table in a database, which is incredibly useful for tracking changes over time. This setup includes creating a sequence for unique identifiers and a trigger to handle the automatic logging of changes.
Why a History Table?
A history table is essential when you need to track changes to data over time. For example, if customer data such as address or phone number changes, a history table allows you to see what the data was like before the changes. This can help in understanding data trends, auditing, and compliance.
Table Structure
We start with a standard table setup. Here, we assume a simple customer table with fields like ID, name, address, and phone number.
SQL
CREATE TABLE TEST
( TEST_UPLOAD BLOB,
TEST_MIME_TYPE VARCHAR2(4000),
TEST_NAME VARCHAR2(4000),
TEST_ID NUMBER,
A NUMBER,
AA NUMBER,
AB NUMBER,
FDAS NUMBER,
AFDSAFDS NUMBER,
TEST_VALID_FROM NUMBER DEFAULT 5 NOT NULL ENABLE,
TEST_VALID_TO NUMBER DEFAULT 4
) ;Specifications
Next, we define the sequence and trigger specifications to handle automatic ID generation and capturing data changes.
SQL
/*=============================
===== history trigger =====
============================= */
procedure pr_generate_history(
pi_table_name IN apex_t_varchar2
, pi_delete_yn IN varchar2 default 'NO'
, pi_create_yn in varchar2 default 'NO'
, pi_trigger_yn in varchar2 default 'NO'
);Trigger Body
The body of the trigger will specify what happens when data changes—specifically how the history table captures these changes.
SQL
/*=============================
===== history trigger =====
============================= */
procedure pr_generate_history(
pi_table_name IN apex_t_varchar2
, pi_delete_yn IN varchar2 default 'NO'
, pi_create_yn in varchar2 default 'NO'
, pi_trigger_yn in varchar2 default 'NO'
)
is
-- global
v_count_total_table number := pi_table_name.COUNT;
v_table_name varchar2(4000);
v_table_history_name varchar2(4000);
v_sequenz_name varchar2(4000);
v_trigger_name varchar2(4000);
-- 2. create history table
v_main_table_name varchar2(4000);
v_check_history_table_exists_yn number;
v_check_sequence_exists_yn number;
v_count number;
v_sql varchar2(4000);
v_h_id_count number;
v_h_created_count number;
-- 3. trigger
v_original_table_name varchar2(4000);
v_primary_key_name_trg varchar2(4000);
v_column_name user_tab_columns.column_name%TYPE;
v_column_list VARCHAR2(4000) := '';
v_new_column_list VARCHAR2(4000) := '';
v_primary_key_count NUMBER;
v_primary_key_name VARCHAR2(4000);
begin
-- 1. delete =============================================================
if pi_delete_yn = 'YES' then
-- 1. ========= delete table ======
FOR i IN 1..v_count_total_table LOOP
-- save table name
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
BEGIN
-- drop table
EXECUTE IMMEDIATE 'DROP TABLE ' || v_table_history_name;
-- successfully deleted
DBMS_OUTPUT.PUT_LINE('Table is successfully deleted: ' || v_table_history_name);
-- if error
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed to delete Table: ' || v_table_history_name || '. Error: ' || SQLERRM);
END;
END LOOP;
-- 2. ========= delete seq ======
FOR i IN 1..v_count_total_table LOOP
-- save sequenz name
v_table_history_name := 'H_'||pi_table_name(i);
v_sequenz_name := v_table_history_name||'_SEQ';
BEGIN
-- drop table
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || v_sequenz_name;
-- successfully deleted
DBMS_OUTPUT.PUT_LINE('Sequence is successfully deleted: ' || v_sequenz_name);
-- if error
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed to delete Sequence: ' || v_sequenz_name || '. Error: ' || SQLERRM);
END;
END LOOP;
-- 3. ========= delete trigger ======
FOR i IN 1..v_count_total_table LOOP
-- save trigger name
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
v_trigger_name := v_table_history_name||'_TRG';
BEGIN
-- drop table
EXECUTE IMMEDIATE 'DROP TRIGGER ' || v_trigger_name;
-- successfully deleted
DBMS_OUTPUT.PUT_LINE('trigger is successfully deleted: ' || v_trigger_name);
-- if error
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Failed to delete trigger: ' || v_trigger_name || '. Error: ' || SQLERRM);
END;
END LOOP;
end if;
if pi_create_yn = 'YES' then
-- 2.1 create history table =============================================
FOR i IN 1..v_count_total_table LOOP
-- Zuweisung des Tabellennamens aus der Liste
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
-- ===== create if not exists ======================================================
SELECT COUNT(*)
INTO v_check_history_table_exists_yn
FROM user_tables
WHERE LOWER(table_name) = LOWER(v_table_history_name);
IF v_check_history_table_exists_yn = 0 THEN
-- Erstellen der Historientabelle, wenn sie nicht existiert
EXECUTE IMMEDIATE 'CREATE TABLE ' || v_table_history_name || ' AS SELECT * FROM ' || v_table_name || ' WHERE 1=0';
-- successfully message
DBMS_OUTPUT.PUT_LINE('History table successfully created: ' || v_table_history_name);
END IF;
END LOOP;
-- 2.2 create Sequence =============================================
FOR i IN 1..v_count_total_table LOOP
-- Zuweisung des Tabellennamens aus der Liste
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
v_sequenz_name := v_table_history_name||'_SEQ';
-- ===== create if not exists ======================================================
SELECT COUNT(*)
INTO v_check_sequence_exists_yn
FROM user_sequences
WHERE LOWER(sequence_name) = LOWER(v_sequenz_name);
IF v_check_sequence_exists_yn = 0 THEN
-- Erstellen der Sequenz für die Historientabelle
EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || v_sequenz_name || ' START WITH 1 INCREMENT BY 1';
-- successfully message
DBMS_OUTPUT.PUT_LINE('Sequence successfully created: ' || v_sequenz_name);
END IF;
END LOOP;
-- 2.3 add new columns =============================================
FOR i IN 1..v_count_total_table LOOP
-- Zuweisung des Tabellennamens aus der Liste
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
--========= get columns
FOR r IN (SELECT column_name
, data_type
, data_length
FROM user_tab_columns
WHERE LOWER(table_name) = LOWER(v_table_name)
)
LOOP
-- check history table
SELECT COUNT(*)
INTO v_count
FROM user_tab_columns
WHERE LOWER(table_name) = LOWER(v_table_history_name)
AND LOWER(column_name) = LOWER(r.column_name);
IF v_count = 0 THEN
v_sql := 'ALTER TABLE ' || v_table_history_name || ' ADD ' || r.column_name || ' ' || r.data_type;
IF r.data_type IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN
v_sql := v_sql || '(' || r.data_length || ')';
END IF;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('New column ' || r.column_name || ' successfully added to table ' || v_table_history_name || '.');
END IF;
END LOOP;
END LOOP;
-- 2.4 add h_id and h_created =============================================
FOR i IN 1..v_count_total_table LOOP
-- Zuweisung des Tabellennamens aus der Liste
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
--========= put h_id columns and h_created column to history table
SELECT COUNT(*)
INTO v_h_id_count
FROM user_tab_columns
WHERE LOWER(table_name) = LOWER(v_table_history_name) AND LOWER(column_name) = 'h_id'
;
SELECT COUNT(*)
INTO v_h_created_count
FROM user_tab_columns
WHERE LOWER(table_name) = LOWER(v_table_history_name) AND LOWER(column_name) = 'h_created'
;
-- Hinzufügen der Spalten 'h_id' und 'h_created', wenn sie nicht existieren
IF v_h_id_count = 0 OR v_h_created_count = 0 THEN
v_sql := 'ALTER TABLE ' || v_table_history_name || ' ADD (';
IF v_h_id_count = 0 THEN
v_sql := v_sql || 'h_id NUMBER DEFAULT ' || v_table_history_name || '_SEQ.NEXTVAL PRIMARY KEY';
IF v_h_created_count = 0 THEN
v_sql := v_sql || ', ';
END IF;
END IF;
IF v_h_created_count = 0 THEN
v_sql := v_sql || 'h_created TIMESTAMP DEFAULT SYSDATE';
END IF;
v_sql := v_sql || ')';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Columns h_id and/or h_created added to the table ' || v_table_history_name || '.');
END IF;
END LOOP;
end if;
-- 3. trigger ==================================================
if pi_trigger_yn = 'YES' then
FOR i IN 1..v_count_total_table LOOP
-- Assign the table name from the list
v_table_name := pi_table_name(i);
v_table_history_name := 'H_'||pi_table_name(i);
v_trigger_name := v_table_history_name || '_TRG';
-- Reset the column list for each iteration
v_column_list := '';
v_new_column_list := '';
-- Query column information
FOR r IN (SELECT column_name
FROM user_tab_columns
WHERE table_name = v_table_name
ORDER BY column_id
)
LOOP
IF v_column_list IS NOT NULL THEN
v_column_list := v_column_list || ', ';
v_new_column_list := v_new_column_list || ', ';
END IF;
v_column_list := v_column_list || r.column_name || chr(13);
v_new_column_list := v_new_column_list || ':NEW.' || r.column_name || chr(13);
END LOOP;
-- Check if the history table contains the columns valid_to and valid_from
SELECT COUNT(*)
INTO v_count
FROM user_tab_columns
WHERE LOWER(table_name) = LOWER(v_table_name)
AND SUBSTR(LOWER(column_name), 6) IN ('valid_to', 'valid_from')
;
-- get primary key of table
SELECT MIN(column_name)
INTO v_primary_key_name_trg
FROM user_tab_columns
WHERE LOWER(table_name) = LOWER(v_table_name)
AND lower(column_name) LIKE '%\_id' ESCAPE '\'
AND lower(column_name) != 'h_id'
AND lower(data_type) = 'number'
;
-- Create the trigger if conditions are met
IF v_count = 2 THEN
v_sql := 'CREATE OR REPLACE TRIGGER ' || v_trigger_name ||chr(13)||
'AFTER INSERT OR UPDATE ON ' || v_table_name ||chr(13)||
'FOR EACH ROW ' ||chr(13)||
'DECLARE ' ||chr(13)||
' v_check_get_old_h_id NUMBER; ' ||chr(13)||
' v_get_old_h_id NUMBER; ' ||chr(13)||
'BEGIN ' ||chr(13)|| chr(13)|| chr(13)||
' ' ||chr(13)||
' SELECT COUNT(*) ' ||chr(13)||
' INTO v_check_get_old_h_id ' ||chr(13)||
' FROM dual ' ||chr(13)||
' WHERE EXISTS (SELECT * ' ||chr(13)||
' FROM '||v_table_history_name ||chr(13)||
' WHERE '||v_primary_key_name_trg||' = :new.'||v_primary_key_name_trg ||chr(13)||
' ) ' ||chr(13)||
' ; ' ||chr(13)||
' ' ||chr(13)|| chr(13)||
' IF v_check_get_old_h_id = 1 THEN ' ||chr(13)||chr(13)||
' SELECT MAX(h_id) ' ||chr(13)||
' INTO v_get_old_h_id ' ||chr(13)||
' FROM '||v_table_history_name ||chr(13)||
' WHERE '||v_primary_key_name_trg||' = :new.'||v_primary_key_name_trg ||chr(13)||
' ; ' ||chr(13)||chr(13)||
' UPDATE '||v_table_history_name ||chr(13) ||
' 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) ||
' WHERE h_id = v_get_old_h_id ' ||chr(13) ||
' ; ' ||chr(13) ||
' END IF; ' ||chr(13) ||
' ' ||chr(13)||chr(13)||
' INSERT INTO ' || v_table_history_name || ' (' || v_column_list || ') ' ||chr(13)||
' VALUES (' || v_new_column_list || '); ' ||chr(13)||
'END;';
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.PUT_LINE('Trigger successfully created: ' || v_trigger_name);
ELSE
DBMS_OUTPUT.PUT_LINE('Required columns (valid_to, valid_from) not found in ' || v_table_name);
END IF;
END LOOP;
end if;
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
declare
l_table_list apex_t_varchar2;
begin
l_table_list := apex_t_varchar2(
'H_ARTICLE'
, 'H_CUSTOMER'
, 'H_EMPLOYEE'
, 'H_TEST'
);
SH_UTIL_PKG.pr_generate_history(
pi_table_name => l_table_list
, pi_delete_yn => 'NO'
, pi_create_yn => 'YES'
, pi_trigger_yn => 'YES'
);
end;Conclusion
With the structure set up as specified, you should now have a functioning history table where the ID is managed by a sequence, and changes are automatically logged by a trigger. This setup ensures that any modifications to your main data table are traceable and that the historical integrity of your data is maintained.
{fullWidth}
Posts
0 $type={blogger}:
Kommentar veröffentlichen