Cuando trabajamos con trigger a nivel de fila, Oracle provee de dos tablas temporales a las cuales
se puede acceder, que contienen los antiguos y nuevos valores de los campos del registro afectado
por la sentencia que disparó el trigger. El nuevo valor es ":new" y el viejo valor es ":old". Para
referirnos a ellos debemos especificar su campo separado por un punto ":new.CAMPO" y
":old.CAMPO".
El acceso a estos campos depende del evento del disparador.
En un trigger disparado por un "insert", se puede acceder al campo ":new" unicamente, el campo
":old" contiene "null".
En una inserción se puede emplear ":new" para escribir nuevos valores en las columnas de la
tabla.
En un trigger que se dispara con "update", se puede acceder a ambos campos. En una
actualizacion, se pueden comparar los valores de ":new" y ":old".
En un trigger de borrado, unicamente se puede acceder al campo "old", ya que el campo ":new"
no existe luego que el registro es eliminado, el campo ":new" contiene "null" y no puede ser
modificado.
Los valores de "old" y "new" están disponibles en triggers after y before.
El valor de ":new" puede modificarse en un trigger before, es decir, se puede acceder a los nuevos
valores antes que se ingresen en la tabla y cambiar los valores asignando a ":new.CAMPO" otro
valor.
El valor de ":new" NO puede modificarse en un trigger after, esto es porque el trigger se activa
luego que los valores de "new" se almacenaron en la tabla.
El campo ":old" nunca se modifica, sólo puede leerse.
Pueden usarse en una clásula "when" (que veremos posteriormente).
En el cuerpo el trigger, los campos "old" y "new" deben estar precedidos por ":" (dos puntos), pero
si está en "when" no.
Veamos un ejemplo.
Creamos un trigger a nivel de fila que se dispara "antes" que se ejecute un "update" sobre el
campo "precio" de la tabla "libros". En el cuerpo del disparador se debe ingresar en la tabla
"control", el nombre del usuario que realizó la actualización, la fecha, el código del libro que ha
sido modificado, el precio anterior y el nuevo:
create or replace trigger tr_actualizar_precio_libros
before update of precio
on libros
for each row
begin
insert into control values(user,sysdate,:new.codigo,:old.precio,:new.precio);
end tr_actualizar_precio_libros;
Cuando el trigger se dispare, antes de ingresar los valores a la tabla, almacenará en "control",
además del nombre del usuario y la fecha, el precio anterior del libro y el nuevo valor.
El siguiente trigger debe controlar el precio que se está actualizando, si supera los 50 pesos, se
debe redondear tal valor a entero, hacia abajo (empleando "floor"), es decir, se modifica el valor
ingresado accediendo a ":new.precio" asignándole otro valor:
create or replace trigger tr_actualizar_precio_libros
before update of precio
on libros
for each row
begin
if (:new.precio>50) then
:new.precio:=floor(:new.precio);
end if;
insert into control values(user,sysdate,:new.codigo,:old.precio,:new.precio);
end tr_actualizar_precio_libros;
Si al actualizar el precio de un libro colocamos un valor superior a 50, con decimales, tal valor se
redondea al entero más cercano hacia abajo. Por ejemplo, si el nuevo valor es "54.99", se
almacenará "54".
Podemos crear un disparador para múltiples eventos, que se dispare al ejecutar "insert", "update"
y "delete" sobre "libros". En el cuerpo del trigger se realiza la siguiente acción: se almacena el
nombre del usuario, la fecha y los antiguos y viejos valores del campo "precio":
create or replace trigger tr_libros
before insert or update or delete
on libros
for each row
begin
insert into control values(user,sysdate,:old.codigo,:old.precio,:new.precio);
end tr_libros;
Si ingresamos un registro, el campo ":old.codigo" y el campo ":old.precio" contendrán "null". Si
realizamos una actualización del campo de un campo que no sea "precio", los campos
":old.precio" y ":new.precio" guardarán el mismo valor.
Si eliminamos un registro, el campo ":new.precio" contendrá "null".
Entonces, se puede acceder a los valores de ":new" y ":old" en disparadores a nivel de fila (no en
disparadores a nivel de sentencia). Están disponibles en triggers after y before. Los valores de
":old" solamente pueden leerse, nunca modificarse. Los valores de ":new" pueden modificarse
únicamente en triggers before (nunca en triggers after).
En los triggers a nivel de fila, se puede incluir una restricción adicional, agregando la clausula
"when" con una condición que se evalúa para cada fila que afecte el disparador; si resulta cierta,
se ejecutan las sentencias del trigger para ese registro; si resulta falsa, el trigger no se dispara para
ese registro.
Limitaciones de "when":
- no puede contener subconsultas, funciones agregadas ni funciones definidas por el usuario;
- sólo se puede hacer referencia a los parámetros del evento;
- no se puede especificar en los trigers "instead of" ni en trigger a nivel de sentencia.
Creamos el siguiente disparador:
create or replace trigger tr_precio_libros
before insert or update of precio
on libros
for each row when(new.precio>50)
begin
:new.precio := round(:new.precio);
end tr_precio_libros;
El disparador anterior se dispara ANTES (before) que se ejecute un "insert" sobre "libros" o un
"update" sobre "precio" de "libros". Se ejecuta una vez por cada fila afectada (for each row) y
solamente si cumple con la condición del "when", es decir, si el nuevo precio que se ingresa o
modifica es superior a 50. Si el precio es menor o igual a 50, el trigger no se dispara. Si el precio es
mayor a 50, se modifica el valor ingresado redondeándolo a entero.
Note que cuando hacemos referencia a "new" (igualmente con "old") en la condición "when", no
se colocan los dos puntos precediéndolo; pero en el cuerpo del trigger si.
Si ingresamos un registro con el valor 30.80 para "precio", el trigger no se dispara.
Si ingresamos un registro con el valor "55.6" para "precio", el trigger se dispara modificando tal
valor a "56".
Si actualizamos el precio de un libro a "40.30", el trigger no se activa.
Si actualizamos el precio de un libro a "50.30", el trigger se activa y modifica el valor a "50".
Si actualizamos el precio de 2 registros a valores que superen los "50", el trigger se activa 2 veces
redondeando los valores a entero.
Si actualizamos en una sola sentencia el precio de 2 registros y solamente uno de ellos supera los
"50", el trigger se activa 1 sola vez.
El trigger anterior podría haberse creado de la siguiente manera:
create or replace trigger tr_precio_libros
before insert or update of precio
on libros
for each row
begin
if :new.precio>50 then
:new.precio := round(:new.precio);
end if;
end tr_precio_libros;
En este caso, la condición se chequea en un "if" dentro del cuerpo del trigger. La diferencia con el
primer trigger que contiene "when" es que la condición establecida en el "when" se testea antes
que el trigger se dispare y si resulta verdadera, se dispara el trigger, sino no. En cambio, si la
condición está dentro del cuerpo del disparador, el trigger se dispara y luego se controla el precio,
si cumple la condición, se modifica el precio, sino no.
Por ejemplo, la siguiente sentencia:
update libros set precio=40 where...;
no dispara el primer trigger, ya que no cumple con la condición del "when"; pero si dispara el
segundo trigger, que no realiza ninguna acción ya que al evaluarse la condición del "if", resulta
falsa.
Primer problema:
Una librería almacena los datos de sus libros en una tabla denominada "libros" y en otra
denominada "ofertas", almacena los códigos y precios de los libros cuyo precio es inferior a $50.
1- Elimine las tablas:
drop table libros;
drop table ofertas;
2- Cree las tablas con las siguientes estructuras:
create table libros( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial
varchar(20), precio number(6,2) );
create table ofertas( codigo number(6), titulo varchar2(40) );
3- Ingrese algunos registros en "libros":
insert into libros values(100,'Uno','Richard Bach','Planeta',25);
insert into libros values(103,'El aleph','Borges','Emece',28);
insert into libros values(105,'Matematica estas ahi','Paenza','Nuevo siglo',12);
insert into libros values(120,'Aprenda PHP','Molina Mario','Nuevo siglo',55);
insert into libros values(145,'Alicia en el pais de las maravillas','Carroll','Planeta',35);
4- Cree un trigger a nivel de fila que se dispara "antes" que se ejecute un "insert" sobre "libros". Se
activa solamente si el precio que se ingresa es inferior a $30, en caso de serlo, se ingresa en
"ofertas" el código y precio del libro
5- Ingrese un libro en "libros" cuyo precio sea inferior a $30
6- Verifique que el trigger se disparó consultando "ofertas"
7- Ingrese un libro en "libros" cuyo precio supere los $30
8- Verifique que el trigger no se disparó consultando "ofertas"
9- Cree un trigger a nivel de fila que se dispare al borrar un libro de "libros", únicamente si el
precio del libro que se elimina es inferior a $30, es decir, si existe en "ofertas"
10- Elimine un registro de "libros" cuyo precio sea inferior a $30
11- Verifique que el trigger se disparó consultando "ofertas" y "libros"
12- Elimine un registro de "libros" cuyo precio supere los $30
13- Verifique que el trigger no se disparó consultando "ofertas" y que si se ha eliminado el registro
en "libros"
drop table libros;
drop table ofertas;
create table libros( codigo number(6), titulo varchar2(40), autor varchar2(30), editorial
varchar(20), precio number(6,2) );
create table ofertas( codigo number(6), titulo varchar2(40) );
insert into libros values(100,'Uno','Richard Bach','Planeta',25);
insert into libros values(103,'El aleph','Borges','Emece',28);
insert into libros values(105,'Matematica estas ahi','Paenza','Nuevo siglo',12);
insert into libros values(120,'Aprenda PHP','Molina Mario','Nuevo siglo',55);
insert into libros values(145,'Alicia en el pais de las maravillas','Carroll','Planeta',35);
create or replace trigger tr_ingresar_libros_ofertas
before insert
on libros
for each row when(new.precio<30)
begin
insert into ofertas values(:new.codigo,:new.titulo);
end tr_ingresar_libros_ofertas;
insert into libros values(150,'El experto en laberintos','Gaskin','Planeta',28);
select *from ofertas;
insert into libros values(155,'El gato con botas',null,'Planeta',38);
select *from ofertas;
create or replace trigger tr_borrar_libros_ofertas
before delete
on libros
for each row when(old.precio<30)
begin
delete from ofertas where codigo=:old.codigo;
end tr_borrar_libros_ofertas;
delete from libros where codigo=150;
select *from ofertas;
select *from libros;
delete from libros where codigo=155;
select *from ofertas;
select *from libros;
