Oracle Trigger Follows Clause: Simultaneous Ordered Triggers

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So here is something you may not be aware and is really cool:

Follows Clause

Oracle allows more than one trigger to be created for the same timing point, but up to version 11g is not possible to establish the execution order of execution. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point.

The example below establish that second_trigger is executed after first_trigger before inserting each row in table_example.

CREATE OR REPLACE TRIGGER second_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
FOLLOWS first_trigger
BEGIN NULL; END;
/

Nice, han?
Cheers!

Oracle: Create Disabled Triggers

Hey,
I believe you may know about this already, but in case its something new to you: Yes, this is possible since 11g!

Prior to 11g, it was only possible to create triggers in the enabled state, and after creating to disable them. Since 11g they can be explicitly enabled or disabled at creation time using clause ENABLE or DISABLE. As per example:

CREATE OR REPLACE TRIGGER disabled_trigger
BEFORE INSERT ON table_example
FOR EACH ROW
DISABLE
BEGIN NULL; END;
/

If didn’t know it yet. Np! You know it now! 😀

Cheers!

Auditing Logons with Triggers

Hello all!
So, I was attending a client who wanted to audit logon on database without Advanced Security pack. Complicated? Not at all.

Performatic? eehh, not really the best option, but Oracle is not really good for opening sessions anyway (this is why we have connection pools), so I consider this not bad…

Ok, how to do it?

A sequence for ID control:

create sequence sys.logon_capture_seq;
A Table for logon logging:
create table sys.logon_capture (
 id                     number,
 capture_time           date,
 authenticated_identity varchar2(30),
 authentication_method  varchar2(30),
 identification_type    varchar2(30),
 network_protocol       varchar2(30),
 session_user           varchar2(30),
 os_user                varchar2(30),
 host                   varchar2(30),
 ip_address             varchar2(30),
 program                varchar2(30),
 module                 varchar2(30),
 action                 varchar2(30),
 service_name           varchar2(30))
tablespace logon_capture;

* Here is an important point: Be always sure to have enough space on this tablespace, otherwise all new connections can be frozen.

Create the logon trigger:

create or replace trigger SYS.trg_capture_logons
after logon on database
when (SYS_CONTEXT ('USERENV', 'SESSION_USER') not in ('SYS'))
begin
  insert into sys.logon_capture (id,capture_time,authenticated_identity,authentication_method,identification_type,network_protocol,session_user,os_user,host,ip_address,program,module,action,service_name)
  select
    sys.logon_capture_seq.nextval,
    sysdate,
    substr(sys_context('userenv','authenticated_identity'),1,30),
    substr(sys_context('userenv','authentication_method'),1,30),
    substr(sys_context('userenv','identification_type'),1,30),
    substr(sys_context('userenv','network_protocol'),1,30),
    substr(sys_context('userenv','session_user'),1,30),
    substr(sys_context('userenv','os_user'),1,30),
    substr(sys_context('userenv','host'),1,30),
    substr(sys_context('userenv','ip_address'),1,30),
    substr(program,1,30),
    substr(sys_context('userenv','module'),1,30),
    substr(sys_context('userenv','action'),1,30),
    substr(sys_context('userenv','service_name'),1,30)
  from v$session
  where sid = sys_context('userenv','sid');
  commit;
exception
  when others then null;
end;
/

Know what is a good idea? a cleanup job, keeping only the last 6 month of “audit” data:

begin
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'SYS.PURGE_LOGON_CAPTURE',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'begin delete from sys.logon_capture where capture_time < add_months(sysdate, -6); commit; end;',    number_of_arguments  => 0,
   start_date           => trunc(sysdate+1) + 23/24,
   repeat_interval      => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=40;BYSECOND=0',
   enabled              => false,
   auto_drop            => false,
   comments             => '');
end;
/
exec DBMS_SCHEDULER.ENABLE ( name => 'SYS.PURGE_LOGON_CAPTURE' );
select owner, enabled from dba_scheduler_jobs where job_name = 'PURGE_LOGON_CAPTURE'

Hope it helps you!

ORA-04091: Table is Mutating, Trigger/Function may not see it

No!
This is not a super-table nor a x-table (X-Men joke, this was awfull, I know… I’m sorry).

ORA-04091: Table "TABLE NAME" is Mutating, Trigger/Function may not see it
ORA-06512: em "TRC_INSERT_TABLE", line 14
ORA-04088: error during execution of trigger 'TRC_INSERT_TABLE'

Very interesting. But not hard to understand. The cause is that the trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

In other words, your trying to read a data the you are modifying. The obviously cause an inconsistency, the reason to this error. The data is “mutant”. But the error could be less annoying, right? Oracle and his jokes…

The solution is to rewrite the trigger to not use the table, or, in some situation, you can use an autonomous transaction, to turn it independent. It can be done using the clause PRAGMA AUTONOMOUS_TRANSACTION.

This FAQ can be useful to you: http://www.orafaq.com/wiki/Autonomous_transaction

Matheus.