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. [sourcecode language="sql"] CREATE OR REPLACE TRIGGER TEST_TRIGGER after UPDATE of COLUMNName ON TABLENAME1 for each row WHEN ( NEW.COLUMNName= '0' and NEW.COLUMNName2='1' ) … Continue reading Trigger on Change of Column Value in Oracle

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. [sourcecode language="sql"] CREATE OR REPLACE … Continue reading Block access to database oracle

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