Use oracle database trigger for audit

In a recent forum post at Oracle a user asked the following question: "I have audit trail for the table *****_INSTALL_BASE_ALL for columns attribute10, attribute21, attribute22 if someone changes these columns I want to get the alert for the same". I answerd this question that this could be done with a database trigger and that you can write code to send you a mail or any other way of sending an alert.

Giving this answer is the quick response however it triggered me as I have received a couple of questions that all involve logging of some sort when a value in a table changes. Due to some reason the usage of triggers in the database and what you can do with them is not widespread knowledge. Triggers can be used for all kind of reasons, one of the most important reasons I am personally in favor of them is that you can add logic to a insert or update without having to change anything to the code that is initiating this insert or update.

If you have, for example, a packaged application that is allowed to update some values in a table and you want to add a log to it however you do not want to change the application and add customizations in the code of the application itself you can easily achieve what you want by adding a trigger to the table.

In the below example we have a application which is used to create contracts for customers. Every contract type has a certain profit margin associated to it. Every newly created contract will look into this table and based upon the contract type it will select the right profit margin that needs to be applied. Changing this value can have a huge impact on the financial results of the company. Due to this reason you might want to have some extra logging and auditing in place to be able to track back who changed something at which time.

In this example we have a table named CONTRACT_PROFIT;

desc contract_profit
Name                 Null Type         
-------------------- ---- ------------ 
CONTRACT_TYPE             VARCHAR2(20) 
CONTRACT_PROFIT_RATE      VARCHAR2(20) 

For our audit process we have a table called CONTRACT_AUDIT where we will store the log information;

desc contract_audit
Name      Null     Type           
--------- -------- -------------- 
LOG_DATE  NOT NULL DATE           
LOG_ENTRY NOT NULL VARCHAR2(2000) 

What we want to achieve is that if someone changes a value in the table CONTRACT_PROFIT we will have a log entry in the table CONTRACT_AUDIT so we have a trail on what changed when. The layout of the table is very simple and you can make this as sophisticated as you like however for this example we have made this as easy as possible.

At the start of our example the table CONTRACT_PROFIT is filled with the following data;

CONTRACT_TYPE        CONTRACT_PROFIT_RATE
-------------------- --------------------
DIRECT_SALES         20                   
INDIRECT_SALES       15                   
DIRECT_LEASE         19                   
INDIRECT_LEASE       14        

We can now define a trigger on the table CONTRACT_PROFIT to write some logging to a table. In the below example you can see how we defined the trigger. This trigger will fire upon a update statement.

CREATE OR REPLACE TRIGGER contract_profit_after_update AFTER
  UPDATE ON contract_profit FOR EACH ROW DECLARE v_username VARCHAR2(10);
  BEGIN
    SELECT USER INTO v_username FROM dual;
    INSERT
    INTO contract_audit
      (
        log_date,
        log_entry
      )
      VALUES
      (
        sysdate,
        'Last change made on: '
        ||CURRENT_TIMESTAMP(1)
        ||' . Change made by user: '
        ||v_username
        ||' . New value for '
        ||:old.contract_type
        ||' is now '
        ||:new.contract_profit_rate
        ||' .'
      );
  END;

If we now update the table CONTRACT_PROFIT and change the value of CONTRACT_PROFIT_RATE for DIRECT_SALES from 20 to 21 the trigger will be fired and you will see a new line in the table CONTRACT_AUDIT. An example of this line is shown below;

Last change made on: 04-DEC-12 05.58.14.3 AM US/PACIFIC . Change made by user: LOUWERSJ .New value for DIRECT_SALES is now 21 .

You might want to add other information to a log file, you might even do not want to create a insert statement however rather have a mail action defined. This can all be done when developing your trigger.