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 :D. I read a funny comment on a forum once.

“Being lazy is the reason that pushes anyone to use triggers.
Being the most laziest person in the world pushes to desire a commit inside the trigger action.”

Well I am really lazy i guess then :)) I use a lot of triggers in my day to day daily tasks. And i cannot tell you how many times i have solved problems for business requirements; where complex reports need to be generated based on column values. I could certainly use pl/sql for those tasks but triggers make it so much easier and less complex. Plus i like to use triggers along with shell scripts and the fusion of both of them work like a charm.

I will try to dedicate my next few posts specific to triggers.

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.


CREATE OR REPLACE TRIGGER TEST_TRIGGER
after UPDATE of COLUMNName ON TABLENAME1
for each row
WHEN (
NEW.COLUMNName= '0' and NEW.COLUMNName2='1'
)
BEGIN
if :new.COLUMNName= '0'  and :new.COLUMNName2='1' then
update tablename2 set columname3= '2';
end if;
END;
/

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.

CREATE OR REPLACE TRIGGER SYS.block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Don't Check SYS Connections
AND ROWNUM = 1; -- Parallel processes will have the same AUDSID's

IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%SQL%DEVELOPER%' OR -- SQL Developer
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/

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
(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;

2. Create Logon Trigger

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into LOGIN_AUDIT_INFO_ALL values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate, 'hh24:mi:ss'),
null,
null,
null
);
END;
/

3. Create Logoff Trigger

create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_action = (select action from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_program = (select program from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
last_module = (select module from v$session where
sys_context('USERENV','SESSIONID') = audsid)
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff day
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the logoff time
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Compute the elapsed minutes
-- ***************************************************
update
SYS.LOGIN_AUDIT_INFO_ALL
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id;
END;
/
4. Check the audit table after opening another session

SELECT * FROM SYS.LOGIN_AUDIT_INFO_ALL