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.