Monitoring changes in database tables data is always a pain in the ass for many developers. It was even for me until recently, when I had to create a dozen of tables which should have save history of changes. Saving the history was not a big deal but how to show differences in a clear (and easy as possible way) was a real challenge for me. I was able to do hard codding for each table, but I know that must be done in a more comfort way.
First have to point that browsing history is always related on one record an its data changes. If you have in mind that many columns may be changed in one update as well as many changes might be done on the same record in several attempts, finding only which data has been changed and present it in very clear way was a real problem.
I know that all presented here could be done much better and more "proffi" but in mine case it works ... fast and with no bugs. Because solution use analytic functions, only 10g and on wards database are supported.
Create demo environment
Demo environment will be based on famous EMP table who will have only 4 fields for easines of presenting main points.
Regardless EMP table is the main, I'll start example with EMP_HIS table, which is history table for EMP table. History table means that this table will hold how records were looking before change (delete) action takes place in parent, EMP table. Because EMP table has built in triggers which reference EMP_HIS table, this table is shown first. Here is the whole create table script:
DROP TABLE EMP_HIS CASCADE CONSTRAINTS;
CREATE TABLE EMP_HIS (
ID NUMBER NOT NULL,
HIS_ACTION VARCHAR2(1 CHAR),
HIS_DATE DATE,
HIS_USER VARCHAR2(32 CHAR),
ENAME VARCHAR2(10),
HIREDATE DATE,
SAL NUMBER(7,2),
CREATE_DATE DATE NOT NULL,
CREATE_USER VARCHAR2(32 CHAR) NOT NULL,
EDIT_DATE DATE,
EDIT_USER VARCHAR2(32 CHAR)
) ;
This table is allmost the same as EMP with additional three fields (which are highlighted). This table should not have any index (or relation on EMP table) because history must not interfere with EMP table which is important only!
OK, let us go to mentioned, EMP table definition:
ALTER TABLE EMP DROP PRIMARY KEY CASCADE;
DROP TABLE EMP CASCADE CONSTRAINTS;
CREATE TABLE EMP (
ID NUMBER NOT NULL,
ENAME VARCHAR2(10 CHAR),
HIREDATE DATE,
SAL NUMBER(7,2),
CREATE_DATE DATE NOT NULL,
CREATE_USER VARCHAR2(32 CHAR) NOT NULL,
EDIT_DATE DATE,
EDIT_USER VARCHAR2(32 CHAR)
);
Next, I'll add primary key-in mine case this is ID column (I adore surrogate PK's). Have to accent importance of having primary key Otherwise there is no way to reference master record (ROWID may be changed on table move or rebuild process!). Another reasonable restriction is that in this solution primary key CANNOT be modified.
ALTER TABLE EMP ADD (
CONSTRAINT EMP_PK
PRIMARY KEY (ID)
USING INDEX EMP_PK
ENABLE VALIDATE
);
At the end add two triggers to EMP table. "before insert", which will assign only create_date and create_user fields (based on insert actions):
CREATE OR REPLACE TRIGGER EMP_TGBI
BEFORE INSERT ON EMP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
:new.CREATE_DATE:=sysdate;
:new.CREATE_USER:=USER;
end;
/
And "before update/delete" trigger which will be fired on update/delete actions on EMP table, with purpose to save original records to EMP_HIS table
before they are changed or deleted. Here is its declaration:
CREATE OR REPLACE TRIGGER EMP_TGBUD
BEFORE DELETE OR UPDATE ON EMP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
l_his_action VARCHAR2(1 CHAR);
l_user VARCHAR2(32 CHAR);
l_date DATE;
BEGIN
l_date:=sysdate;
l_user:=USER;
IF DELETING THEN
l_his_action := 'D';
ELSIF UPDATING THEN
l_his_action := 'U';
:new.EDIT_DATE := l_date;
:new.EDIT_USER := l_user;
END IF;
INSERT INTO EMP_HIS (
ID,
HIS_ACTION, HIS_DATE, HIS_USER,
ENAME, HIREDATE, SAL,
CREATE_DATE, CREATE_USER, EDIT_DATE, EDIT_USER
)
VALUES (
:old.ID,
l_his_action,l_date,l_user,
:old.ENAME, :old.HIREDATE, :old.SAL,
:old.CREATE_DATE, :old.CREATE_USER, :old.EDIT_DATE, :old.EDIT_USER
)
;
END;
/
Now when tables are ready, let's add some records in EMP table:
truncate table EMP drop storage;
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7369,'SMITH',to_date('17-12-1980','dd-mm-yyyy'),800);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7499,'ALLEN',to_date('20-02-1981','dd-mm-yyyy'),1600);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7521,'WARD',to_date('22-02-1981','dd-mm-yyyy'),1250);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7566,'JONES',to_date('02-04-1981','dd-mm-yyyy'),2975);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7654,'MARTIN',to_date('28-09-1981','dd-mm-yyyy'),1250);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7698,'BLAKE',to_date('01-05-1981','dd-mm-yyyy'),2850);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7782,'CLARK',to_date('09-06-1981','dd-mm-yyyy'),2450);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7788,'SCOTT',to_date('13-07-1987','dd-mm-yyyy'),3000);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7839,'KING',to_date('17-11-1981','dd-mm-yyyy'),5000);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7844,'TURNER',to_date('08-09-1981','dd-mm-yyyy'),1500);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7876,'ADAMS',to_date('13-07-1987','dd-mm-yyyy'),1100);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7900,'JAMES',to_date('03-12-1981','dd-mm-yyyy'),950);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7902,'FORD',to_date('03-12-1981','dd-mm-yyyy'),3000);
INSERT INTO EMP (ID,ENAME,HIREDATE,SAL) VALUES
(7934,'MILLER',to_date('23-01-1982','dd-mm-yyyy'),1300);
COMMIT;
Let us modified some data to have some history:
UPDATE EMP SET SAL=900 WHERE ID=7369;
And after succesfull update, old values are preserved in EMP_HIS table. Just for more consistent image, I'm altering session date value. And here is initial content of EMP_HIS table :
SQL> set linesize 140
SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> col id for 9990
SQL> col HIS_USER for a10
SQL> col CREATE_USER for a10
SQL> col EDIT_USER for a10
SQL> SELECT * FROM EMP_HIS;
ID H HIS_DATE HIS_USER ENAME HIREDATE SAL CREATE_DATE CREATE_USE EDIT_DATE EDIT_USER
----- - ------------------- ---------- ---------- ------------------- ------ ------------------- ---------- ------------------- ----------
7369 U 26.01.2012 14:58:32 TAB_APEX SMITH 17.12.1980 00:00:00 800 26.01.2012 14:56:50 TAB_APEX
SQL>
Here are brief explanation of first 4 columns:
- ID column shows us foreign key relation to PK in EMP table (EMP.ID column).
- HIS_ACTION column (represented as "H"), which may be D(elete) or U(pdate), tells us what action was performed.
- HIS_DATE is the date when this change happened (intentionally added to distinct previously saved edit_date value which might have value)
- HIS_USER is the user who has make that change. This field was needed to distinct previously saved edit_user value which might have different value.
Let us make another change to the same record-two separate changes:
SQL> UPDATE EMP SET SAL=1100 WHERE ID=7369;
1 row updated.
SQL> UPDATE EMP SET ENAME='MR. SMITH' WHERE ID=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL>
Let us see again EMP_HIS table:
SQL> SELECT * FROM EMP_HIS;
ID H HIS_DATE HIS_USER ENAME HIREDATE SAL CREATE_DATE CREATE_USE EDIT_DATE EDIT_USER
----- - ------------------- ---------- ---------- ------------------- ------ ------------------- ---------- ------------------- ----------
7369 U 26.01.2012 14:58:32 TAB_APEX SMITH 17.12.1980 00:00:00 800 26.01.2012 14:56:50 TAB_APEX
7369 U 26.01.2012 15:11:46 TAB_APEX SMITH 17.12.1980 00:00:00 900 26.01.2012 14:56:50 TAB_APEX 26.01.2012 14:58:32 TAB_APEX
7369 U 26.01.2012 15:11:46 TAB_APEX SMITH 17.12.1980 00:00:00 1100 26.01.2012 14:56:50 TAB_APEX 26.01.2012 15:11:46 TAB_APEX
As you see three records are present (one for each command). And for the end add two changes in one statement to same record. Return name to "SMITH" and rise again salary (good worker!):
SQL> UPDATE EMP SET ENAME='SMITH', SAL=1200 WHERE ID=7369;
1 row updated.
SQL> commit;
Commit complete.
SQL>
And look again in EMP_HIS table to see the content:
SQL> SELECT * FROM EMP_HIS;
ID H HIS_DATE HIS_USER ENAME HIREDATE SAL CREATE_DATE CREATE_USE EDIT_DATE EDIT_USER
----- - ------------------- ---------- ---------- ------------------- ------ ------------------- ---------- ------------------- ----------
7369 U 26.01.2012 14:58:32 TAB_APEX SMITH 17.12.1980 00:00:00 800 26.01.2012 14:56:50 TAB_APEX
7369 U 26.01.2012 15:11:46 TAB_APEX SMITH 17.12.1980 00:00:00 900 26.01.2012 14:56:50 TAB_APEX 26.01.2012 14:58:32 TAB_APEX
7369 U 26.01.2012 15:11:46 TAB_APEX SMITH 17.12.1980 00:00:00 1100 26.01.2012 14:56:50 TAB_APEX 26.01.2012 15:11:46 TAB_APEX
7369 U 26.01.2012 15:21:10 TAB_APEX MR. SMITH 17.12.1980 00:00:00 1100 26.01.2012 14:56:50 TAB_APEX 26.01.2012 15:11:46 TAB_APEX
Four rows are now present in EMP_HIS table, what confirms that rows are added on statement levele not independant change of columns in table. This propertiy will be essential when we come to codding how to show change history. If you look closely it is very hard to see where changes occur and imagine a table with many fields ... almost impossible task for real human eye.
History GTT table
Now that we have real data examples it is time to add some aditional objects, required for this solution. First is EMP_HIS_GTT (global temporary table) which will have the same structure as EMP_HIS table. Idea to have GTT table is based on fact that history tables might have huge number of records and because they are not indexed in any way (to speed up DML on parent table), querying them without control may lead to performance problem. So EMP_HIS_GTT table will hold in one moment
only data for select record from EMP table and all other actions will be performed on GTT not HIS table. Let me tell that in mine case I found the most suitable CACHE option enabled for GTT tables, but in your case this might differ-test! Here is the creation script for EMP_HIS_GTT global temporary table:
DROP TABLE EMP_HIS_GTT CASCADE CONSTRAINTS;
CREATE GLOBAL TEMPORARY TABLE EMP_HIS_GTT (
ID NUMBER NOT NULL,
HIS_ACTION VARCHAR2(1 CHAR),
HIS_DATE DATE,
HIS_USER VARCHAR2(32 CHAR),
ENAME VARCHAR2(10),
HIREDATE DATE,
SAL NUMBER(7,2),
CREATE_DATE DATE NOT NULL,
CREATE_USER VARCHAR2(32 CHAR) NOT NULL,
EDIT_DATE DATE,
EDIT_USER VARCHAR2(32 CHAR)
)
ON COMMIT DELETE ROWS
CACHE;
Once again GTT tables are not necessary but I find them very convenient regardless its usage make all a little more complex.
PL/SQL work
Now is time to make some PL/SQL codding which will handle all data in a proper way. Idea for that is taken from
Difference between two rows blog post, which I in this way sent many thanks for that work.
First we need to create two types, which will be used as result for package functions.
o_column_value_difference will hold one record with two values:
- column_value (in mine language-stupac)
- change_pair (in mine language-par_promjene
and a table
c_column_value_difference as a set of
o_column_value_difference records. Here are those two declarations:
DROP TYPE O_COLUMN_VALUE_DIFFERENCE FORCE;
DROP TYPE C_COLUMN_VALUE_DIFFERENCE FORCE;
CREATE OR REPLACE TYPE o_column_value_difference is object (
stupac varchar2(30)
,par_promjene varchar2(4000)
)
/
CREATE OR REPLACE TYPE c_column_value_difference is table
of o_column_value_difference
/
The main code is bundled in one package
COLUMN_DIFF for easier maintainance. Owner of this package must have SELECT privilege on
dba_tab_columns view. The best is to place this code to some "special owner" where other dba utilities are set (i.e. TOOLS schema) and grant execute on package to PUBLIC.
Package has only two overloaded functions, which differ in arguments definition, but both have the same meaning-result set. Here is the source code (package declaration):
CREATE OR REPLACE PACKAGE column_diff IS
FUNCTION show_column_diffs (
p_owner in varchar2
,p_table_name in varchar2
,p_key_value_pair in varchar2
,p_excluded_columns_list in varchar2
) RETURN c_column_value_difference;
FUNCTION show_column_diffs (
p_owner in varchar2
,p_table_name in varchar2
,p_rowid_a in rowid
,p_rowid_b in rowid
,p_excluded_columns_list in varchar2
) RETURN c_column_value_difference;
END column_diff;
/
First function declaration is based on "p_key_value_pair" parameter which must look like this:
emp_id=1:emp_ver_no=1|emp_id=1:emp_ver_no=2
so the routine will change single quotes to double quotes for you and add the WHERE and AND components to finish up a set of where clauses. Please note we are using the pipe (|) and colon (:) as delimiters so if you are using date formats make sure not to use either in your format string it is expected that each expression yeilds one row thus you should be supplying primary or unique key data.
Second function is based on ROWID (do not mix with previously said for ROWID's because this is totally different ROWID) and have for that two parameters:
- p_rowid_a
- p_rowid_b
which has the similar meaning as single "p_key_value_pair" parameter in first case. This one I prefer more.
Here is the code of
column_diff package body:
CREATE OR REPLACE PACKAGE BODY column_diff
IS
FUNCTION show_column_diffs (p_owner IN VARCHAR2,
p_table_name IN VARCHAR2,
p_rowid_a IN ROWID,
p_rowid_b IN ROWID,
p_excluded_columns_list IN VARCHAR2)
RETURN c_column_value_difference
IS
BEGIN
RETURN show_column_diffs (
p_owner,
p_table_name,
'rowid=''' || p_rowid_a || '''|rowid=''' || p_rowid_b || '''',
p_excluded_columns_list
);
END;
-- main func
FUNCTION show_column_diffs (p_owner IN VARCHAR2,
p_table_name IN VARCHAR2,
p_key_value_pair IN VARCHAR2,
p_excluded_columns_list IN VARCHAR2)
RETURN c_column_value_difference
IS
where_string_1_v VARCHAR2 (4000);
where_string_2_v VARCHAR2 (4000);
sql_v VARCHAR2 (32000);
col_expression_v VARCHAR2 (32000);
c_column_value_difference_v c_column_value_difference
:= c_column_value_difference ();
c_column_value_difference_f_v c_column_value_difference
:= c_column_value_difference ();
BEGIN
where_string_1_v := SUBSTR (p_key_value_pair, 1, INSTR (p_key_value_pair, '|') - 1);
where_string_1_v := 'a.' || REPLACE (where_string_1_v, ':', ' and a.');
where_string_2_v := SUBSTR (p_key_value_pair, INSTR (p_key_value_pair, '|') + 1);
where_string_2_v := 'b.' || REPLACE (where_string_2_v, ':', ' and b.');
FOR i IN 1 .. 100 LOOP
sql_v := NULL;
FOR r1 IN (SELECT column_name, data_type
FROM dba_tab_columns
WHERE owner = p_owner AND table_name = p_table_name
AND data_type IN ('DATE', 'NUMBER', 'INTEGER', 'PLS_INTEGER', 'VARCHAR2', 'CHAR')
AND INSTR (
',' || UPPER (p_excluded_columns_list) || ',',
',' || column_name || ','
) = 0
AND column_id BETWEEN (i - 1) * 10 + 1 AND (i * 10)
ORDER BY column_name) LOOP
IF r1.data_type IN ('DATE', 'NUMBER', 'INTEGER', 'PLS_INTEGER', 'VARCHAR2', 'CHAR') THEN
col_expression_v :=
''''
|| r1.column_name
|| ''''
|| ' column_name,substr(a.'
|| LOWER (r1.column_name)
|| '||''->''||b.'
|| LOWER (r1.column_name)
|| ',1,4000) data_value_pair';
ELSE
col_expression_v :=
''''
|| r1.column_name
|| ''''
|| ' column_name,substr(nvl(to_char(a.'
|| LOWER (r1.column_name)
|| ',''dd.mm.yyyy hh24:mi:ss''),lpad('' '',20,'' ''))||''->''||nvl(to_char(b.'
|| LOWER (r1.column_name)
|| ',''dd.mm.yyyy hh24:mi:ss''),lpad('' '',20,'' '')),1,4000) data_value_pair';
END IF;
-- AUTO_LOG_ERROR_DEBUG ('col_expression_v="'||col_expression_v||'"');
sql_v :=
sql_v
|| ' union all select '
|| col_expression_v
|| ' from '
|| p_owner
|| '.'
|| p_table_name
|| ' a,'
|| p_owner
|| '.'
|| p_table_name
|| ' b where '
|| where_string_1_v
|| ' and '
|| where_string_2_v
|| ' and decode(a.'
|| r1.column_name
|| ',b.'
|| r1.column_name
|| ',0,1) = 1';
END LOOP;
-- AUTO_LOG_ERROR_DEBUG ('sql_v="'||sql_v||'"');
IF sql_v IS NOT NULL THEN
sql_v :=
'select cast(multiset(select * from ( '
|| SUBSTR (sql_v, 11)
|| ' )) as c_column_value_difference ) from dual';
-- AUTO_LOG_ERROR_DEBUG ('prije execute sql_v="'||sql_v||'"');
EXECUTE IMMEDIATE sql_v INTO c_column_value_difference_v;
SELECT CAST (
MULTISET (
SELECT *
FROM ( SELECT *
FROM TABLE (
CAST (
c_column_value_difference_v AS c_column_value_difference)
)
UNION ALL
SELECT *
FROM TABLE (
CAST (
c_column_value_difference_f_v AS c_column_value_difference)))) AS c_column_value_difference
)
INTO c_column_value_difference_f_v
FROM DUAL;
END IF;
END LOOP;
IF c_column_value_difference_f_v.LAST IS NULL THEN
c_column_value_difference_f_v.EXTEND;
c_column_value_difference_f_v (c_column_value_difference_f_v.LAST) := o_column_value_difference (NULL, NULL);
END IF;
RETURN (c_column_value_difference_f_v);
END;
END column_diff;
/
The beauty of this solution is dynamic content generation-any table structure is accepted without special knowledge of it before execution happened.
Live demo
Now when all is setup, time is to query history for real. The whole process is processed in two logical steps:
- Filling data from EMP_HIST and EMP tables to EMP_HIST_GTT (for one chosen record of EMP table)
- Showing data from EMP_HIST_GTT table with column_diff function
In our case we have made only changes for EMP with id=7369, so this is the constant in this example. In real example use binding variables.
Just to mention that always real data (EMP table)
MUST comes before union with HIS table data. Here is the example of step 1:
SQL> INSERT INTO EMP_HIS_GTT (
2 ID, HIS_ACTION, HIS_DATE, HIS_USER,
3 ENAME, HIREDATE,
4 SAL, CREATE_DATE, CREATE_USER,
5 EDIT_DATE, EDIT_USER
6 )
7 (
8 SELECT ID, null, null, null,
9 ENAME, HIREDATE,
10 SAL, CREATE_DATE, CREATE_USER,
11 EDIT_DATE, EDIT_USER
12 FROM EMP
13 WHERE ID = 7369
14 UNION ALL
15 SELECT
16 ID, HIS_ACTION, HIS_DATE, HIS_USER,
17 ENAME, HIREDATE,
18 SAL, CREATE_DATE, CREATE_USER,
19 EDIT_DATE, EDIT_USER
20 FROM EMP_HIS
21 WHERE ID = 7369 AND HIS_ACTION = 'U'
22 )
23 ;
5 rows created.
As espected (1+4 rows) are added to GTT. And now query GTT table with shown function from package. This is that step in action:
SQL> break on id on a_his_date skip 1
SQL> col PAR_PROMJENE for a30
SQL>
SQL> SELECT a.id,
2 a_his_date,
3 a_his_user,
4 b.*
5 FROM (SELECT LAG (his_date) OVER (PARTITION BY id ORDER BY his_date)
6 a_his_date,
7 LAG (his_user) OVER (PARTITION BY id ORDER BY his_date)
8 a_his_user,
9 LAG (ROWID) OVER (PARTITION BY id ORDER BY his_date) a_rowid,
10 ROWID b_rowid,
11 EMP_HIS_GTT.*
12 FROM EMP_HIS_GTT
13 WHERE id = 7369
14 ) a,
15 TABLE ( CAST (
16 tab_apex.column_diff.show_column_diffs (
17 'TAB_APEX',
18 'EMP_HIS_GTT',
19 a_rowid,
20 b_rowid,
21 'HIS_USER,HIS_ACTION,HIS_DATE,EDIT_USER,EDIT_DATE,CREATE_DATE') AS c_column_value_difference)
22 ) b
23 WHERE NOT A_HIS_DATE IS NULL
24 ORDER BY a_his_date;
ID A_HIS_DATE A_HIS_USER STUPAC PAR_PROMJENE
---------- ------------------- -------------------------------- ------------------------------ ------------------------------
7369 26.01.2012 14:58:32 TAB_APEX SAL 800->900
26.01.2012 15:11:46 TAB_APEX SAL 900->1100
TAB_APEX ENAME SMITH->MR. SMITH
26.01.2012 15:21:10 TAB_APEX ENAME MR. SMITH->SMITH
TAB_APEX SAL 1100->1200
SQL>
Because first part in select is fixed, small explanation for function call arguments:
- TAB_APEX is user that own EMP_HIS_GTT table.
- a_row_id and a_rowb_id are called from previous subquery-may be fixed in this way
- 'HIS_USER,HIS_ACTION,HIS_DATE,EDIT_USER,EDIT_DATE,CREATE_DATE' is a comma delimited string which holds columns which should not be treated as change
If you look in result, it is really lovely to see all changes in such a consistent way-without any unnecessary information-nice.
How about delete?
Because trigger will assign "D" in HIS_ACTION column, this will help us distinct that event from other UPDATES.
Well if you delete a record, then it doesn't exist in EMP table, so there is no way to join with EMP table. So initialization part (insert records to GTT) should have a little difference look. Here it is code for
deleted records history:
SQL> INSERT INTO EMP_HIS_GTT (
2 ID, HIS_ACTION, HIS_DATE, HIS_USER,
3 ENAME, HIREDATE,
4 SAL, CREATE_DATE, CREATE_USER,
5 EDIT_DATE, EDIT_USER
6 )
7 (
8 SELECT
9 ID, HIS_ACTION, HIS_DATE, HIS_USER,
10 ENAME, HIREDATE,
11 SAL, CREATE_DATE, CREATE_USER,
12 EDIT_DATE, EDIT_USER
13 FROM EMP_HIS
14 WHERE ID = 7369 AND HIS_ACTION = 'D'
15 UNION ALL
16 SELECT
17 ID, HIS_ACTION, HIS_DATE, HIS_USER,
18 ENAME, HIREDATE,
19 SAL, CREATE_DATE, CREATE_USER,
20 EDIT_DATE, EDIT_USER
21 FROM EMP_HIS
22 WHERE ID = 7369 AND HIS_ACTION = 'D'
23 UNION ALL
24 SELECT
25 ID, HIS_ACTION, HIS_DATE, HIS_USER,
26 ENAME, HIREDATE,
27 SAL, CREATE_DATE, CREATE_USER,
28 EDIT_DATE, EDIT_USER
29 FROM EMP_HIS
30 WHERE ID = 7369 AND HIS_ACTION = 'U'
31 )
32 ;
6 rows created.
SQL>
Here is important that the same query at the beginning is intentionally executed
twice and both on EMP_HIS table (instead od EMP as it was for update history query). At the end (as in update part history) updates records from EMP_HIS are added.
Second step is present history in more human way with our package. Changes according browsing history on update part are highlighted.
SQL> SELECT a.id,
2 a_his_action,
3 a_his_date,
4 a_his_user,
5 b.*
6 FROM (SELECT LAG (his_action) OVER (PARTITION BY id ORDER BY his_date)
7 a_his_action,
8 LAG (his_date) OVER (PARTITION BY id ORDER BY his_date)
9 a_his_date,
10 LAG (his_user) OVER (PARTITION BY id ORDER BY his_date)
11 a_his_user,
12 LAG (ROWID) OVER (PARTITION BY id ORDER BY his_date) a_rowid,
13 ROWID b_rowid,
14 EMP_HIS_GTT.*
15 FROM EMP_HIS_GTT
16 WHERE id = 7369
17 ) a,
18 TABLE ( CAST (
19 tab_apex.column_diff.show_column_diffs (
20 'TAB_APEX',
21 'EMP_HIS_GTT',
22 a_rowid,
23 b_rowid,
24 'HIS_USER,HIS_ACTION,HIS_DATE,EDIT_USER,EDIT_DATE,CREATE_DATE') AS c_column_value_difference)
25 ) b
26 WHERE NOT A_HIS_DATE IS NULL
27 ORDER BY a_his_date;
ID A A_HIS_DATE A_HIS_USER STUPAC PAR_PROMJENE
---------- - ------------------- ------------- ---------- ------------------------------
7369 U 26.01.2012 14:58:32 TAB_APEX SAL 800->900
U 26.01.2012 15:11:46 TAB_APEX SAL 900->1100
U TAB_APEX ENAME SMITH->MR. SMITH
U 26.01.2012 15:21:10 TAB_APEX ENAME MR. SMITH->SMITH
U TAB_APEX SAL 1100->1200
D 26.01.2012 22:32:41 TAB_APEX
6 rows selected.
SQL>
The End
And if you ask yourself how to know when to show form with history data, answer is easy-it must have DATE_EDIT value not null (in EMP table). If performance allow, add also condition that related child record must exist in EMP_HIST table. Here is small example for that (before 7369 was deleted):
SQL> SELECT id,
2 CASE WHEN edit_date IS NULL THEN 'N'
3 ELSE 'Y'
4 END EDITED,
5 ENAME,
6 HIREDATE,
7 SAL,
8 FROM EMP;
ID EDITED ENAME HIREDATE SAL
---- ------ ---------- ------------------- ------
7369 Y SMITH 17.12.1980 00:00:00 1200
7499 N ALLEN 20.02.1981 00:00:00 1600
7521 N WARD 22.02.1981 00:00:00 1250
7566 N JONES 02.04.1981 00:00:00 2975
7654 N MARTIN 28.09.1981 00:00:00 1250
7698 N BLAKE 01.05.1981 00:00:00 2850
7782 N CLARK 09.06.1981 00:00:00 2450
7788 N SCOTT 13.07.1987 00:00:00 3000
7839 N KING 17.11.1981 00:00:00 5000
7844 N TURNER 08.09.1981 00:00:00 1500
7876 N ADAMS 13.07.1987 00:00:00 1100
7900 N JAMES 03.12.1981 00:00:00 950
7902 N FORD 03.12.1981 00:00:00 3000
7934 N MILLER 23.01.1982 00:00:00 1300
14 rows selected.
SQL>
So creating views with such a derived column is really not to much work.
Hope this helps someone.
Cheers!
P.S.
I'm pretty sure that if you want to stay with standard naming convention, i.e.
, _his and _his_gtt, that some additional automation may be done. Maybe someone has a time and will to make such an effort ... looking forward to that one-really!
