Cost-Effective Method for Logging Changes in Oracle Tables
Joe Kerr
Context
Logging data changes in Oracle tables is crucial for troubleshooting, meeting regulatory and organizational requirements, and providing customer service. Change Data Capture, Streams, and Log Miner were once possible built-in solutions, but Oracle no longer supports them, leaving Golden Gate as the option. While Golden Gate is a strong solution, it might not be cost-effective for small to medium-sized projects that only need a small part of its features. This means creating a custom logging solution could be a good alternative. If that suits your needs, you've come to the right blog!
Objective:
Create functionality that logs table inserts, updates, and deletes in the following manner:
Highly automated log management
Data driven control over what tables and activity is logged
Only log changed values
Use existing oracle functionality to minimize cost
Log activity with triggers to ensure all changes to tables are captured as intended
Outcomes
Let’s begin with the end in mind.
Create a single interface for viewing and managing log data.
Below is an Oracle APEX tabbed interface, with the first tab displaying Log data. This includes the Table, Occurrence, Primary Key Name, Primary Key ID, Action (Create, Update, Delete), a JSON string of column/value pairs for only the changed data, Days since the change occurred, the Calling Program, and the Call stack (not all are shown here). Since it is in APEX, users have many options for presentation, filtering, and sorting. Some example formats are available in the dropdown menu. Data changes/activity are saved to a single Log Table with Table Name, Primary Key ID, and a JSON formatted column of column name/value pairs for only changed data. Log Views are created for each table with appropriate Select and JSON Select/Path entries for viewing the particular table’s activity.
Below is the second tab, “Data Change Log Control,” which manages logging triggers on selected tables using metadata and automated functions. Each table has an entry with a dropdown menu to choose which activities to log (Insert, Update, Delete) in various combinations, along with indicators showing whether the logging trigger and view exist. You can use Effective and Expire dates to activate or deactivate the entry. Note that tables set to No Logging do not have log triggers or views. (More on this in a later callout).
The Row Action menu, with the first table entry selected, allows you to recreate the table’s logging trigger as either a static or dynamic trigger, view the current log trigger’s body/source, delete the log trigger, or delete the log view. Static triggers are “hard-coded” with specific actions (Insert, Update, Delete) enabled, while dynamic triggers include a function to determine actions at runtime.
Below are the available “Include Log Actions.” Choosing an action level determines which row activities are logged, or if no logging is done for the selected table. Logging and system tables are examples where logging might not be needed, appropriate, or could cause system issues. (Logging changes to the Log table would create an endless loop.)
The Include Log Actions values are stored in a table that translates the descriptions shown into individual character combinations (i.e. Create and Delete are both logged so CD using “CRUD” syntax) and indicates whether the entry results in logging or not.
The “Data Change Log Control” tab's “Actions” dropdown menu, shown below, lets you manage triggers and views for all entries at once.
“Refresh Data Change Control Table” creates, updates, or deletes tables on this control tab based on User_Objects, ensuring a one-to-one match. New entries default to log all activities.
Refresh Triggers (Static): Recreates logging triggers with a “hard-coded” definition of which actions are logged for tables where Include Actions indicate logging is on, and deletes logging triggers if they exist and the Include Action is set to No Logging.
Refresh Triggers (Dynamic): Recreates logging triggers with a function call that decides which activity is logged at runtime, where Include Actions indicate logging is on, and deletes logging triggers if they exist and the Include Action is set to No Logging.
In both cases, if the table is not logged, Log Views are not deleted if they exist, in case logging data exists from when logging was on. If the Log View is not needed, it can be removed at the table level Row Menu in the Control Tab.
An example of a dynamic logging trigger on an Address table is:
DECLARE
v_new_json VARCHAR2(32767);
v_old_json VARCHAR2(32767);
v_CRUD_Actions Varchar2(1 CHAR);
v_LG_JSON_Difference Varchar2(32767);
BEGIN
Case
When INSERTING THEN
v_CRUD_Actions := 'C';
When UPDATING THEN
v_CRUD_Actions := 'U';
When DELETING THEN
v_CRUD_Actions := 'D';
END Case;
--
-- NOTE: Dynamic portion that sends table name / Action being performed
-- receiving back Y or N on whether this activity should be logged.
--
If Pkg_SY_Log_Tables_Control.Qry_Log_YN( p_LT_Table_Name => 'ADDRESS'
,p_CRUD_Action => v_CRUD_Actions
) = 'Y' Then
IF INSERTING OR UPDATING THEN
If v_New_JSON Is Null Then
v_New_JSON := '{';
End If;
v_new_json := v_new_json || '"ADDRESS_ID":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.ADDRESS_ID)), 'null') || '"'
|| ',' || '"AD_TYPE_CID":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_TYPE_CID)), 'null') || '"'
|| ',' || '"AD_OWNER_ID":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_OWNER_ID)), 'null') || '"'
|| ',' || '"AD_ADR1":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_ADR1)), 'null') || '"'
|| ',' || '"AD_ADR2":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_ADR2)), 'null') || '"'
|| ',' || '"AD_CITY":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_CITY)), 'null') || '"'
|| ',' || '"AD_POSTAL_CODE":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_POSTAL_CODE)), 'null') || '"'
|| ',' || '"AD_EFFECTIVE":"' || Nvl(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_EFFECTIVE,'MM/DD/YYYY')), 'null') || '"'
|| ',' || '"AD_EXPIRES":"' || Nvl(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.AD_EXPIRES,'MM/DD/YYYY')), 'null') || '"'
|| '}';
END IF;
IF UPDATING OR DELETING THEN
If v_Old_JSON Is Null Then
v_Old_JSON := '{';
End If;
v_old_json := v_old_json || '"ADDRESS_ID":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.ADDRESS_ID)), 'null') || '"'
|| ',' || '"AD_TYPE_CID":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_TYPE_CID)), 'null') || '"'
|| ',' || '"AD_ADR1":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_ADR1)), 'null') || '"'
|| ',' || '"AD_ADR2":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_ADR2)), 'null') || '"'
|| ',' || '"AD_CITY":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_CITY)), 'null') || '"'
|| ',' || '"AD_POSTAL_CODE":"' || NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_POSTAL_CODE)), 'null') || '"'
|| ',' || '"AD_EFFECTIVE":"' || Nvl(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_EFFECTIVE,'MM/DD/YYYY')), 'null') || '"'
|| ',' || '"AD_EXPIRES":"' || Nvl(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.AD_EXPIRES,'MM/DD/YYYY')), 'null') || '"'
|| '}';
END IF;
v_LG_JSON_Difference := SY_JSON_String_Diff(p_JSON_1 => v_New_JSON
,p_JSON_2 => v_Old_JSON
);
If v_LG_JSON_Difference <> '[]' And v_LG_JSON_Difference <> '{}' Then
Pkg_SY_Log_Tables.Ins_Log_Entry( p_LG_Table_Name => 'ADDRESS'
,p_LG_Primary_ID => Nvl(:New.ADDRESS_ID,:Old.ADDRESS_ID)
,p_LG_CRUD_Action => v_CRUD_Actions
,p_LG_JSON_DIFFERENCE => v_LG_JSON_Difference
);
End If;
End If;
EXCEPTION WHEN OTHERS
THEN RAISE;
END;
In summary, the Log Table Control functionality results in
Highly automated log management
Data driven control over what tables and activity is logged
Only log changed values
Use existing oracle functionality to minimize cost
Log activity with triggers to ensure all changes to tables are captured as intended
Building the Functionality
Now for the fun part. How to build it…
Create the Log and Control tables and views.
A single Log Table keeps track of each row's Table Name, Primary ID, Program Call Stack, and the Program that caused the activity. It records the type of change to the row and includes a JSON-formatted string of the column name and value that changed. The effective date is when the entry was created, and the expiration date defaults to 12/31/2999. You can adjust the dates and user tracking according to your method.
Drop Table SY_Log_Tables Cascade Constraints
/
Drop Sequence SY_Log_Tables_ID;
/
Create Sequence SY_Log_Tables_ID MinValue 1 MaxValue 999999999999999999999999999
Start with 1
Increment by 1
NoCache
/
Create Table SY_Log_Tables
(
SY_Log_Tables_ID NUMBER(20) Default on Null SY_Log_Tables_ID.Nextval
,LG_Table_Name Varchar2(128 CHAR) Constraint SY_Log_Tables_TN_NN Not Null
,LG_PRIMARY_ID Integer Constraint SY_Log_Tables_PK_NN Not Null
,LG_CALL_STACK Varchar2(32767 CHAR)
,LG_CALL_PROGRAM Varchar2(200 CHAR)
,LG_CRUD_ACTION Varchar2(1 CHAR)
,LG_JSON_DIFFERENCE Varchar2(32767 CHAR)
,LG_EFFECTIVE DATE Default on Null Trunc(Current_Date) CONSTRAINT SY_Log_Tables_EE_NN NOT NULL
,LG_EXPIRES DATE Default on Null To_Date('12/31/2999','MM/DD/YYYY') CONSTRAINT SY_Log_Tables_EX_NN NOT NULL
-- Your tracking columns below, these are examples.
,SY_USER_ID_ADD NUMBER(20) CONSTRAINT SY_Log_Tables_UA_NN NOT NULL
,SY_DATE_ADD DATE CONSTRAINT SY_Log_Tables_DA_NN NOT NULL
,SY_USER_ID_MOD NUMBER(20)
,SY_DATE_MOD DATE
,Constraint SY_Log_Tables_LG_PK Primary Key (SY_Log_Tables_ID)
)
/
Create Index SY_Log_Tables_TN on SY_Log_Tables (LG_Table_Name);
Create Index SY_Log_Tables_TI on SY_Log_Tables (LG_Table_Name, LG_Primary_ID);
Create Index SY_Log_Tables_UA on SY_Log_Tables (SY_User_ID_Add);
Create Index SY_Log_Tables_UM on SY_Log_Tables (SY_User_ID_Mod);
Create Index SY_Log_Tables_EX on SY_Log_Tables (LG_Effective, LG_Expires);
Comment on Table SY_Log_Tables Is 'Tracks Table row value changes with JSON strings of columns that changed in value.';
Comment on Column SY_Log_Tables.SY_Log_Tables_ID Is 'Uniquely identifies this SY_Log_Tables. Use Current Date between effective/expires to get current entry.';
Comment on Column SY_Log_Tables.LG_Table_Name Is 'Name of Table with changes occurring.';
Comment on Column SY_Log_Tables.LG_PRIMARY_ID Is 'Primary ID of row in LG_Table_Name with values changing.';
Comment on Column SY_Log_Tables.LG_CALL_STACK Is 'Program call stack resulting in the change to the data.';
Comment on Column SY_Log_Tables.LG_CALL_PROGRAM Is 'Name of Last calling program from which the change came.';
Comment on Column SY_Log_Tables.LG_CRUD_ACTION Is '(C)reate, (U)pdate, (D)elete to indicate what action was performed. (R)ead not tracked. ';
Comment on Column SY_Log_Tables.LG_JSON_DIFFERENCE Is 'JSON String of columns and changed values.';
Comment on Column SY_Log_Tables.LG_Effective Is 'Date this entry became valid. ';
Comment on Column SY_Log_Tables.LG_Expires Is 'Date after which this entry is no longer valid. ';
Comment on Column SY_Log_Tables.SY_User_ID_Add Is 'User who originally added this row.';
Comment on Column SY_Log_Tables.SY_Date_Add Is 'Date row originally added.';
Comment on Column SY_Log_Tables.SY_User_ID_Mod Is 'User who last modified this row.';
Comment on Column SY_Log_Tables.SY_Date_Mod Is 'Date row last updated.';
/
CREATE OR REPLACE TRIGGER SY_Log_Tables_TRG BEFORE INSERT OR UPDATE OR Delete
ON SY_Log_Tables
FOR EACH ROW
BEGIN
If Inserting and Nvl(:New.SY_Log_Tables_ID,0) = 0 Then
:New.SY_Log_Tables_ID := SY_Log_Tables_ID.NextVal;
End If;
If Inserting Or Updating Then
:New.LG_Table_Name := Upper(Trim(:New.LG_Table_Name));
:New.LG_CRUD_Action := Upper(Trim(:New.LG_CRUD_Action));
:New.LG_CALL_STACK := Pkg_SY_Log_Tables.Format_Call_Stack;
:New.LG_CALL_PROGRAM := Upper(Pkg_Context.Get_Call_Program);
:New.LG_Effective := Nvl(:New.LG_Effective,Current_TimeStamp);
:New.LG_Expires := Nvl(:New.LG_Expires,Current_TimeStamp);
End If;
-- Your code for tracking dates and user update information
Exception
-- Your error and logging code here
End SY_Log_Tables_Trg;
/
Show Errors;
The Get_Call_Program function checks a current program context variable to see what the last program called was. Programs set the context variable value at the start of each function/procedure. You might handle this differently.
The Format_Call_Stack function utilizing util_call_stack is as follows:
-- Analyze the UTL_Call_Stack, format it, and return it in a varchar2()
--variable.
--
Function Format_Call_Stack
Return Varchar2
Is
v_Return Varchar2(32767) := Null;
Begin
v_Return := 'LexDepth Depth LineNo Name' || Chr(13) || Chr(10) ||
'-------- ----- ------ ----' ;
FOR the_depth IN REVERSE 1 .. utl_call_stack.dynamic_depth ()
LOOP
v_Return := v_Return || Chr(13) || Chr(10) ||
RPad (Utl_Call_Stack.Lexical_Depth (the_depth),9)
|| RPAD (the_depth, 5)
|| RPAD (
TO_CHAR (Utl_Call_Stack.Unit_Line (The_Depth),'9999'),8)
|| utl_call_stack.concatenate_subprogram (Utl_Call_Stack.SubProgram (
the_depth))
|| 'Backtrace Depth: ' || Utl_Call_Stack.Backtrace_Depth() ;
-- Do not exceed Varchar2 length limit of 32,767
Exit When Length(v_Return) > 32600;
END LOOP;
Return v_Return;
End Format_Call_Stack;
The Log Tables Control table relates the table (LT_Table_Name) to the Include Log Action code for the table via the Log Actions primary ID. (LT_INCL_LOG_ACTIONS_CID). Refer to the “Include Log Actions” in the “Data Change Log Control” tab that indicated what kind of row activity should be logged for any particular table. (Use your own methodology for tracking codes and/or refer to a prior blog post regarding tracking codes and their values using code type, code, and code value tables. Simplify managing application codes and their values )
Drop Table SY_LOG_TABLES_CONTROL Cascade Constraints
/
Drop Sequence SY_LOG_TABLES_CONTROL_ID;
/
Create Sequence SY_LOG_TABLES_CONTROL_ID MinValue 1 MaxValue 999999999999999999999999999
Start with 1
Increment by 1
NoCache
/
Create Table SY_LOG_TABLES_CONTROL
(
SY_LOG_TABLES_CONTROL_ID NUMBER(20) Default on Null SY_LOG_TABLES_CONTROL_ID.Nextval
,LT_TABLE_NAME Varchar2(128 CHAR ) Constraint SY_LOG_TABLES_CONTROL_TN_NN Not Null
,LT_INCL_LOG_ACTIONS_CID Number(20) Constraint SY_LOG_TABLES_CONTROL_LA_NN Not Null
,LT_EFFECTIVE DATE Default on Null Trunc(Current_Date) CONSTRAINT SY_LOG_TABLES_CONTROL_EE_NN NOT NULL
,LT_EXPIRES DATE Default on Null To_Date('12/31/2999','MM/DD/YYYY') CONSTRAINT SY_LOG_TABLES_CONTROL_EX_NN NOT NULL
,SY_USER_ID_ADD NUMBER(20) CONSTRAINT SY_LOG_TABLES_CONTROL_UA_NN NOT NULL
,SY_DATE_ADD DATE CONSTRAINT SY_LOG_TABLES_CONTROL_DA_NN NOT NULL
,SY_USER_ID_MOD NUMBER(20)
,SY_DATE_MOD DATE
,Constraint SY_LOG_TABLES_CONTROL_LA_FK Foreign Key ( LT_INCL_LOG_ACTIONS_CID ) References SY_Codes_Value(SY_Codes_Value_ID)
,Constraint SY_LOG_TABLES_CONTROL_LT_PK Primary Key (SY_LOG_TABLES_CONTROL_ID)
)
/
-- Create Index
Create Index SY_LOG_TABLES_CONTROL_TN on SY_LOG_TABLES_CONTROL (LT_TABLE_NAME);
Create Index SY_LOg_Tables_Control_LA on SY_Log_Tables_Control (LT_INCL_LOG_ACTIONS_CID);
Create Index SY_LOG_TABLES_CONTROL_UA on SY_LOG_TABLES_CONTROL (SY_User_ID_Add);
Create Index SY_LOG_TABLES_CONTROL_UM on SY_LOG_TABLES_CONTROL (SY_User_ID_Mod);
Create Index SY_LOG_TABLES_CONTROL_EX on SY_LOG_TABLES_CONTROL (LT_Effective, LT_Expires);
Comment on Table SY_LOG_TABLES_CONTROL Is 'All Application tables with an indicator on whether activity should be logged automatically in the triggers. ';
Comment on Column SY_LOG_TABLES_CONTROL.SY_LOG_TABLES_CONTROL_ID Is 'Uniquely identifies this SY_LOG_TABLES_CONTROL. Use Current Date between effective/expires to get current entry.';
Comment on Column SY_Log_Tables_Control.LT_INCL_LOG_ACTIONS_CID Is 'Indicates which actions (C)reate, (U)pdate, (D)elete, (A)ll, (N)one to log';
Comment on Column SY_LOG_TABLES_CONTROL.LT_Effective Is 'Date this entry became valid. ';
Comment on Column SY_LOG_TABLES_CONTROL.LT_Expires Is 'Date after which this entry is no longer valid. ';
Comment on Column SY_LOG_TABLES_CONTROL.SY_User_ID_Add Is 'User who originally added this row.';
Comment on Column SY_LOG_TABLES_CONTROL.SY_Date_Add Is 'Date row originally added.';
Comment on Column SY_LOG_TABLES_CONTROL.SY_User_ID_Mod Is 'User who last modified this row.';
Comment on Column SY_LOG_TABLES_CONTROL.SY_Date_Mod Is 'Date row last updated.';
/
CREATE OR REPLACE TRIGGER SY_LOG_TABLES_CONTROL_TRG BEFORE INSERT OR UPDATE OR Delete
ON SY_LOG_TABLES_CONTROL
FOR EACH ROW
BEGIN
If Inserting And Nvl(:New.SY_LOG_TABLES_CONTROL_ID,0) = 0 Then
:New.SY_LOG_TABLES_CONTROL_ID := SY_LOG_TABLES_CONTROL_ID.NextVal;
End If;
If Inserting Or Updating Then
-- Could use a virtual column for this as well.
:New.LT_TABLE_NAME := Upper(Trim(:New.LT_TABLE_NAME));
End If;
-- Your code here for default dates and tracking users.
Exception
-- Your error and logging code here
End SY_LOG_TABLES_CONTROL_Trg;
/
Show Errors;
Create a Log Tables view that incorporates aging data for the APEX user interface and to assist with archival and pruning.
Create Or Replace View VW_SY_Log_Tables
As
Select
SY_Log_Tables.SY_Log_Tables_ID
,SY_Log_Tables.LG_Table_Name
,SY_Log_Tables.LG_Table_Name || '_ID' LG_PRIMARY_ID_NAME
,SY_Log_Tables.LG_PRIMARY_ID
,SY_Log_Tables.LG_CALL_STACK
,SY_Log_Tables.LG_CALL_PROGRAM
,SY_Log_Tables.LG_CRUD_ACTION
,SY_Log_Tables.LG_JSON_DIFFERENCE
,(Trunc(Current_Date) - Trunc(SY_Log_Tables.LG_Effective)) Days_Old
,Case
When (Trunc(Current_Date) - Trunc(SY_Log_Tables.LG_Effective)) Between 0 and 30 Then
'00-30'
When (Trunc(Current_Date) - Trunc(SY_Log_Tables.LG_Effective)) Between 31 and 60 Then
'31-60'
When (Trunc(Current_Date) - Trunc(SY_Log_Tables.LG_Effective)) Between 61 and 90 Then
'61-90'
When (Trunc(Current_Date) - Trunc(SY_Log_Tables.LG_Effective)) > 90 Then
'91+'
End Days_Old_Freq
,SY_Log_Tables.LG_EFFECTIVE
,SY_Log_Tables.LG_EXPIRES
-- Your user tracking columns
From
SY_Log_Tables
;
Create a Log Tables Control View for the APEX tab, including the icons visually indicating whether the table’s log trigger and view exist.
Create Or Replace View VW_SY_Log_Tables_Control
As
Select
SY_Log_Tables_Control.SY_LOG_TABLES_CONTROL_ID
,SY_Log_Tables_Control.LT_TABLE_NAME
,SY_Log_Tables_Control.LT_INCL_LOG_ACTIONS_CID
,CD_Incl_Log_Actions.CV_VALUE_CHARACTER CD_INCL_LOG_ACTIONS
,CD_Incl_Log_Actions.CV_DESCRIPTION DS_INCL_LOG_ACTIONS
,CD_Incl_Log_Actions.CV_GR_ABBREV LOG_INDICATOR
,Case
When (Select Count(*) from User_Triggers Where Trigger_Name = Upper('LOG_' || LT_TABLE_NAME || '_TRG')) > 0 Then 'fa fa-check check-Y' -- check-Y'
Else 'fa fa-times check-N' -- check-N'
End TRIGGER_YN
,Case
When (Select Count(*) From User_Views Where View_Name = Upper('VW_LOG_' || LT_TABLE_NAME)) > 0 Then 'fa fa-check check-Y' -- check-Y'
Else 'fa fa-times check-N' -- check-N'
End VIEW_YN
,SY_Log_Tables_Control.LT_EFFECTIVE
,SY_Log_Tables_Control.LT_EXPIRES
-- Your user tracking columns here
From SY_Log_Tables_Control
Join SY_Codes_Value CD_INCL_LOG_ACTIONS
On SY_Log_Tables_Control.LT_INCL_LOG_ACTIONS_CID = CD_INCL_LOG_ACTIONS.SY_Codes_Value_ID
;
Create a slimmed down version of the Log Tables Control View for performance reasons.
Create Or Replace View VW_SY_Log_Tables_Control_Slim
As
Select
SY_Log_Tables_Control.SY_LOG_TABLES_CONTROL_ID
,SY_Log_Tables_Control.LT_TABLE_NAME
,SY_Log_Tables_Control.LT_INCL_LOG_ACTIONS_CID
,CD_Incl_Log_Actions.CV_VALUE_CHARACTER CD_INCL_LOG_ACTIONS
,CD_Incl_Log_Actions.CV_DESCRIPTION DS_INCL_LOG_ACTIONS
,CD_Incl_Log_Actions.CV_GR_ABBREV LOG_INDICATOR
,SY_Log_Tables_Control.LT_EFFECTIVE
,SY_Log_Tables_Control.LT_EXPIRES
From SY_Log_Tables_Control
Join SY_Codes_Value CD_INCL_LOG_ACTIONS
On SY_Log_Tables_Control.LT_INCL_LOG_ACTIONS_CID = CD_INCL_LOG_ACTIONS.SY_Codes_Value_ID
;
Create SY_Log_Tables supporting functionality.
Create a simple function to insert entries into SY_Log_Tables. This function should need minimal data and have low overhead to keep performance costs low when the log trigger for the calling table runs.
-- Insert Log entry with basic information. Remainder of columns will be
-- populated by SY_Log_Tables Trigger.
--
Procedure Ins_Log_Entry( p_LG_Table_Name IN SY_Log_Tables.LG_Table_Name%Type
,p_LG_Primary_ID IN SY_Log_Tables.LG_PRIMARY_ID%Type
,p_LG_CRUD_Action IN SY_Log_Tables.LG_CRUD_ACTION%Type
,p_LG_JSON_DIFFERENCE IN SY_Log_Tables.LG_JSON_DIFFERENCE%Type
)
Is
v_SY_Log_Tables SY_Log_Tables%RowType;
Begin
If p_LG_JSON_Difference <> '[]' And p_LG_JSON_Difference Is Not Null Then
-- Do not insert empty JSON values because nothing changed.
v_SY_Log_Tables.SY_Log_Tables_ID := Null; -- Populated by Trigger
v_SY_Log_Tables.LG_Table_Name := p_LG_Table_Name;
v_SY_Log_Tables.LG_PRIMARY_ID := p_LG_Primary_ID;
v_SY_Log_Tables.LG_CALL_STACK := Null; -- Populated by Trigger
v_SY_Log_Tables.LG_CALL_PROGRAM := Null; -- Populated by Trigger using a context variable.
v_SY_Log_Tables.LG_CRUD_ACTION := p_LG_CRUD_Action;
v_SY_Log_Tables.LG_JSON_DIFFERENCE := p_LG_JSON_Difference;
v_SY_Log_Tables.LG_EFFECTIVE := Current_TimeStamp;
v_SY_Log_Tables.LG_EXPIRES := To_Date('12/31/2999','MM/DD/YYYY');
v_SY_Log_Tables.SY_USER_ID_ADD := Null; -- Populated by Trigger
v_SY_Log_Tables.SY_DATE_ADD := Null; -- Populated by Trigger
v_SY_Log_Tables.SY_USER_ID_MOD := Null; -- Populated by Trigger
v_SY_Log_Tables.SY_DATE_MOD := Null; -- Populated by Trigger
-- Call a basic insert function that inserts the full Log entry.
Pkg_SY_Log_Tables.Ins( p_SY_Log_Tables => v_SY_Log_Tables);
End If;
Exception
-- Your error handling here
End Ins_Log_Entry;
Create SY_Log_Tables_Control supporting functionality
“Refresh Data Change Control Table” in the “Data Change Log Control” tab updates the table / actions rows from User_Objects so there is a one-to-one relationship for each table, default to log all activity for new entries.
The source code for accomplishing this is:
-- Refresh SY_Log_Tables_Control values from User_Tables.
--
Procedure Refresh_SY_Log_Tables_Control
Is
v_INCL_LOG_ACTIONS_CID SY_Codes_Value.SY_Codes_Value_ID%Type;
v_LT_Effective SY_Log_Tables_Control.LT_Effective%Type;
v_LT_Expires SY_Log_Tables_Control.LT_Expires%Type;
Begin
-- Get Primary Key value for code corresponding to Log all CRUD actions/table activity (Default)
-- Lookup code and value using your code management methodology
v_Incl_Log_Actions_CID := Pkg_SY_Codes_Value.Qry_Code_Value_To_ID( p_CO_NAME_ABREV => 'INCL_LOG_ACTIONS'
,p_CV_Value_Character => 'A'
,p_Error_on_No_Find => 'Y'
);
v_LT_Effective:= Trunc(Current_Date);
v_LT_Expires := To_Date('12/31/2999','MM/DD/YYYY');
Begin
-- MERGE to insert/update records for tables in USER_TABLES
-- Make sure there is a Log Control row for each table.
--
Merge Into SY_LOG_TABLES_CONTROL Target
Using (
Select Object_Name
From User_Objects
WHERE User_Objects.Temporary = 'N' -- Exclude temporary tables
And User_Objects.OBJECT_TYPE = 'TABLE'
And Object_Name Not IN (Select Object_Name
From User_Objects
Where Object_Type = 'MATERIALIZED VIEW'
) -- Materialized View have 2 entries, 1 as MV, 2nd as Table.
And Object_NAME NOT LIKE 'SYS%' -- Exclude system tables
And Substr(Object_Name,1,3) <> 'AQ$'
) Source
ON (Target.LT_TABLE_NAME = Source.Object_NAME)
WHEN Not Matched THEN
INSERT (
--SY_LOG_TABLES_CONTROL_ID, -- Trigger will assign
LT_TABLE_NAME
,LT_INCL_LOG_ACTIONS_CID
,LT_EFFECTIVE
,LT_EXPIRES
)
VALUES (
--SY_LOG_TABLES_CONTROL_ID.NEXTVAL -- Trigger will assign
Source.Object_Name
,v_Incl_Log_Actions_CID -- Default Log all CRUD Actions.
,v_LT_Effective
,v_LT_Expires
);
-- Delete records for tables no longer in USER_TABLES
Begin
Delete
From SY_Log_Tables_Control
WHERE SY_Log_Tables_Control.LT_Table_Name Not In
(Select Object_Name
From User_Objects
Where User_Objects.Temporary = 'N' -- Exclude temporary tables
And User_Objects.OBJECT_TYPE = 'TABLE'
And Object_Name Not IN (Select Object_Name
From User_Objects
Where Object_Type = 'MATERIALIZED VIEW'
) -- Materialized View have 2 entries, 1 as MV, 2nd as Table.
);
End;
End;
Exception
-- Your exception and logging code here.
End Refresh_SY_Log_Tables_Control;
Process each SY_Log_Tables_Control entry, creating/deleting log triggers and views according to parameters specified in control entry.
Source code is below with comments:
-- Cycle through SY_Log_Tables_Control and make sure each table
-- LOG_ table_name _TRG exists and is current unless the table is set to NO_LOG
-- p_Static_Dynamic:
-- Determine VW_SY_Log_Tables_Control Logging rules
-- (S)tatic: If Static, hard code which actions get logged in the trigger
-- (D)ynamic: Include Function to look up logging rules at run time in trigger
--
Procedure Refresh_Log_Triggers(p_Static_Dynamic IN Varchar2 := 'D')
Is
v_Trigger_Name Varchar2(200 CHAR);
v_View_Name Varchar2(200 CHAR);
v_Table_Name Varchar2(200 CHAR);
v_Static_Dynamic Pkg_SY_Codes_Value.s_Varchar2_1;
v_SQL Varchar2(1000 CHAR);
Begin
If Trim(p_Static_Dynamic) Is Null Then
-- Your error handling here
Else
v_Static_Dynamic := Upper(Trim(p_Static_Dynamic));
End If;
-- Process each Log Tables Control entry that is active / not expired.
For c_Tables In (Select
VW_SY_Log_Tables_Control.SY_LOG_TABLES_CONTROL_ID
,VW_SY_Log_Tables_Control.LT_TABLE_NAME
,VW_SY_Log_Tables_Control.LT_INCL_LOG_ACTIONS_CID
,VW_SY_Log_Tables_Control.CD_INCL_LOG_ACTIONS
,VW_SY_Log_Tables_Control.DS_INCL_LOG_ACTIONS
,VW_SY_Log_Tables_Control.LOG_INDICATOR
,VW_SY_Log_Tables_Control.LT_EFFECTIVE
,VW_SY_Log_Tables_Control.LT_EXPIRES
From VW_SY_Log_Tables_Control
Where Trunc(Current_Date) Between VW_SY_Log_Tables_Control.LT_EFFECTIVE and VW_SY_Log_Tables_Control.LT_EXPIRES
)
Loop
v_Table_Name := Upper(Trim(c_Tables.LT_TABLE_NAME));
If c_Tables.LOG_INDICATOR = 'NO_LOG' Then
-- Control table row for current table set to NO LOGging for this table.
-- Check to see if Log Trigger exists already, if so delete it since no Logging for the current table.
--
If Pkg_SY_Log_Tables_Control.Qry_Log_Trigger_Exists_YN( p_Table_Name => c_Tables.LT_TABLE_NAME) = 'Y' Then
-- Logging trigger for table does exist, so remove the Log trigger.
v_Trigger_Name := 'LOG_' || v_Table_Name || '_Trg';
v_SQL := 'DROP TRIGGER ' || v_Trigger_Name ;
EXECUTE IMMEDIATE v_SQL;
End IF;
-- Check to see if View of SY_Log_Tables entries JSON Difference data for the current table exists.
-- If the view exists, drop it since there should not be logging.
-- Consider not deleting the view automatically because logging could have been on for a period of time
-- resulting in log data that still might want to be viewed.
--
If Pkg_SY_Log_Tables_Control.Qry_Log_View_Exists_YN( p_Table_Name => c_Tables.LT_TABLE_NAME) = 'Y' Then
-- Remove Log view since Log Trigger removed or did not exist.
v_View_Name := 'VW_LOG_' || v_Table_Name;
v_SQL := 'DROP View ' || v_View_Name ;
Execute Immediate v_SQL;
End If;
Else
-- Log indicator is Logging is On for the current table.
-- Make sure trigger exists, and is up to date with the most current data structure.
Pkg_SY_Log_Tables_Control.SY_Create_JSON_Trigger( p_table_name => v_Table_Name
,p_Schema_Name => '[your schema name]'
,p_Static_Dynamic => v_Static_Dynamic
);
End If;
End Loop;
Exception
-- Your error handling and logging here.
End Refresh_Log_Triggers;
The Qry_Log_Trigger_Exists_YN code is:
-- Accept a table name and return (Y)es if Log Trigger exists, else (N)o
--
Function Qry_Log_Trigger_Exists_YN( p_Table_Name IN SY_Log_Tables_Control.LT_TABLE_NAME%Type)
Return Pkg_SY_Codes_Value.s_Varchar2_1
Is
v_Count Integer;
v_Result_YN Pkg_SY_Codes_Value.s_Varchar2_1;
v_Trigger_Name Varchar2(200 CHAR);
Begin
If Trim(p_Table_Name) Is Null Then
-- Your error handling here
Else
v_Trigger_Name := 'LOG_' || Upper(Trim(p_Table_Name)) || '_TRG';
End If;
Begin
Select Nvl(Count(*),0)
Into v_Count
From User_Objects
Where User_Objects.Object_Name = v_Trigger_Name
And User_Objects.Object_Type = 'TRIGGER';
End;
v_Result_YN := 'N';
If v_Count > 0 Then
v_Result_YN := 'Y';
End If;
Return v_Result_YN;
Exception
-- Your error handling and logging
End Qry_Log_Trigger_Exists_YN;
Similary, Qry_Log_View_Exists_YN is:
-- Accept a table name and return (Y)es if Log View exists, else (N)o
--
Function Qry_Log_View_Exists_YN( p_Table_Name IN SY_Log_Tables_Control.LT_TABLE_NAME%Type)
Return Pkg_SY_Codes_Value.s_Varchar2_1
Is
v_Count Integer;
v_Result_YN Pkg_SY_Codes_Value.s_Varchar2_1;
v_View_Name Varchar2(200 CHAR);
Begin
If Trim(p_Table_Name) Is Null Then
-- Your error handling here
Else
v_View_Name := 'VW_LOG_' || Upper(Trim(p_Table_Name)) ;
End If;
Begin
Select Nvl(Count(*),0)
Into v_Count
From User_Objects
Where User_Objects.Object_Name = v_View_Name
And User_Objects.Object_Type = 'VIEW';
End;
v_Result_YN := 'N';
If v_Count > 0 Then
v_Result_YN := 'Y';
End If;
Return v_Result_YN;
Exception
-- Your error handling here
End Qry_Log_View_Exists_YN;
Procedure SY_Create_JSON_Trigger creates the log triggers and views for each table in SY_Log_Tables_Control according to the limitations set by INCL_LOG_ACTIONS and the static/dynamic parameter. The procedure does so by creating the SQL needed to create the triggers and views, customizing it for the parameters mentioned and the specific column layout of the table selected.
-- Create an After Insert, Update, Delete Trigger for the provided Schema/Table combination
-- p_Static_Dynamic:
-- Determine VW_SY_Log_Tables_Control Logging rules
-- (S)tatic: If Static, hard code which actions get logged
-- (D)ynamic: Include Function to look up logging rules at run time.
--
PROCEDURE SY_Create_JSON_Trigger( p_table_name IN VARCHAR2
,p_Schema_Name IN Varchar2
,p_Static_Dynamic IN Varchar2 := 'D'
)
AS
v_Trigger_SQL CLOB ; -- SQL for entire Trigger
v_Table_Name Varchar2(128 CHAR); -- Table the Log trigger will be related to.
v_Primary_Key_Name Varchar2(128 CHAR); -- Primary Key Name for the Table to be logged.
v_New_JSON_SQL VARCHAR2(32767) ; -- JSON formatted :New column list.
v_Old_JSON_SQL VARCHAR2(32767) ; -- JSON formatted :Old column list.
v_First BOOLEAN := TRUE; -- First time through the loop?
v_Static_Dynamic Varchar2(1 CHAR); -- (S)tatic hard coded trigger, (D)ynamic checks control codes at trigger run time
v_VW_SY_Log_Tables_Control VW_SY_Log_Tables_Control_Slim%RowType;
v_VW_SQL CLOB; -- SQL for building View of Log Table JSON for this table.
v_VW_JSON_Select_SQL Varchar2(32767); -- SQL for Select portion of View definition
v_VW_JSON_Columns_SQL Varchar2(32767); -- SQL for JSON column/path portion of view definition
Begin
-- Validate table name
IF Trim(p_table_name) IS NULL THEN
-- Your error handling here
Else
v_Table_Name := Upper(Trim(p_Table_Name));
END IF;
If Trim(p_Static_Dynamic) Is Null Then
-- Your error handling here.
Else
v_Static_Dynamic := Upper(Trim(p_Static_Dynamic));
v_VW_SY_Log_Tables_Control:= Pkg_SY_Log_Tables_Control.Qry_Abbrev_VW( p_Abbrev => p_Table_Name
,p_Error_On_No_Find => 'N');
If v_VW_SY_Log_Tables_Control.SY_Log_Tables_Control_ID Is Null Then
v_Static_Dynamic := 'D'; -- Cannot be static because there are not any rules on file.
End If;
End If;
If v_VW_SY_Log_Tables_Control.LOG_INDICATOR = 'NO_LOG' Then
--Remove any trigger if exists.
-- Do not remove Log_View in case there was logging previously
-- and so should be able to see those rows.
Pkg_SY_Log_Tables_Control.Del_Log_Trigger( p_Table_Name => p_Table_Name);
Else
-- Confirm table to be logged has a Primary Key defined. Without a primary key the Log row
-- cannot be tied back to the table and row combination that changed.
-- Requires a custom Log trigger if the table should still be logged.
--
v_Primary_Key_Name := SY_Table_Primary_Key_Name( p_Table_Name => v_Table_Name);
If v_Primary_Key_Name Is Null then
-- Error: := 'Error: Table ' || v_Table_Name || ' lacks a Primary Key, not appropriate for logging because cannot tie log entry to a row.';
-- Your error handling here.
End If;
-- Create v_New_JSON_SQL and v_Old_JSON_SQL that can be used as hard coded JSON lines in Trigger
-- Fetch all columns for the table
FOR Col IN (Select column_name
,Data_Type
From user_tab_columns
Where Table_Name = v_Table_Name
Order By column_id
)
Loop
IF NOT v_First THEN
-- An additional column / value pair will be added, insert comma.
v_New_JSON_SQL := v_New_JSON_SQL || ' || '','' || ';
v_Old_JSON_SQL := v_Old_JSON_SQL || ' || '','' || ';
v_VW_JSON_Select_SQL := v_VW_JSON_Select_SQL || ' ,';
v_VW_JSON_Columns_SQL := v_VW_JSON_Columns_SQL || ' ,';
Else
v_VW_JSON_Select_SQL := ' ';
v_VW_JSON_Columns_SQL:= ' ';
END IF;
-- Build JSON key-value pairs for :NEW and :OLD and
-- column list to be selected for the VW_Log_ [table_name] view.
--
Case Col.Data_Type
When 'DATE' Then
v_New_JSON_SQL := v_New_JSON_SQL || '''"' || col.column_name || '":"'' || ' ||
'Nvl(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.' || col.column_name || ',''MM/DD/YYYY'')), ''null'') || ''"''' || Chr(13);
v_Old_JSON_SQL := v_Old_JSON_SQL || '''"' || col.column_name || '":"'' || ' ||
'Nvl(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.' || col.column_name || ',''MM/DD/YYYY'')), ''null'') || ''"''' || Chr(13);
v_VW_JSON_Select_SQL := v_VW_JSON_Select_SQL || 'To_Date(JT_' || col.column_name || ',''MM/DD/YYYY'') JT_' || col.column_name || Chr(13);
v_VW_JSON_Columns_SQL := v_VW_JSON_Columns_SQL || RPad(' ',15,' ') || 'JT_' || col.column_Name || ' Varchar2 Path ''$.' || col.column_name || '''' || Chr(13);
When 'NUMBER' Then
v_New_JSON_SQL := v_new_json_sql || '''"' || col.column_name || '":"'' || ' ||
'NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.' || col.column_name || ')), ''null'') || ''"''' || Chr(13);
v_Old_JSON_SQL := v_old_json_sql || '''"' || col.column_name || '":"'' || ' ||
'NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.' || col.column_name || ')), ''null'') || ''"''' || Chr(13);
v_VW_JSON_Select_SQL := v_VW_JSON_Select_SQL || 'JT_' || col.column_name || Chr(13);
v_VW_JSON_Columns_SQL := v_VW_JSON_Columns_SQL || RPad(' ',15,' ') || 'JT_' || col.column_Name || ' Number Path ''$.' || col.column_name || '''' || Chr(13);
Else
v_New_JSON_SQL := v_new_json_sql || '''"' || col.column_name || '":"'' || ' ||
'NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:NEW.' || col.column_name || ')), ''null'') || ''"''' || Chr(13);
v_Old_JSON_SQL := v_Old_JSON_SQL || '''"' || col.column_name || '":"'' || ' ||
'NVL(DBMS_XMLGEN.CONVERT(TO_CHAR(:OLD.' || col.column_name || ')), ''null'') || ''"''' || Chr(13);
v_VW_JSON_Select_SQL := v_VW_JSON_Select_SQL || 'JT_' || col.column_name || Chr(13);
v_VW_JSON_Columns_SQL := v_VW_JSON_Columns_SQL || RPad(' ',15,' ') || 'JT_' || col.column_Name || ' Varchar2 Path ''$.' || col.column_name || '''' || Chr(13);
End Case;
v_First := FALSE;
END LOOP;
-- And SY_Log_Tables columns to end of view after Table columns.
v_VW_JSON_Select_SQL := v_VW_JSON_Select_SQL
|| ' ,LT.SY_LOG_TABLES_ID ' || Chr(13)
|| ' ,LT.LG_TABLE_NAME ' || Chr(13)
|| ' ,LT.LG_PRIMARY_ID_NAME ' || Chr(13)
|| ' ,LT.LG_PRIMARY_ID ' || Chr(13)
|| ' ,LT.LG_CALL_STACK ' || Chr(13)
|| ' ,LT.LG_CALL_PROGRAM ' || Chr(13)
|| ' ,LT.LG_CRUD_ACTION ' || Chr(13)
|| ' ,LT.LG_EFFECTIVE ' || Chr(13)
|| ' ,LT.LG_EXPIRES ' || Chr(13)
-- Your user update tracking columns here, if applicable.
;
-- Code for Log View for current table.
v_VW_SQL := 'CREATE OR REPLACE VIEW VW_LOG_' || v_Table_Name || Chr(13)
|| ' As' || Chr(13)
|| ' Select' || Chr(13)
|| v_VW_JSON_Select_SQL || Chr(13)
|| ' From VW_SY_Log_Tables LT' || Chr(13)
|| ' ,JSON_Table( LT.LG_JSON_DIFFERENCE' || Chr(13)
|| ' ,''$[*]''' || Chr(13)
|| ' Columns(' || Chr(13)
|| v_VW_JSON_Columns_SQL || Chr(13)
|| ' )' || Chr(13)
|| ' ) LG_JSON_DIFFERENCE' || Chr(13)
|| ' Where LT.LG_Table_Name = ''' || v_Table_Name || ''''
;
-- Build Log trigger SQL
v_Trigger_SQL := 'CREATE OR REPLACE TRIGGER ' || p_Schema_Name || '.LOG_' || v_table_name || '_Trg' || Chr(13)
|| ' AFTER INSERT OR UPDATE OR DELETE ' || Chr(13)
|| ' ON ' || v_table_name || Chr(13)
|| ' FOR EACH ROW ' || Chr(13)
|| ' DECLARE ' || Chr(13)
|| ' v_new_json VARCHAR2(32767);' || Chr(13)
|| ' v_old_json VARCHAR2(32767);' || Chr(13)
|| ' v_CRUD_Actions Varchar2(1 CHAR);' || Chr(13)
|| ' v_LG_JSON_Difference Varchar2(32767);' || Chr(13)
|| ' BEGIN ' || Chr(13)
|| ' Case' || Chr(13)
|| ' When INSERTING THEN ' || Chr(13)
|| ' v_CRUD_Actions := ''C'';' || Chr(13)
|| ' When UPDATING THEN' || Chr(13)
|| ' v_CRUD_Actions := ''U'';' || Chr(13)
|| ' When DELETING THEN' || Chr(13)
|| ' v_CRUD_Actions := ''D'';' || Chr(13)
|| ' END Case;' || Chr(13)
;
Case
When v_Static_Dynamic = 'D' Then
-- Lookup Log Control settings for the table/action combination in VW_SY_Log_Tables_Control
-- at the time of the table change. Will reflect the most recent values in the Control table.
-- Include CHR(13) line feeds to improve formatting when viewing actual trigger.
--
v_Trigger_SQL := v_Trigger_SQL
|| ' If Pkg_SY_Log_Tables_Control.Qry_Log_YN( p_LT_Table_Name => ''' || v_Table_Name || '''' || Chr(13)
|| ' ,p_CRUD_Action => v_CRUD_Actions ' || Chr(13)
|| ' ) = ''Y'' Then ' || Chr(13)
|| ' IF INSERTING OR UPDATING THEN ' || Chr(13)
|| ' If v_New_JSON Is Null Then ' || Chr(13)
|| ' v_New_JSON := ''{''; ' || Chr(13)
|| ' End If;' || Chr(13)
|| ' v_new_json := v_new_json || ' || v_New_JSON_SQL || ' || ''}''; ' || Chr(13)
|| ' END IF; ' || Chr(13)
|| ' IF UPDATING OR DELETING THEN ' || Chr(13)
|| ' If v_Old_JSON Is Null Then' || Chr(13)
|| ' v_Old_JSON := ''{'';' || Chr(13)
|| ' End If;' || Chr(13)
|| ' v_old_json := v_old_json || ' || v_Old_JSON_SQL || ' || ''}''; ' || Chr(13)
|| ' END IF; ' || Chr(13)
|| ' v_LG_JSON_Difference := SY_JSON_String_Diff(p_JSON_1 => v_New_JSON' || Chr(13)
|| ' ,p_JSON_2 => v_Old_JSON' || Chr(13)
|| ' );' || Chr(13)
|| ' If v_LG_JSON_Difference <> ''[]'' And v_LG_JSON_Difference <> ''{}'' Then' || Chr(13)
|| ' Pkg_SY_Log_Tables.Ins_Log_Entry( p_LG_Table_Name => ''' || v_Table_Name || '''' || Chr(13)
|| ' ,p_LG_Primary_ID => Nvl(:New.' || v_Table_Name || '_ID,:Old.' || v_Table_Name || '_ID)' || Chr(13)
|| ' ,p_LG_CRUD_Action => v_CRUD_Actions' || CHR(13)
|| ' ,p_LG_JSON_DIFFERENCE => v_LG_JSON_Difference' || Chr(13)
|| ' );' || Chr(13)
|| ' End If;' || Chr(13)
|| ' End If;' || Chr(13)
|| ' EXCEPTION WHEN OTHERS' || Chr(13)
|| ' THEN RAISE;' || Chr(13)
|| ' END;' || Chr(13)
;
When v_Static_Dynamic = 'S' Then
-- Lookup Log Control settings for the table/action combination in the VW_SY_Log_Tables_Control
-- at the time the trigger is created. Will reflect the Control table settings at the time the trigger was created
-- and save a database lookup each time a change is made to the table.
--
If v_VW_SY_Log_Tables_Control.CD_Incl_log_Actions <> 'A' Then
-- Log only certain changes, not all of them. For example Insert Only, Delete Only, Create / Insert, etc.
v_Trigger_SQL := v_Trigger_SQL
|| ' If Instr(''' || v_VW_SY_Log_Tables_Control.CD_INCL_LOG_ACTIONS || ''',v_CRUD_Actions) > 0 Then ' || Chr(13)
|| ' IF INSERTING OR UPDATING THEN ' || Chr(13)
|| ' If v_New_JSON Is Null Then ' || Chr(13)
|| ' v_New_JSON := ''{''; ' || Chr(13)
|| ' End If;' || Chr(13)
|| ' v_new_json := v_new_json || ' || v_New_JSON_SQL || ' || ''}''; ' || Chr(13)
|| ' END IF; ' || Chr(13)
|| ' IF UPDATING OR DELETING THEN ' || Chr(13)
|| ' If v_Old_JSON Is Null Then ' || Chr(13)
|| ' v_Old_JSON := ''{''; ' || Chr(13)
|| ' End If;' || Chr(13)
|| ' v_old_json := v_old_json || ' || v_Old_JSON_SQL || ' || ''}''; ' || Chr(13)
|| ' END IF; ' || Chr(13)
|| ' v_LG_JSON_Difference := SY_JSON_String_Diff(p_JSON_1 => v_New_JSON' || Chr(13)
|| ' ,p_JSON_2 => v_Old_JSON' || Chr(13)
|| ' );' || Chr(13)
|| ' If v_LG_JSON_Difference <> ''[]'' And v_LG_JSON_Difference <> ''{}'' Then' || Chr(13)
|| ' Pkg_SY_Log_Tables.Ins_Log_Entry( p_LG_Table_Name => ''' || v_Table_Name || '''' || Chr(13)
|| ' ,p_LG_Primary_ID => Nvl(:New.' || v_Table_Name || '_ID,:Old.' || v_Table_Name || '_ID)' || Chr(13)
|| ' ,p_LG_CRUD_Action => v_CRUD_Actions' || CHR(13)
|| ' ,p_LG_JSON_DIFFERENCE => v_LG_JSON_Difference' || Chr(13)
|| ' );' || Chr(13)
|| ' End If;' || Chr(13)
|| ' End If;' || Chr(13)
|| ' EXCEPTION WHEN OTHERS' || Chr(13)
|| ' THEN RAISE;' || Chr(13)
|| ' END;' || Chr(13)
;
Else
-- Log (A)ll changes.
v_Trigger_SQL := v_Trigger_SQL
|| ' IF INSERTING OR UPDATING THEN ' || Chr(13)
|| ' v_new_json := v_new_json || ' || v_New_JSON_SQL || ' || ''}''; ' || Chr(13)
|| ' END IF; ' || Chr(13)
|| ' IF UPDATING OR DELETING THEN ' || Chr(13)
|| ' v_old_json := v_old_json || ' || v_Old_JSON_SQL || ' || ''}''; ' || Chr(13)
|| ' END IF; ' || Chr(13)
|| ' v_LG_JSON_Difference := SY_JSON_String_Diff(p_JSON_1 => v_New_JSON' || Chr(13)
|| ' ,p_JSON_2 => v_Old_JSON' || Chr(13)
|| ' );' || Chr(13)
|| ' If v_LG_JSON_Difference <> ''[]'' And v_LG_JSON_Difference <> ''{}'' Then' || Chr(13)
|| ' Pkg_SY_Log_Tables.Ins_Log_Entry( p_LG_Table_Name => ''' || v_Table_Name || '''' || Chr(13)
|| ' ,p_LG_Primary_ID => Nvl(:New.' || v_Table_Name || '_ID,:Old.' || v_Table_Name || '_ID)' || Chr(13)
|| ' ,p_LG_CRUD_Action => v_CRUD_Actions' || CHR(13)
|| ' ,p_LG_JSON_DIFFERENCE => v_LG_JSON_Difference' || Chr(13)
|| ' );' || Chr(13)
|| ' End If;' || Chr(13)
|| ' EXCEPTION WHEN OTHERS' || Chr(13)
|| ' THEN RAISE;' || Chr(13)
|| ' END;' || Chr(13)
;
End If;
Else
-- Error := 'Error: Static / Dynamic indicator of ' || v_Static_Dynamic || ' is not valid.';
-- Your error handling here
End Case;
-- Execute the trigger creation
EXECUTE IMMEDIATE v_Trigger_SQL;
-- Create View of logged changes associated with the current table.
Execute Immediate v_VW_SQL;
End If;
Exception
-- Your error handling and logging here.
END SY_Create_JSON_Trigger;
End Pkg_SY_Log_Tables_Control;
/
Show Errors;
/
The SY_JSON_String_Diff function compares two JSON formatted parameters returning only column / value combinations that are different between the two strings. The function is described in this blog post: Easily identify differences between JSON strings
The Qry_Log_YN Function queries the SY_Log_Tables_Control table to determine at run time whether logging is on or off for the particular table. If Logging is off that means it was turned off after the last refresh of the trigger was performed because if logging is off at the time the trigger is refreshed then the log trigger is not created and deleted if it exists on the table.
-- Accept an LT_TABLE_NAME and "CRUD" action
-- Return (Y)es if should be logged, (N)o if should not be logged.
--
Function Qry_Log_YN( p_LT_Table_Name IN SY_Log_Tables_Control.LT_TABLE_NAME%Type
,p_CRUD_Action IN Pkg_SY_Codes_Value.s_Varchar2_50
)
Return Pkg_SY_Codes_Value.s_Varchar2_1
Is
v_VW_SY_Log_Tables_Control VW_SY_Log_Tables_Control_Slim%RowType;
v_LT_Table_Name SY_Log_Tables_Control.LT_Table_Name%Type;
v_CRUD_Action Pkg_SY_Codes_Value.s_Varchar2_50;
v_Result_Log_YN Pkg_SY_Codes_Value.s_Varchar2_1;
Begin
If Trim(p_LT_Table_Name) Is Null Then
-- Your error handling
Else
v_LT_Table_Name := Upper(Trim(p_LT_Table_Name));
End If;
If Trim(p_CRUD_Action) Is Null Then
-- Your error handling
Else
v_CRUD_Action := Upper(Trim(p_CRUD_Action));
End If;
-- Get Log Control Entry
v_VW_SY_Log_Tables_Control:= Pkg_SY_Log_Tables_Control.Qry_Abbrev_VW( p_Abbrev => v_LT_Table_Name
,p_Error_On_No_Find => 'N'
);
v_Result_Log_YN := 'Y';
If v_VW_SY_Log_Tables_Control.SY_Log_Tables_Control_ID Is Null then
-- If table does not exist in SY Log Tables Control table, then log. (Default))
Else
-- Table exists in control table.
If v_VW_SY_Log_Tables_Control.Log_Indicator = 'NO_LOG' Then
v_Result_Log_YN := 'N';
Else
Case
When v_VW_SY_Log_Tables_Control.CD_INCL_LOG_ACTIONS = 'A' Then
v_Result_Log_YN := 'Y';
When v_VW_SY_Log_Tables_Control.CD_INCL_LOG_ACTIONS = 'N' Then
v_Result_Log_YN := 'N';
When Instr(v_VW_SY_Log_Tables_Control.CD_INCL_LOG_ACTIONS,v_CRUD_Action) > 0 Then
v_Result_Log_YN := 'Y';
Else
v_Result_Log_YN := 'N';
End Case;
End If;
End If;
Return v_Result_Log_YN;
Exception
-- Your error handling and logging here
End Qry_Log_YN;
Create APEX User Interface
The APEX user Interface is a basic tabbed Interactive Grid (IG) with “Data Change Logs” and “Data Change Logs Control” tabs. Each tab uses the VW_SY_Log_Tables and VW_SY_Log_Tables_Control views, respectively. The Row and Actions Menus are as shown in the Outcomes section above. My fan favorite for building those menu items is the United Codes free Menu Add button and Execute PL/SQL plugins found Plug-ins Pro - Log In (Free account required.) They were previously FOS plugins.
The crosswalk from the menus to the functions defined above is:
Actions:
Refresh Data Change Control Table => Refresh_SY_Log_Tables_Control;
Refresh Triggers (Static) => Refresh_Log_Triggers(p_Static_Dynamic => 'S');
Refresh Triggers (Dynamic) => Refresh_Log_Triggers(p_Static_Dynamic => 'D');
💡Depending on the number of tables, it may be best to push the processing to the background using the job scheduler. Example code follows.-- Execute in the background v_SQL := 'BEGIN Pkg_SY_Log_Tables_Control.Refresh_Log_Triggers(p_Static_Dynamic => ''S''); End;' ; v_Job_Name := dbms_scheduler.generate_job_name ( prefix => 'GEN_TRG_' ); BEGIN DBMS_SCHEDULER.create_job ( job_name => v_Job_Name, job_type => 'PLSQL_BLOCK', job_action => v_SQL, number_of_arguments => 0, start_date => SYSTIMESTAMP, repeat_interval => NULL, enabled => True ); END; -- Code to track the job, could be helpful with a separate modal dialog. select * from all_scheduler_jobs Where owner = '[your schema name here]' and State = 'RUNNING'; -- Code to look for job errors SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS
Row:
Refresh Trigger (Static)
Pkg_SY_Log_Tables_Control.SY_Create_JSON_Trigger( p_table_name => :P86_CONTROL_TABLE_CURR ,p_Schema_Name => '[your schema name]' ,p_Static_Dynamic => 'S' );Refresh Trigger (Dynamic)
Pkg_SY_Log_Tables_Control.SY_Create_JSON_Trigger( p_table_name => :P86_CONTROL_TABLE_CURR ,p_Schema_Name => '[your schema name]' ,p_Static_Dynamic => 'D' );View Table Log Trigger Body: Pass the table name to a modal dialog with table_name and trigger_body page items.
Create a Before Header Page Process that populates the trigger body page item.
Declare v_Table_Name Varchar2(128 CHAR); v_Trigger_Name Varchar2(256 CHAR); Begin If Trim(:P##_TABLE_NAME) Is Null Then -- Your error handling here Else v_Table_Name := Upper(trim(:P##_TABLE_NAME)); :P##_TRIGGER_NAME := 'LOG_' || v_Table_Name || '_TRG'; End If; Begin Select Trigger_Body into :P##_TRIGGER_BODY From User_Triggers Where User_Triggers.Trigger_Name = :P##_TRIGGER_NAME; Exception When No_Data_Found Then :P##_TRIGGER_BODY := '** NO LOG TRIGGER FOUND **'; End; Exception -- Your error handling and logging here. End;
Delete Log Trigger => Pkg_SY_Log_Tables_Control.Del_Log_Trigger( p_Table_Name => :P##_CONTROL_TABLE_CURR);
Delete Log View => Pkg_SY_Log_Tables_Control.Del_Log_View( p_Table_Name => :P##_CONTROL_TABLE_CURR);
An option to get values such as table name from the currently selected row(s) is Anton Nielsen’s Get IG Data Plugin ( ainielse/get_ig_data ). The Plugin first retrieves all or selected column values into a designated APEX Page Item as a JSON formatted string. Inspection of the query in the browser provides the SQL select statement that can be used to retrieve individual value(s) into Page_Item(s).
Here is an example of the first True Action in a Dynamic Action retrieving the values for SY_LOG_TABLES_CONTROL_ID and LT_TABLE_NAME, and placing them as a JSON string into Page Item P##_CONTROL_CURR
The second true action below places the ID and table name values into P##_CONTROL_ID_CURR and P##_CONTROL_TABLE_CURR, respectively. The Select from JSON Table is the SQL copied from the inspection of the browser when the Interactive Grid is displayed/active.
The check marks are conditionally set in the SY_Log_Tables_Control view with font awesome icons depending on whether log triggers or views exist for the table. The APEX columns are type HTML Expression with an expression of <i class=”&TRIGGER_YN.”></i>
Together, these items make up the main components of the Oracle APEX user interface mentioned at the start of the blog. They can be adjusted to fit your organization's specific methods and practices.
Conclusion
This logging management solution is highly automated and operates at the table level to ensure all activity is recorded. It uses existing Oracle features, tracks only changes, and employs metadata through a user interface to manage logging activity without incurring extra licensing fees. Additional items can be controlled and tracked, such as retention period, and review priority, among others. These items would support pruning, archival, alerts, prioritizing items to review, etc.
I hope you find this blog helpful. If you do, please click the like button.
Subscribe to my newsletter
Read articles from Joe Kerr directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
Joe Kerr
Joe Kerr
Specializing in innovative, visionary and strategic application of technology to create value and solve real world problems through a virtual CIO client-service model. Services include vision and strategic planning; creative problem solving and process optimization; application architecting, Oracle database & PL/SQL, Oracle APEX, Forms migration, and web design, build, and support. Experienced certified Oracle Database Administrator, Oracle Cloud Infrastructure, and Linux system administration team as well.