Oracle Logging Trigger Creator

August 25, 2010

In a multi-user database environment it seems that sometimes changes just magically appear in ERP (or other) data.  The questions swirl, “Who changed that record?”; “I know that employee ABC arrived on time yesterday, why does the computer show he arrived late?”; “That new record is missing vital information, who created it?”, etc.  There are auditing features built into Oracle Database, and it is possible to review the redo log contents with LogMiner, but what if you just need a simple way to quietly record changes made to a table’s data?  Sure, triggers could be manually crafted to log changes, but it is a time consuming task, and consistency from one logging table/trigger to the next is difficult to maintain.  Well, this seems to be enough justification to develop a solution in search of a suitable problem, for which the solution will solve (we always invent the solution first, and then search for suitable problems that can be fixed, right?).

Today’s article will offer a VBS script that connects to an Oracle Database, and displays table definitions for all tables in the database using Internet Explorer as the script’s user interface.  For example, the following screen capture shows the tables in the TESTUSER schema with the definition of the LOCATIONS table displayed.  As shown, this table contains two VARCHAR2(15) columns in the primary key, a typical VARCHAR2(80) column, and a CHAR(1) column with a NOT NULL constraint.

—-

Switching to the PARTS table, we see that there is much more to the VBS script that automatically creates logging triggers and the table to maintain the captured data.  For each column in the table we are able to specify whether or not a change in a column’s value will cause a row to be inserted into the logging table – the “Trigger On Change” column in the VBS script’s user interface allows the user to specify the columns that cause the trigger to fire.  The “Log Values” column determines what data will be captured when the trigger fires:

• Both New and Old (capture the prior values before the change and the values after the change)
• New Values (capture only the values after the change)
• Old Values (capture only the values before the change)
• New Values and Delete (capture only the values after the change, unless the row is being deleted – then capture the values from before the change)
• Old Values and Insert (capture only the values before the change, unless the row is being inserted – then capture the values from the insert statement)
• Do Not Log (no data is captured for this column)

Clicking on the Create Data Log Script button builds the script which may be used to create the logging table and the logging trigger.  By default the logging table will be owned by the DATALOG user, although that can be changed within the VBS script.  The generated logging script displayed on the screen should look like this when the above selections are made:

(Added January 6, 2013: Sample trigger code shown above in a text file, save as TRIGGER_DATALOG_or112p_HPM_LOG_PARTS.SQL – without the .DOC extension.)

Notice that the generated script provides details about the purpose of the script, how to execute the script, what passwords need to be specified, sample commands to create a DATALOG tablespace and DATALOG user, the SQL to create the logging table, and the SQL to create the logging trigger.  By design, the default behavior of the script is easy to modify.

You can download the VBS script here: LoggingTrigger.vbs (save as LoggingTrigger.vbs). 

The script logic starts in the StartUp sub:

Sub StartUp()
    Dim strSQL
    Dim intResult
    Dim intFlag

    'Fire up Internet Explorer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Left = 0
    objIE.Top = 0
    objIE.Width = 1100
    objIE.Height = 800
    objIE.StatusBar = True
    objIE.MenuBar = False
    objIE.Toolbar = False

    objIE.Navigate "about:blank"
    objIE.Document.Title = "Charles Hoopers Oracle Logging Trigger Creator"
    objIE.Visible = True

    Set dbDatabase = CreateObject("ADODB.Connection")
    Set snpData = CreateObject("ADODB.Recordset")
    Set comRetrieveTableNames = CreateObject("ADODB.Command")
    Set comRetrieveTableColumns = CreateObject("ADODB.Command")

    'Database configuration
    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"

    On Error Resume Next

    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbDatabase.Open

    'Should verify that the connection attempt was successful, but I will leave that for someone else to code

    strTableOwner = "TESTUSER"  'Set to the default owner name to be selected, possibly same as strUsername
    strDataLogSchema = "DATALOG"

As was the case for VBS scripts appearing in earlier articles that interact with the database, you must modify the database connection details, specifying username, password, and database.  Additionally, this script permits setting the default table schema (strTableOwner) and the default logging schema (strDataLogSchema) that display in the script’s user interface.  The script runs in a tight loop (sleeping for 0.5 seconds) in the StartUp sub until the script ends.  Within that loop the script reacts to changes in the Internet Explorer window, such as a selection of a different table name, or clicking the Create Data Log Script button.

When the user selects a different Table Owner the script executes the TableOwnerChange procedure which retrieves a list of the tables that reside in the selected user’s schema (note that this means the username used to run the script must have access to the various DBA_ views).  When a Table Name is selected, the TableNameChange procedure is executed which retrieves the definition for that table, and displays that definition in the Internet Explorer supplied user interface.  When the Create Data Log Script button is clicked the CreateLoggingScript procedure is executed, which writes the SQL*Plus compatible script to the root of the C:\ drive, and then displays the script on the screen.

If we run the script that was generated (after setting the correct passwords in the script), we should see something like this:

SQL> @C:\TRIGGER_DATALOG_or112p_HPM_LOG_PARTS.SQL
Connected.
DROP TABLE DATALOG.HPM_LOG_PARTS
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

Note that the script attempted to drop an old logging table named DATALOG.HPM_LOG_PARTS at the start of the script, and that is what triggered the ORA-00942 error – just ignore this error message.

The PARTS table in this database has a definition that looks like this (it was a table created in one of my scripts mentioned in this article Faulty Quotes 5 – Block Sizes):

CREATE TABLE PARTS (
  PART_ID VARCHAR2(30) NOT NULL ENABLE,
  DESCRIPTION VARCHAR2(40),
  STOCK_UM VARCHAR2(15) NOT NULL ENABLE,
  PLANNING_LEADTIME NUMBER DEFAULT 0 NOT NULL ENABLE,
  ORDER_POLICY CHAR(1) DEFAULT 'M' NOT NULL ENABLE,
  ORDER_POINT NUMBER(14,4),
  SAFETY_STOCK_QTY NUMBER(14,4),
  FIXED_ORDER_QTY NUMBER(14,4),
  DAYS_OF_SUPPLY NUMBER,
  MINIMUM_ORDER_QTY NUMBER(14,4),
  MAXIMUM_ORDER_QTY NUMBER(14,4),
  ENGINEERING_MSTR VARCHAR2(3),
  PRODUCT_CODE VARCHAR2(15),
  COMMODITY_CODE VARCHAR2(15),
  MFG_NAME VARCHAR2(30),
  MFG_PART_ID VARCHAR2(30),
  FABRICATED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  PURCHASED CHAR(1) DEFAULT 'Y' NOT NULL ENABLE,
  STOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  DETAIL_ONLY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  DEMAND_HISTORY CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  TOOL_OR_FIXTURE CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  INSPECTION_REQD CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  WEIGHT NUMBER(14,4),
  WEIGHT_UM VARCHAR2(15),
  DRAWING_ID VARCHAR2(15),
  DRAWING_REV_NO VARCHAR2(8),
  PREF_VENDOR_ID VARCHAR2(15),
  PRIMARY_WHS_ID VARCHAR2(15),
  PRIMARY_LOC_ID VARCHAR2(15),
  BACKFLUSH_WHS_ID VARCHAR2(15),
  BACKFLUSH_LOC_ID VARCHAR2(15),
  INSPECT_WHS_ID VARCHAR2(15),
  INSPECT_LOC_ID VARCHAR2(15),
  MRP_REQUIRED CHAR(1) DEFAULT 'N',
  MRP_EXCEPTIONS CHAR(1) DEFAULT 'N',
  PRIVATE_UM_CONV CHAR(1) DEFAULT 'N',
  AUTO_BACKFLUSH CHAR(1) DEFAULT 'Y',
  PLANNER_USER_ID VARCHAR2(20),
  BUYER_USER_ID VARCHAR2(20),
  ABC_CODE CHAR(1),
  ANNUAL_USAGE_QTY NUMBER(15,4),
  INVENTORY_LOCKED CHAR(1) DEFAULT 'N' NOT NULL ENABLE,
  UNIT_MATERIAL_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_LABOR_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_BURDEN_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  UNIT_SERVICE_COST NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
  BURDEN_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  PURC_BUR_PERCENT NUMBER(6,3) DEFAULT 0 NOT NULL ENABLE,
  PURC_BUR_PER_UNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  UNIT_PRICE NUMBER(20,6),
  NEW_MATERIAL_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_LABOR_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_SERVICE_COST NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_PERCENT NUMBER(5,2) DEFAULT 0 NOT NULL ENABLE,
  NEW_BURDEN_PERUNIT NUMBER(20,6) DEFAULT 0 NOT NULL ENABLE,
  NEW_FIXED_COST NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE,
  MAT_GL_ACCT_ID VARCHAR2(30),
  LAB_GL_ACCT_ID VARCHAR2(30),
  BUR_GL_ACCT_ID VARCHAR2(30),
  SER_GL_ACCT_ID VARCHAR2(30),
  QTY_ON_HAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_AVAILABLE_ISS NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_AVAILABLE_MRP NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_ON_ORDER NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  QTY_IN_DEMAND NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  USER_1 VARCHAR2(80),
  USER_2 VARCHAR2(80),
  USER_3 VARCHAR2(80),
  USER_4 VARCHAR2(80),
  USER_5 VARCHAR2(80),
  USER_6 VARCHAR2(80),
  USER_7 VARCHAR2(80),
  USER_8 VARCHAR2(80),
  USER_9 VARCHAR2(80),
  USER_10 VARCHAR2(80),
  LT_PLUS_DAYS NUMBER,
  LT_MINUS_DAYS NUMBER,
  STATUS CHAR(1),
  USE_SUPPLY_BEF_LT CHAR(1),
  QTY_COMMITTED NUMBER(14,4) DEFAULT 0 NOT NULL ENABLE,
  PRT_CREATE_USER_ID VARCHAR2(30) DEFAULT USER,
  PRT_CREATE_DATE DATE DEFAULT SYSDATE,
  CONSTRAINT "CHK_PART1" CHECK (
    (PLANNING_LEADTIME >= 0)
    AND (ORDER_POLICY = 'N'
      Or ORDER_POLICY = 'M'
      Or ORDER_POLICY = 'F'
      Or ORDER_POLICY = 'E'
      Or ORDER_POLICY = 'D'
      Or ORDER_POLICY = 'P')
    AND (ORDER_POINT >= 0)
    AND (SAFETY_STOCK_QTY >= 0)
    AND (FIXED_ORDER_QTY >= 0)
    AND (DAYS_OF_SUPPLY >= 0)
    AND (MINIMUM_ORDER_QTY >= 0)
    AND (MAXIMUM_ORDER_QTY >= 0)
    AND (FABRICATED = 'Y' Or FABRICATED = 'N')
    AND (PURCHASED = 'Y' Or PURCHASED = 'N')
    AND (STOCKED = 'Y' Or STOCKED = 'N')
    AND (DETAIL_ONLY = 'Y' Or DETAIL_ONLY = 'N')
    AND (DEMAND_HISTORY = 'Y' Or DEMAND_HISTORY = 'N')
    AND (TOOL_OR_FIXTURE = 'Y' Or TOOL_OR_FIXTURE = 'N')
    AND (MRP_REQUIRED = 'Y' Or MRP_REQUIRED = 'N')
    AND (MRP_EXCEPTIONS = 'Y' Or MRP_EXCEPTIONS = 'N')
    AND (PRIVATE_UM_CONV = 'Y' Or PRIVATE_UM_CONV = 'N')
    AND (INVENTORY_LOCKED = 'Y' Or INVENTORY_LOCKED = 'N')
    AND (INSPECTION_REQD = 'Y' Or INSPECTION_REQD = 'N')) ENABLE,
  PRIMARY KEY (PART_ID),
  CONSTRAINT "FKEY_INSP" FOREIGN KEY (INSPECT_WHS_ID, INSPECT_LOC_ID)
    REFERENCES LOCATIONS (WAREHOUSE_ID, LOCATION_ID) ENABLE,
  CONSTRAINT "FKEY_PREF_VENDOR" FOREIGN KEY (PREF_VENDOR_ID)
    REFERENCES VENDORS (VENDOR_ID) ENABLE,
  CONSTRAINT "FKEY_UM" FOREIGN KEY (WEIGHT_UM)
    REFERENCES UMS (UNIT_OF_MEASURE) ENABLE,
  CONSTRAINT "FKEY_STOCK_UM" FOREIGN KEY (STOCK_UM)
    REFERENCES UMS (UNIT_OF_MEASURE) ENABLE);

Yes, it is an ugly, long table definition that references a couple of additional tables.  Now, let’s make some changes to the above table:

INSERT INTO PARTS (
  PART_ID,
  DESCRIPTION,
  STOCK_UM,
  PLANNING_LEADTIME,
  ORDER_POLICY,
  FABRICATED,
  PURCHASED,
  STOCKED,
  DETAIL_ONLY,
  DEMAND_HISTORY,
  TOOL_OR_FIXTURE,
  INSPECTION_REQD,
  INVENTORY_LOCKED,
  UNIT_MATERIAL_COST,
  UNIT_LABOR_COST,
  UNIT_BURDEN_COST,
  UNIT_SERVICE_COST,
  BURDEN_PERCENT,
  BURDEN_PER_UNIT,
  PURC_BUR_PERCENT,
  PURC_BUR_PER_UNIT,
  FIXED_COST,
  UNIT_PRICE,
  NEW_MATERIAL_COST,
  NEW_LABOR_COST,
  NEW_BURDEN_COST,
  NEW_SERVICE_COST,
  NEW_BURDEN_PERCENT,
  NEW_BURDEN_PERUNIT,
  NEW_FIXED_COST,
  QTY_ON_HAND,
  QTY_AVAILABLE_ISS,
  QTY_AVAILABLE_MRP,
  QTY_ON_ORDER,
  QTY_IN_DEMAND,
  QTY_COMMITTED)
SELECT
  'AA'||ROWNUM,
  'WATCH ME',
  'EA',
  0,
  'N',
  'Y',
  'N',
  'N',
  'N',
  'N',
  'N',
  'N',
  'N',
  100,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  109,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  0
FROM
  DUAL
CONNECT BY
  LEVEL<=5;

5 rows created.

The above added 5 rows to the PARTS table.  Now let’s modify those rows:

UPDATE
  PARTS
SET
  DESCRIPTION='CHANGED'
WHERE
  PART_ID IN ('AA1','AA2');

2 rows updated.

UPDATE
  PARTS
SET
  SAFETY_STOCK_QTY=10,
  STOCK_UM='PC',
  FIXED_ORDER_QTY=10
WHERE
  PART_ID IN ('AA1','AA4','A5');

2 rows updated.

UPDATE
  PARTS
SET
  SAFETY_STOCK_QTY=10,
  STOCK_UM='PC',
  FIXED_ORDER_QTY=10
WHERE
  PART_ID IN ('AA1','AA4','AA5');

3 rows updated.

DELETE FROM
  PARTS
WHERE
  PART_ID IN ('AA2','AA3','AA4','AA5');

4 rows deleted.

COMMIT;

SELECT
  COUNT(*)
FROM
  DATALOG.HPM_LOG_PARTS;

  COUNT(*)
----------
        14

As indicated by the above output, our logging table now has 14 rows of data.  Let’s view that data using a spreadsheet program (in this case Excel 2010):

Obviously, it is important to be smart when deciding which columns to capture, whether the old and/or new values should be captured, and changes to which columns should cause the trigger to fire.  So, what was the inspiration for this article?  One of the features in my Toy Project for Performance Tuning:

The VBS script should work on client computers running Windows 2000 or above.  Have fun with the script, and let me know about any improvements that you make to the script (there were a couple of neat improvements to the Automated DBMS XPLAN Viewer script).