Why you can’t commit inside a trigger ?

Well to put it across in a few words. A trigger is a part of a transaction and not a transaction in itself. So Oracle doesn't let you commit in a trigger. You can commit in a trigger by using PRAGMA AUTONOMOUS TRANSACTION. But that isn't advisable. Even using triggers is not advisable by some … Continue reading Why you can’t commit inside a trigger ?


Trigger on Change of Column Value in Oracle

Suppose you need to fire a trigger based on the change of value of a column and then perform some action based on it. You can do it easily like the below.

Block access to database oracle

This is a script to create a logon trigger for blocking third party tools like Toad, SQl Navigator etc from accesing a production database. The script is taken from: http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm and modified to include SQL Developer as well. Since these days it is the defacto tool to work on databases by developers.

Oracle Database Logon Trigger

The various security mandates these days required a lot of auditing on the database. Though there are a host of various third party tools and Oracle tools (Audit Vault) available for this. In this exercise we will design a simple login and logout capture mechanism for the database. 1. Create Auditing Table create table SYS.LOGIN_AUDIT_INFO_ALL … Continue reading Oracle Database Logon Trigger