5.4.8.

Gestión de excepciones

Se llama excepción a todo hecho que le sucede a un programa que causa que la
ejecución del mismo finalice. Lógicamente eso causa que el programa termine de forma
anormal. Las excepciones se deben a:

Que ocurra un error detectado por Oracle (por ejemplo si un SELECT no devuelve datos
ocurre el error ORA-01403 llamado NO_DATA_FOUND). Que el propio programador las
lance (comando RAISE). Las excepciones se pueden capturar a fin de que el programa
controle mejor la existencia de las mismas.

5.4.8.1. Captura de excepciones

La captura se realiza utilizando el bloque EXCEPTION que es el bloque que está justo
antes del END del bloque. Cuando una excepción ocurre, se comprueba el bloque
EXCEPTION para ver si ha sido capturada, si no se captura, el error se propaga a Oracle
que se encargará de indicar el error existente.

Las excepciones pueden ser de estos tipos:

 Excepciones predefinidas de Oracle. Que tienen ya asignado un nombre de

excepción.
 Excepciones de Oracle sin definir. No tienen nombre asignado pero se les
puede asignar.
 Definidas por el usuario. Las lanza el programador.

La captura de excepciones se realiza con esta sintaxis:

DECLARE

sección de declaraciones

BEGIN

instrucciones

EXCEPTION

WHEN excepción1 [OR excepción2 ...] THEN

instrucciones que se ejcutan si suceden esas excepciones

[WHEN excepción3 [OR...] THEN

instrucciones que se ejcutan si suceden esas excepciones]

[WHEN OTHERS THEN

instrucciones que se ejecutan si suceden otras excepciones]

END;
Cuando ocurre una determinada excepción, se comprueba el primer WHEN para
comprobar si el nombre de la excepción ocurrida coincide con el que dicho WHEN
captura; si es así se ejecutan las instrucciones, si no es así se comprueba el siguiente
WHEN y así sucesivamente.

Si existen cláusula WHEN OTHERS, entonces las excepciones que no estaban

reflejadas en los demás apartados WHEN ejecutan las instrucciones del WHEN
OTHERS. Ésta cláusula debe ser la última.

5.4.8.2. Excepciones predefinidas

Oracle tiene muchas excepciones predefinidas. Son errores a los que Oracle asigna un
nombre de excepción. Algunas de las que aparecen con mayor frecuencia son:

Nombre de excepción Número Ocurre cuando…

CASE_NOT_FOUND ORA- Ninguna opción WHEN dentro de la
06592 instrucción CASE captura el valor, y no hay
instrucción ELSE
DUP_VAL_ON_INDEX ORA- Se intentó añadir una fila que provoca que un
00001 índice único repita valores
INVALID_NUMBER ORA- Falla la conversión de carácter a número
01722
NO_DATA_FOUND ORA- El SELECT de fila única no devolvió valores
01403
TOO_MANY_ROWS ORA- El SELECT de fila única devuelve más de una
01422 fila
VALUE_ERROR ORA- Hay un error aritmético, de conversión, de
06502 redondeo o de tamaño en una operación
ZERO_DIVIDE ORA- Se intenta dividir entre el número cero.
01476
Ejemplos:

En el siguiente ejemplo se producirá una excepción ZERO_DIVIDE puesto que el divisor

x es igual a 0.

DECLARE

x NUMBER := 0;

y NUMBER := 3;

res NUMBER;

BEGIN

res:=y/x;

DBMS_OUTPUT.PUT_LINE(res);

EXCEPTION

WHEN ZERO_DIVIDE THEN

DBMS_OUTPUT.PUT_LINE('No se puede dividir por cero') ;

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error inesperado') ;

END;

En el siguiente ejemplo el cursor implícito Hotel99 sólo puede recibir una única fila o
registro como resultado de una consulta. En este caso podrían producirse 2
excepciones: NO_DATA_FOUND (la consulta select no devuelve ningún registro) o
TO_MANY_ROWS (la consulta select devuelve más de un registro). En el primer caso
insertamos un nuevo registro. En el segundo caso borramos el registro duplicado.
DECLARE

Hotel99 Hotel%ROWTYPE;

BEGIN

SELECT * INTO Hotel99 WHERE Nombre='Costanera';

-- IF SQL%NOTFOUND THEN ... // Esto no tiene sentido aquí

-- IF SQL%ROWCOUNT > 1 THEN ... // Tampoco tiene sentido aquí

EXCEPTION

WHEN NO_DATA_FOUND THEN -- Cuando no se recupera ninguna fila

INSERT INTO Hotel VALUES (99, 'Costanera', 110, 60, 'S', 3 );

WHEN TOO_MANY_ROWS THEN -- Cuando se recuperan varias filas

DELETE Hotel WHERE Nombre='Costanera' AND HotelID<>99;

END;

Si una instrucción SELECT INTO no devuelve una fila, PL/SQL lanza la excepción
predefinida NO_DATA_FOUND tanto si se comprueba SQL%NOTFOUND en la línea
siguiente como si no. Si una instrucción SELECT INTO devuelve más de una fila,
PL/SQL lanza la excepción predefinida TOO_MANY_ROWS tanto si se comprueba
SQL%ROWCOUNT en la línea siguiente como si no.
5.4.8.3. Funciones de uso con excepciones

Se suelen usar dos funciones cuando se trabaja con excepciones:

 SQLCODE. Retorna el código de error del error ocurrido

 SQLERRM. Devuelve el mensaje de error de Oracle asociado a ese número de
error.

Ejemplo:

EXCEPTION

...

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE

('Ocurrió el error ' || SQLCODE ||'mensaje: ' || SQLERRM);

END;

5.4.8.4. Excepciones de usuario

El programador puede lanzar sus propias excepciones simulando errores del programa.
Para ello hay que:

1. Declarar un nombre para la excepción en el apartado DECLARE, al igual que

para las excepciones sin definir:

miExcepcion EXCEPTION;

2. En la sección ejecutable (BEGIN … END) utilizar la instrucción RAISE para

lanzar la excepción:

RAISE miExcepcion;

3. En el apartado de excepciones capturar el nombre de excepción declarado:

EXCEPTION

...

WHEN miExcepcion THEN

...

Ejemplo:
DECLARE

error_al_eliminar EXCEPTION;

BEGIN

DELETE piezas WHERE tipo='ZU' AND modelo=26;

IF SQL%NOTFOUND THEN

RAISE error_al_eliminar;

END IF;

EXCEPTION

WHEN error_al_eliminar THEN

DBMS_OUTPUT.PUT_LINE ('Error -20001: No existe esa pieza');

END;

Otra forma es utilizar la función RAISE_APPLICATION_ERROR que simplifica los tres

pasos anteriores. Sintaxis:

RAISE_APPLICATION_ERROR (noDeError, mensaje, [,{TRUE|FALSE}]);

Esta instrucción se coloca en la sección ejecutable o en la de excepciones y sustituye a

los tres pasos anteriores. Lo que hace es lanzar un error cuyo número debe de estar
entre el -20000 y el -20999 y hace que Oracle muestre el mensaje indicado. El tercer
parámetro opciones puede ser TRUE o FALSE (por defecto TRUE) e indica si el error
se añade a la pila de errores existentes.

Ejemplo con RAISE_APPLICATION_ERROR:

DECLARE

BEGIN

DELETE piezas WHERE tipo='ZU' AND modelo=26;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20001,'No existe esa pieza');

END IF;

END;

En el ejemplo, si la pieza no existe, entonces SQL%NOTFOUND devuelve verdadero

ya que el DELETE no elimina ninguna pieza. Se lanza la excepción de usuario -20001
haciendo que Oracle utilice el mensaje indicado. Oracle lanzará el mensaje: ORA-
20001: No existe esa pieza.
5.4.9. Procedimientos

Un procedimiento es un bloque que puede recibir parámetros, lo cual permite trabajar

con unos datos de entrada, realizar las operaciones deseadas con dichos datos y, en
algunos casos guardar ciertos resultados como parámetros de salida. Se usa la palabra
reservada PROCEDURE. Su estructura simplificada es:

PROCEDURE nombre IS

bloque sin palabra DECLARE

Su estructura en detalle es:

PROCEDURE nombre

[(parámetro1 [modo] tipoDatos[,parámetro2 [modo] tipoDatos [,...])]

{IS|AS}

bloque sin palabra DECLARE

Los procedimientos permiten utilizar parámetros para realizar su tarea. El modo, que es
opcional, puede ser de 3 tipos: IN, OUT o IN OUT. Si no se indica nada, por defecto es
IN.

 Parámetros IN. Son los parámetros que en otros lenguajes se denominan como
parámetros por valor. El procedimiento recibe una copia del valor o variable
que se utiliza como parámetro al llamar al procedimiento. Estos parámetros
pueden ser: valores literales (18 por ejemplo), variables (v_num por ejemplo) o
expresiones (como v_num+18). A estos parámetros se les puede asignar un
valor por defecto.
 Parámetros OUT. Relacionados con el paso por variable de otros lenguajes.
Sólo pueden ser variables y no pueden tener un valor por defecto. Se utilizan
para que el procedimiento almacene en ellas algún valor. Es decir, los
parámetros OUT son variables sin declarar que se envían al procedimiento de
modo que si en el procedimiento cambian su valor, ese valor permanece en
ellas cuando el procedimiento termina.
 Parámetros IN OUT. Son una mezcla de los dos anteriores. Se trata de
variables declaradas anteriormente cuyo valor puede ser utilizado por el
procedimiento que, además, puede almacenar un valor en ellas. No se las
puede asignar un valor por defecto.

Para crear el procedimiento debemos anteponer la sentencia

CREATE [ OR REPLACE ]

La opción REPLACE hace que si ya existe un procedimiento con ese nombre, se

reemplaza con el que se crea ahora. Los parámetros son la lista de variables que
necesita el procedimiento para realizar su tarea. Para invocar al procedimiento o
procedimientos definidos debemos hacerlo dentro de un bloque BEGIN … END; o
también con la sentencia EXEC si lo ejecutamos desde SQL*Plus.

BEGIN

procedimiento1;

procedimiento2();

procedimiento3(parametro1, parametro2);

...

END;

o también en SQL*Plus:

EXEC procedimiento1;

EXEC procedimiento2();

EXEC procedimiento3(parametro1, parametro2);

Cuando se invoca a un procedimiento, si éste no tiene parámetros, se pueden omitir los

paréntesis (es decir la llamada al procedimiento procedimiento2() se puede hacer
simplemente escribiendo procedimiento2, sin paréntesis)

Para eliminar un procedimiento utilizamos la sentencia DROP PROCEDURE.

DROP PROCEDURE procedimiento;

Ejemplo:

CREATE OR REPLACE

PROCEDURE muestra_fecha IS

fecha DATE;

BEGIN

DBMS_OUTPUT.PUT_LINE ('Salida de información');

SELECT SYSDATE INTO fecha FROM DUAL;

DBMS_OUTPUT.PUT_LINE ('Fecha: ' || fecha);

END muestra_fecha;

Para crear el procedimiento muestra_fecha sin parámetros.

Para invocar el procedimiento muestra_fecha:

BEGIN

muestra_fecha;

END;

o también en SQL*Plus:

EXEC muestra_fecha;

Ejemplo de procedimiento con parámetros:

CREATE OR REPLACE

PROCEDURE escribe (texto VARCHAR2)

IS

BEGIN

DBMS_OUTPUT.PUT_LINE(texto);

END;

BEGIN

ESCRIBE('HOLA');

END;

o también en SQL*Plus:

EXEC ESCRIBE('HOLA');

Al declarar cada parámetro se indica el tipo de los mismos, pero no su tamaño; es decir
sería VARCHAR2 y no VARCHAR2(50).
5.4.10. Funciones

Una función es prácticamente idéntica a un procedimiento. También puede recibir

parámetros de entrada y realizar operaciones con dichos datos. Lo que distingue a una
función de un procedimiento es que la función siempre devuelve algún valor. Se usa la
palabra reservada FUNCTION. Su estructura simplificada es:

FUNCTION nombre RETURN tipoDedatos IS

bloque sin palabra DECLARE

Su estructura en detalle es:

FUNCTION nombre

[(parámetro1 [modelo] tipoDatos

[,parámetro2 [modelo] tipoDatos [,...]])]

RETURN tipoDeDatos

{IS|AS}

bloque sin palabra DECLARE

Para crear la función debemos anteponer la sentencia

CREATE [ OR REPLACE ]

La opción REPLACE hace que si ya existe una función con ese nombre, se reemplaza
con la que se crea ahora. Los parámetros son la lista de variables que necesita la función
para realizar su tarea. Para invocar la función debemos hacerlo dentro de una expresión.
Ejemplo:

SELECT ...función... FROM DUAL;

Para eliminar una función utilizamos la sentencia DROP FUNCTION.

DROP FUNCTION función;

Ejemplo:

CREATE OR REPLACE

FUNCTION SUMA (NUM1 NUMBER, NUM2 NUMBER)

RETURN NUMBER
IS

BEGIN

RETURN NUM1+NUM2;

END SUMA;

Para invocar la función definida debemos hacerlo dentro de una expresión. Ejemplos:

SELECT SUMA(5.7, 9.3) FROM DUAL;

SELECT SUMA(5.7, 9.3)*3 FROM DUAL;

SELECT 150/(SUMA(5.7, 9.3)*3) FROM DUAL;

SELECT SYSDATE+SUMA(10,2)-2 FROM DUAL;

5.4.11. Paquetes

Los paquetes sirven para agrupar bajo un mismo nombre funciones y procedimientos.
Facilitan la modularización de programas y su mantenimiento. Los paquetes constan de
dos partes:

 Especificación. Que sirve para declarar los elementos de los que consta el
paquete. En esta especificación se indican los procedimientos, funciones y
variables públicos del paquete (los que se podrán invocar desde fuera del
paquete). De los procedimientos sólo se indica su nombre y parámetros (sin el
cuerpo).
 Cuerpo. En la que se especifica el funcionamiento del paquete. Consta de la
definición de los procedimientos indicados en la especificación. Además se
pueden declarar y definir variables y procedimientos privados (sólo visibles
para el cuerpo del paquete, no se pueden invocar desde fuera del mismo).

-- PAQUETE ARITMETICA – Especificación

-- PACKAGE_ARITMETICA.SQL
CREATE OR REPLACE

PACKAGE aritmetica IS

version NUMBER := 1.0;

PROCEDURE mostrar_info;

FUNCTION suma (a NUMBER, b NUMBER) RETURN NUMBER;

FUNCTION resta (a NUMBER, b NUMBER) RETURN NUMBER;

FUNCTION multiplica (a NUMBER, b NUMBER) RETURN NUMBER;

FUNCTION divide (a NUMBER, b NUMBER) RETURN NUMBER;

END aritmetica;

-- PAQUETE ARITMETICA – Cuerpo

-- PACKAGE_BODY_ARITMETICA.SQL

CREATE OR REPLACE

PACKAGE BODY aritmetica IS

PROCEDURE mostrar_info IS

BEGIN

DBMS_OUTPUT.PUT_LINE

('Paquete de operaciones aritméticas. Versión ' || version);

END mostrar_info;
 FUNCTION suma (a NUMBER, b NUMBER) RETURN NUMBER IS

BEGIN

RETURN (a+b);

END suma;

FUNCTION resta (a NUMBER, b NUMBER) RETURN NUMBER IS

BEGIN

RETURN (a-b);

END resta;

FUNCTION multiplica (a NUMBER, b NUMBER) RETURN NUMBER IS

BEGIN

RETURN (a*b);

END multiplica;

FUNCTION divide (a NUMBER, b NUMBER) RETURN NUMBER IS

BEGIN

RETURN (a/b);

END divide;

END aritmetica;

Para utilizar el paquete debemos llamar al procedimiento y funciones deseadas.

Ejemplo de uso, sencillo:

BEGIN

ARITMETICA.MOSTRAR_INFO;

END;

SELECT ARITMETICA.SUMA(4,3) FROM DUAL;

SELECT ARITMETICA.RESTA(4,3) FROM DUAL;

SELECT ARITMETICA.MULTIPLICA(4,3) FROM DUAL;

SELECT ARITMETICA.DIVIDE(4,3) FROM DUAL;

Ejemplo de uso, más elaborado:

DECLARE

num1 NUMBER:= 2;

num2 NUMBER:= 5;

resultado NUMBER;

BEGIN

ARITMETICA.MOSTRAR_INFO;

SELECT ARITMETICA.SUMA(num1,num2) INTO resultado FROM DUAL;

DBMS_OUTPUT.PUT_LINE

('La suma de ' || num1 ||' y '|| num2 ||' es '|| resultado);

SELECT ARITMETICA.RESTA(num1,num2) INTO resultado FROM DUAL;

DBMS_OUTPUT.PUT_LINE

('La resta de ' || num1 ||' y '|| num2 ||' es '|| resultado);

END;

Oracle incorpora una serie de paquetes para ser utilizados dentro del código PL/SQL.
Es el caso del paquete DBMS_OUTPUT que sirve para utilizar funciones y
procedimientos de escritura como PUT_LINE. Otro ejemplo es el paquete
DBMS_RANDOM, que contiene diversas funciones para utilizar número aleatorios.
Quizá la más útil es la función DBMS_RANDOM.RANDOM que devuelve un número
entero (positivo o negativo) aleatorio (y muy grande). Ejemplos:

-- Si deseamos un número aleatorio entre 1 y 10

MOD(ABS(DBMS_RANDOM.RANDOM),10)+1

-- Entre 20 y 50

MOD(ABS(DBMS_RANDOM.RANDOM),31)+20
5.4.12. Disparadores (Triggers)

Es un bloque PL/SQL que se ejecuta de forma implícita cuando se ejecuta cierta

operación DML: INSERT, DELETE o UPDATE. Contrariamente, los procedimientos y
las funciones se ejecutan haciendo una llamada explícita a ellos. Un disparador NO
admite argumentos.

Sus aplicaciones son inmensas, como por ejemplo:

 Mantenimiento de Restricciones de Integridad complejas. Ej: Restricciones de

Estado (como que el sueldo sólo puede aumentar).
 Auditoría de una Tabla, registrando los cambios efectuados y la identidad del
que los llevó a cabo.
 Lanzar cualquier acción cuando una tabla es modificada.

Su estructura general es:

CREATE [OR REPLACE]

TRIGGER Nombre

{ BEFORE | AFTER } Suceso_Disparo ON Tabla

[ FOR EACH ROW [ WHEN Condición_Disparo ]]

Bloque_del_TRIGGER;

Para borrar un disparador:

DROP TRIGGER Nombre ;

Para habilitar/deshabilitar un disparador:

ALTER TRIGGER Nombre { ENABLE | DISABLE };

Para desactivar o activar todos los triggers de una tabla:

ALTER TABLE nombreTabla { DISABLE | ENABLE } ALL TRIGGERS;

Eso permite en una sola instrucción operar con todos los triggers relacionados con una
determinada tabla (es decir actúa sobre los triggers que tienen dicha tabla en el apartado
ON del trigger).

5.4.12.1. Tipos de disparadores

Tenemos tres tipos de triggers:

  Triggers de tabla. Se trata de triggers que se disparan cuando ocurre una
acción DML sobre una tabla.
 Triggers de vista. Se lanzan cuando ocurre una acción DML sobre una vista.
 Triggers de sistema. Se disparan cuando se produce un evento sobre la base
de datos (conexión de un usuario, borrado de un objeto,…)

Aquí sólo veremos los del primer y segundo tipo. Por lo que se dará por hecho en todo
momento que nos referiremos siempre a ese tipo de triggers.

Los triggers se utilizan para:

 Ejecutar acciones relacionadas con la que dispara el trigger

 Centralizar operaciones globales
 Realizar tareas administrativas de forma automática
 Evitar errores
 Crear reglas de integridad complejas

El código que se lanza con el trigger es PL/SQL. No es conveniente realizar excesivos

triggers, sólo los necesarios, de otro modo se ralentiza en exceso la base de datos.

5.4.12.2. Elementos de los triggers

Puesto que un trigger es un código que se dispara, al crearle se deben indicar las
siguientes cosas:

1. El evento que da lugar a la ejecución del trigger:

INSERT

UPDATE

DELETE

2. Cuándo se lanza el evento en relación a dicho evento:

BEFORE

AFTER

INSTEAD OF

 BEFORE: El código del trigger se ejecuta antes de ejecutar la instrucción DML

que causó el lanzamiento del trigger.
 AFTER: El código del trigger se ejecuta después de haber ejecutado la
instrucción DML que causó el lanzamiento del trigger.
 INSTEAD OF: El trigger sustituye a la operación DML . Se utiliza para vistas
que no admiten instrucciones DML.

3. Las veces que el trigger se ejecuta o tipo de trigger:

  de Instrucción. El cuerpo del trigger se ejecuta una sola vez por cada evento
que lance el trigger. Esta es la opción por defecto. El código se ejecuta aunque
la instrucción DML no genere resultados.
 de Fila. El código se ejecuta una vez por cada fila afectada por el evento. Por
ejemplo si hay una cláusula UPDATE que desencadena un trigger y dicho
UPDATE actualiza 10 filas; si el trigger es de fila se ejecuta una vez por cada
fila, si es de instrucción se ejecuta sólo una vez.

4. El cuerpo del trigger, es decir el código que ejecuta dicho trigger.

Ejemplo:

-- TRIGGER para realizar auditoría sobre operaciones en Empleados

CREATE OR REPLACE

TRIGGER Control_Empleados

AFTER INSERT OR DELETE OR UPDATE ON Empleados

BEGIN

INSERT INTO Ctrl_Empleados (Tabla,Usuario,Fecha)

VALUES ('Empleados', USER, SYSDATE);

END Control_Empleados;

-- La información de auditoría se guardará en la siguiente tabla

CREATE TABLE CTRL_EMPLEADOS

TABLA VARCHAR2(50),

USUARIO VARCHAR2(50),

FECHA DATE

);

5.4.12.3. Triggers de instrucción

CREATE [ OR REPLACE ]

TRIGGER Nombre

{ BEFORE | AFTER } evento1 [OR evento2[, ...]] ON tabla

[ DECLARE

declaraciones ]

BEGIN
 cuerpo

[ EXCEPTION

captura de excepciones ]

END;

El evento tiene esta sintaxis:

{ INSERT | UPDATE [OF columna1 [,columna2, ...]] | DELETE}

Los eventos asocian el trigger al uso de una instrucción DML. En el caso de la instrucción
UPDATE, el apartado OF hace que el trigger se ejecute sólo cuando se modifique la
columna indicada (o columnas si se utiliza una lista de columnas separada por comas).

En la sintaxis del trigger, el apartado OR permite asociar más de un evento al trigger (se
puede indicar INSERT OR UPDATE por ejemplo).

Ejemplo:

CREATE OR REPLACE

TRIGGER ins_personal

BEFORE INSERT ON personal

BEGIN

IF (TO_CHAR(SYSDATE,'HH24') NOT IN ('10','11','12') THEN

RAISE_APPLICATION_ERROR

(-20001,'Sólo se puede añadir personal entre las 10 y las 12:59');

END IF;

END;

Este trigger impide que se puedan añadir registros a la tabla de personal si no estamos
entre las 10 y las 13 horas.

5.4.12.4. Triggers de fila

Sintaxis básica:

CREATE [ OR REPLACE ]

TRIGGER Nombre

{ BEFORE | AFTER } evento1 [OR evento2[, ...]] ON tabla

FOR EACH ROW [ WHEN condición ]

[ DECLARE

declaraciones ]

BEGIN

cuerpo

[ EXCEPTION

captura de excepciones ]

END;

La cláusula FOR EACH ROW hace que el trigger sea de fila, es decir que se repita su
ejecución por cada fila afectada en la tabla por la instrucción DML. El apartado WHEN
permite colocar una condición que deben de cumplir los registros para que el trigger se
ejecute. Sólo se ejecuta el trigger para las filas que cumplan dicha condición.

Referencias NEW y OLD

Cuando se ejecutan instrucciones UPDATE, hay que tener en cuenta que se modifican
valores antiguos (OLD) para cambiarles por valores nuevos (NEW). Las palabras NEW
y OLD permiten acceder a los valores nuevos y antiguos respectivamente. En el
apartado de instrucciones del trigger (BEGIN …END) serían :NEW.nombre y
:OLD.nombre. Imaginemos que deseamos hacer una auditoría sobre una tabla en la que
tenemos un listado de las piezas mecánicas que fabrica una determinada empresa. Esa
tabla es PIEZAS y contiene el tipo y el modelo de la pieza (los dos campos forman la
clave de la tabla) y el precio de venta de la misma. Deseamos almacenar en otra tabla
diferente los cambios de precio que realizamos a las piezas, para lo cual creamos la
siguiente tabla:

CREATE TABLE PIEZAS_AUDITORIA

precio_viejo NUMBER(11,4),

precio_nuevo NUMBER(11,4),

tipo VARCHAR2(2),

modelo NUMBER(2),

fecha DATE

);

Como queremos que la tabla se actualice automáticamente, creamos el siguiente

trigger:

CREATE OR REPLACE

TRIGGER hacer_auditoria_piezas

BEFORE UPDATE OF precio_venta ON PIEZAS

FOR EACH ROW WHEN (OLD.precio_venta < NEW.precio_venta)

BEGIN

INSERT INTO PIEZAS_AUDITORIA

VALUES (

:OLD.precio_venta,

:NEW.precio_venta,

:OLD.tipo,

:OLD.modelo,

SYSDATE );

END hacer_auditoria_piezas;

Con este trigger cada vez que se modifiquen un registros de la tabla de piezas, siempre
y cuando se esté incrementado el precio, se añade una nueva fila por registro modificado
en la tabla de auditorías, observar el uso de NEW y de OLD y el uso de los dos puntos
(:NEW y :OLD) en la sección ejecutable. Cuando se añaden registros, los valores de
OLD son todos nulos. Cuando se borran registros, son los valores de NEW los que se
borran.

IF INSERTING, IF UPDATING e IF DELETING

Son palabras que se utilizan para determinar la instrucción DML que se estaba
realizando cuando se lanzó el trigger. Esto se utiliza en triggers que se lanza para varias
operaciones (utilizando INSERT OR UPDATE por ejemplo). En ese caso se pueden
utilizar sentencias IF seguidas de INSERTING, UPDATING o DELETING; éstas
palabras devolverán TRUE si se estaba realizando dicha operación.

CREATE OR REPLACE

TRIGGER nombre

BEFORE INSERT OR DELETE OR UPDATE OF campo1 ON tabla

FOR EACH ROW

BEGIN

IF DELETING THEN

instrucciones que se ejecutan si el TRIGGER saltó por borrar filas

ELSIF INSERTING THEN

 instrucciones que se ejecutan si el TRIGGER saltó por insertar
filas

ELSE

instrucciones que se ejecutan si el TRIGGER saltó por modificar

fila

END IF;

END;

5.4.12.5. Triggers sobre vistas

Hay un tipo de trigger especial que se llama INSTEAD OF y que sólo se utiliza con las
vistas. Una vista es una consulta SELECT almacenada. En general sólo sirven para
mostrar datos, pero podrían ser interesantes para actualizar, por ejemplo en esta
declaración de vista:

CREATE VIEW existenciasCompleta (tipo,modelo,precio,almacen,cantidad)

AS

SELECT p.tipo, p.modelo, p.precio_venta,e.n_almacen, e.cantidad

FROM PIEZAS p JOIN EXISTENCIAS e ON p.tipo=e.tipo AND

p.modelo=e.modelo

ORDER BY p.tipo,p.modelo,e.n_almacen;

Esta instrucción daría lugar a error

INSERT INTO existenciasCompleta VALUES('ZA',3,4,3,200);

Indicando que esa operación no es válida en esa vista (al utilizar dos tablas). Esta
situación la puede arreglar un trigger que inserte primero en la tabla de piezas (sólo si
no se encuentra ya insertada esa pieza) y luego inserte en existencias.

Eso lo realiza el trigger de tipo INSTEAD OF, que sustituirá el INSERT original por el
código indicado por el trigger:

CREATE OR REPLACE

TRIGGER ins_piezas_exis

INSTEAD OF INSERT ON existenciascompleta

BEGIN

INSERT INTO piezas(tipo,modelo,precio_venta)

VALUES(:NEW.tipo,:NEW.modelo,:NEW.precio);
 INSERT INTO existencias(tipo,modelo,n_almacen,cantidad)

VALUES(:NEW.tipo,:NEW.modelo, :NEW.almacen,:NEW.cantidad);

END;

Este trigger permite añadir a esa vista añadiendo los campos necesarios en las tablas
relacionadas en la vista. Se podría modificar el trigger para permitir actualizar, eliminar
o borrar datos directamente desde la vista y así cualquiera desde cualquier acceso a la
base de datos utilizaría esa vista como si fuera una tabla más. Orden de ejecución de
los triggers

Puesto que sobre una misma tabla puede haber varios triggers, es necesario conocer
en qué orden se ejecutan los mismos. El orden es:

1. Primero disparadores de tipo BEFORE de tipo instrucción

2. Disparadores de tipo BEFORE por cada fila
3. Se ejecuta la propia orden que desencadenó al trigger.
4. Disparadores de tipo AFTER con nivel de fila.
5. Disparadores de tipo AFTER con nivel de instrucción.

Errores de compilación

Es frecuente que cometamos algún tipo de error cuando definimos distintos tipos de
bloques. Podemos consultar los errores de compilación mediante la vista
USER_ERRORS;

SELECT * FROM USER_ERRORS;

Podemos acotar la consulta estableciendo una condición de búsqueda. Las más

interesantes suelen ser el nombre del bloque y el tipo de bloque.

SELECT * FROM USER_ERRORS

WHERE NAME='nombre_bloque' AND TYPE='tipo_bloque';

La columna TYPE puede tomar uno de los siguientes valores:

PROCEDURE

FUNCTION

PACKAGE

PACKAGE BODY

TRIGGER
Por ejemplo, para ver los errores producidos en el siguiente trigger:

CREATE OR REPLACE

TRIGGER Control_Empleados

AFTER INSERT OR DELETE OR UPDATE ON Empleados

BEGIN

INSERT INTO Ctrl_Empleados (Tabla,Usuario,Fecha)

VALUES ('Empleados', USER, SYSDATE);

END Control_Empleados;

podemos realizar la siguiente consulta:

SELECT name, type, text FROM user_errors

WHERE name='CONTROL_EMPLEADOS' AND type='TRIGGER';

Otra forma más cómoda de hacerlo es con la sentencia:

SHOW ERRORS;