Wednesday, February 1, 2012

Browsing history data changes for tables-how to



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:
  1. ID column shows us foreign key relation to PK in EMP table (EMP.ID column).
  2. HIS_ACTION column (represented as "H"), which may be D(elete) or U(pdate), tells us what action was performed.
  3. HIS_DATE is the date when this change happened (intentionally added to distinct previously saved edit_date value which might have value)
  4. 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:
  1. p_rowid_a
  2. 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:
  1. Filling data from EMP_HIST and EMP tables to EMP_HIST_GTT (for one chosen record of EMP table)
  2. 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!

No comments :

Post a Comment

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign