Nuevas Caracteristicas de los triggers en 11g

Hoy voy a comentarles respecto a los nuevos features de 11g relacionados con los triggers. Las 3 extensiones al funcionamiento de los triggers son:

1. Se pueden crear triggers compuestos, que son como varios triggers definidos en uno.
2. Se pueden crear los triggers inicialmente deshabilitados.
3. Se puede establecer un orden de ejecución de triggers sobre tablas con mas de un trigger definido.

En esta nota voy a mostrarles como crear y usar los triggers compuestos y además haré una comparativa de tiempos con respecto a los triggers tradicionales.

Para comenzar me gustaría mostrarles mediante un ejemplo usando dos tablas inventadas, de forma de simular una base de libros de una biblioteca. Los libros pueden estar en 3 estados: D:"Disponible", P:"Prestado" y R:"En restauracion". Se mantiene un registro de cada cambio de estado en la tabla libros_log que se llena mediante un trigger cada vez que se realiza un update del campo estado en la tabla libros.

rop@ROP111> create table libros (id_libro int primary key,
                                 autor varchar2(20),
                                 titulo varchar2(30),
                                 editorial varchar2(10),
                                 estado char(1) check (estado in ('P','D','R')))
/

Tabla creada.
Transcurrido: 00:00:00.15
rop@ROP111>
rop@ROP111> create table libros_log (id_operacion int,
                                     id_libro int references libros (id_libro),
                                     estado char(1) check (estado in ('P','D','R')),
                                     fecha_operacion date)
/

Tabla creada.

Transcurrido: 00:00:00.01

rop@ROP111> create sequence libros_log_seq;

Secuencia creada.

Una vez creadas las dos tablas y la secuencia voy a proceder a cargar la tabla de libros con 1M de registros generados aleatoriamente, de forma tal de acercarme a la realidad, aunque sean datos irreales.

rop@ROP111> insert into libros
            select rownum,
            dbms_random.string('a',trunc(dbms_random.value(1,20))),
            dbms_random.string('l',trunc(dbms_random.value(1,30))),
            'EDITORIAL'trunc(dbms_random.value(1,10)),
            'D'
            from dual
            connect by rownum <= 1000000  
/  
1000000 filas creadas.  
Transcurrido: 00:02:02.97   

rop@ROP111> commit;
Confirmación terminada.

Ahora voy a cambiar el estado a los libros registrados. La idea es cambiar el estado tratando de simular una distribución real. Se definirá un 40% del total de los libros como prestados (P) y el 2% en estado de restauración (R):

rop@ROP111> update libros
            set estado = 'P'
            where id_libro in (select trunc(dbms_random.value(1,200000))
                               from libros
                               where rownum <= 200000*0.4)  
/  
66163 filas actualizadas.   
Transcurrido: 00:00:03.71   
rop@ROP111>
rop@ROP111> update libros
            set estado = 'R'
            where id_libro in (select trunc(dbms_random.value(1,200000))
            from libros
            where rownum <= 200000*0.02)  
/  
3966 filas actualizadas.  
Transcurrido: 00:00:02.45  

Voy a crear un trigger simple, tal como lo creariamos en versiones 10g o inferiores:

rop@ROP111> create or replace trigger libros_trg_simple
2 after update of estado on libros
3 for each row
4 begin
5 insert into libros_log values (libros_log_seq.nextval,
6 :old.id_libro,
7 :new.estado,
8 sysdate);
9 end libros_trg_simple;
10 /
Disparador creado.

Ahora voy a crear el trigger compuesto introducido en 11g:

rop@ROP111> create or replace trigger libros_trg_compound
2  for update of estado
3  on libros
4  compound trigger
5    type libros_log_type is table of libros_log%rowtype index by pls_integer;
6    l_libros_log_array  libros_log_type;
7    l_pos pls_integer := 0;
8  before statement is
9  begin
10      null;
11  end before statement;
12  before each row is
13  begin
14      null;
15  end before each row;
16  after each row is
17  begin
18      l_pos := l_pos+1;
19      l_libros_log_array(l_pos).id_operacion := libros_log_seq.nextval;
20      l_libros_log_array(l_pos).id_libro := :old.id_libro;
21      l_libros_log_array(l_pos).estado := :new.estado;
22      l_libros_log_array(l_pos).fecha_operacion := sysdate;
23  end after each row;
24  after statement is
25  begin
26      forall i in 1..l_libros_log_array.count()
27            insert into libros_log
28            values l_libros_log_array(i);
29  end after statement;
30  end libros_trg_compound;
31  /

Disparador creado.

Transcurrido: 00:00:00.06
rop@ROP111>

Notar que se realiza un bulk insert lo cual deberia ser mas rapido que insertar por cada update. Tambien se puede ver que se obtiene el próximo valor de secuencia (linea 19) en forma directa desde PL/SQL sin necesidad de usar un select into (nuevo en 11g) .

Los triggers fueron creados habilitados (comportamiento default), pero podrian haberse creado inicialmente deshabilitados aprovechando que en 11g se puede.
Para realizar la primera prueba deshabilitamos el trigger compuesto y dejamos solo habilitado el trigger simple

rop@ROP111> alter trigger libros_trg_compound disable;
Disparador modificado.

Vamos a simular que se recibieron todos los libros que estaban para restaurar (estado=R) y entonces hay que cambiarles el estado en la base para que queden nuevamente disponibles (estado=D).

rop@ROP111> update libros
            set estado = 'D'
            where estado = 'R'
/
3960 filas actualizadas.

Transcurrido: 00:00:01.90

rop@ROP111> rollback;
Rollback terminado.

Una vez tomado el tiempo, deshacemos el update con rollback y habilitamos el trigger
compuesto y deshabilitamos el simple

rop@ROP111> alter trigger libros_trg_compound enable;
Disparador modificado.

Transcurrido: 00:00:00.01
rop@ROP111> alter trigger libros_trg_simple disable;
Disparador modificado.

rop@ROP111> update libros
            set estado = 'D'
            where estado = 'R';
3966 filas actualizadas.

Transcurrido: 00:00:01.22

Como observamos de la comparación, se nota una mejora en los tiempos al usar el trigger compuesto en relación con el trigger simple.

Para notar mas la diferencia voy a modificar todos los registros de forma tal de
setear todos los estados en disponible (estado=D). Primero pruebo con el trigger simple activo:

rop@ROP111> alter trigger libros_trg_simple enable;
Disparador modificado.

rop@ROP111> alter trigger libros_trg_compound disable;
Disparador modificado.

rop@ROP111> update libros
            set estado = 'D';
1000000 filas actualizadas.

Transcurrido: 00:02:57.83
rop@ROP111> rollback;

Rollback terminado.
Transcurrido: 00:01:20.89

Por ultimo comparamos con los tiempos usando el trigger compuesto:

rop@ROP111> alter trigger libros_trg_simple disable;
Disparador modificado.

rop@ROP111> alter trigger libros_trg_compound enable;
Disparador modificado.

rop@ROP111> update libros
            set estado = 'D';

1000000 filas actualizadas.
Transcurrido: 00:02:45.03

Como vemos en la ultima comparación, al modificar mayor cantidad de registros se ve mas claramente la diferencia entre procesar todo al final de la sentencia en forma bulk (trigger compuesto) que con el procesamiento tradicional donde el trigger se dispara por cada registro.