Oracle Logging Trigger Creator

25082010

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:

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.

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:

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).

The mixing of the various languages (VBScript, HTML, SQL, a little bit of PL/SQL, a little bit of Javascript, and the Scripting.FileSystemObject object syntax) probably makes some of my examples seem to be very complicated (or just plain confusing). I think that it would be worthwhile learning the VB language in Excel because it is simply easier than VBScript (syntax helping while typing is great). The user interface part is much more straight-forward in Excel where you have the option of using either a UserForm as the user interface, as well as using simple worksheets and charts. The code in this example will work as is in Excel also if the “StartUp” line near the start of the script is moved to a command button (or another macro).

The other option, of course, is to crack the whip on the programmer, and tell him/her to “build that for me!” – one approach might be easier than the other, but I am not sure which one. 🙂

I have an Oralce 11g application into which I must create a change-log trigger for a multi-columned table. I have downloaded your VBS script, but do not see the trigger shown there. When I select the notepad screen above showing the top end of the trigger, I am not able to scroll it down to see the trigger entirely. Having no knowledge of VBS, it is not feasible for me to try to run the script to discover the trigger it would build.

Would it be possible to show the trigger completely or email a copy? From what I see and read, your trigger will go a long way in supplying the basis from whioch I can create the trigger needed in the application with which I am now working.

I have just modified the article to include the generated trigger code in a plain text file. When you download the file, be sure to replace the .DOC file extension with either .SQL or .TXT (WordPress allows posting Microsoft Word documents, but not all other file types).

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: