Desencadenadores (triggers)

Introducción

Se llama trigger (o disparador) al código que se ejecuta automáticamente cuando se realiza una determinada acción sobre la base de datos. El código se ejecuta independientemente de la aplicación que realizó dicha operación.

De esta forma tenemos tres tipos 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,…)

En este manual sólo se da cabida a 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.

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 o DELETE)
(2) Cuando se lanza el evento en relación a dicho evento (BEFORE (antes), AFTER (después) o INSTEAD OF (en lugar de))
(3) Las veces que el trigger se ejecuta (tipo de trigger: de instrucción o de fila)
(4) El cuerpo del trigger, es decir el código que ejecuta dicho trigger

Desencadenadores BEFORE y AFTER

 
En el apartado anterior se han indicado los posibles tiempos para que el trigger se ejecute. Éstos pueden ser:

♦ 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.

Tipos de trigger
 
Hay dos tipos 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.

Sintaxis de la creación de triggers

triggers de instrucción
CREATE [OR REPLACE] TRIGGER nombreDeTrigger
cláusulaDeTiempo evento1 [OR evento2[,...]]
ON tabla
[DECLARE
declaraciones
]
BEGIN
cuerpo
[EXCEPTION
captura de excepciones
]
END;
La cláusula de tiempo es una de estas palabras: BEFORE o AFTER. Por su parte 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.
triggers de fila

Sintaxis:

CREATE [OR REPLACE] TRIGGER nombreDeTrigger
cláusulaDeTiempo evento1 [OR evento2[,...]]
ON tabla
[REFERENCING {OLD AS nombreViejo | NEW AS nombreNuevo}]
FOR EACH ROW [WHEN condición]
[WHEN (condición)]
[declaraciones]
cuerpo

La diferencia con respecto a los triggers de instrucción está en la línea REFERENCING y en FOR EACH ROW. Ésta última es la que 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.
El apartado REFERENCING es el que permite indicar un nombre para los valores antiguos y otro para los nuevos.

TABLAS 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.
El apartado REFERENCING de la creación de triggers, permite asignar nombres a las palabras NEW y OLD (en la práctica no se suele utilizar esta posibilidad). Así NEW.nombre haría referencia al nuevo nombre que se asigna a una determinada tabla y OLD.nombre al viejo.

En el apartado de instrucciones del trigger (el BEGIN) hay que adelantar el símbolo “:” a las palabra NEW y OLD (serían :NEW.nombre y :OLD.nombre)
Imaginemos que deseamos hacer una auditoria 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_AUDIT(
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 crear_audit_piezas
BEFORE UPDATE OF precio_venta
ON PIEZAS
FOR EACH ROW
WHEN (OLD.precio_venta<NEW.precio_venta)
BEGIN
INSERT INTO PIEZAS_AUDIT
VALUES(:OLD.precio_venta, :NEW.precio_vent
:OLD.tipo,:OLD.modelo,SYSDATE);
END;

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 aditorí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 trigger1
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 filas
END IF
END; 


Triggers de tipo INSTEAD OF

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, EXISTENCIAS e
WHERE 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í cualquier desde cualquier acceso a la base de datos se utilizaría esa vista como si fuera una tabla más.

Funciones y paquetes

• INTRODUCCION

Una función es un conjunto de instrucciones en PL/SQL, que pueden ser llamados usando el nombre con que se le haya creado. Se diferencian de los procedimientos, en que las funciones retornan un valor al ambiende desde donde fueron llamadas.

La sintaxis para crear una función es la siguiente:

CREATE [OR REPLACE] FUNCTION name [(param [IN] datatype) . . .]
RETURN datatype
[IS|AS] pl/sql_subprogram

El uso de OR REPLACE permite sobreescribir una función existente. Si se omite, y la función ya existe, se producirá, un error. El unico modificador permitido para los parámetros es IN, y si se omite, se tomará por defecto. Es decir, solo se permiten parámetros de entrada.

A continuación se presenta un ejemplo de creación de una función:

SQL> CREATE FUNCTION get_bal (acc_no IN NUMBER)
1> RETURN NUMBER
2> IS
3> acc_bal NUMBER(11,2); /* declaración de una variable */
4> BEGIN
5> SELECT balance
6> INTO acc_bal /* asignación */
7> FROM accounts
8> WHERE account_id = acc_no;
9> RETURN(acc_bal);
10> END

La función get_bal retorna el balance de una cuenta dada.

Si se desea eliminar (borrar) una función, se usa la instrucción:

SQL> DROP FUNCTION name;

 

 

• PAQUETES

 

Los paquetes en Oracle se utilizan para guardar subprogramas y otros objetos de la base de datos.

Un paquete consta de los siguientes elementos:

• Especificación o cabecera: contiene las declaraciones públicas (es decir, accesibles desde cualquier parte de la aplicación) de sus programas, tipos, constantes, variables, cursores, excepciones, etc.
• Cuerpo: contiene los detalles de implementación y declaraciones privadas, es decir, accesibles solamente desde los objetos del paquete.

La sintaxis de la cabecera es la siguiente:

   create [or replace] package nombre_paquete as
      <declaraciones públicas>
      <especificaciones de subprogramas>
   end nombre_paquete;

La sintaxis del cuerpo sería la siguiente:

   create [or replace] package body nombre_paquete as
      <declaraciones privadas>
      <código de subprogramas>
      [begin
         <instrucciones iniciales>]
   end nombre_paquete;

Como podéis observar la cabecera se compila independientemente del cuerpo. Os dejo un ejemplo de paquete para que lo veáis más claro.

/* Cabecera */
create or replace package busar_emple as
   TYPE t_reg_emple is RECORD
      (num_empleado emple.emp_no%TYPE,
      apellido emple.apellido%TYPE,
      salario emple.salario%TYPE,
      departamento emple.dept_no%TYPE);
   procedure ver_por_numero(v_cod emple.emp_no%TYPE);
   procedure ver_por_apellido(v_ape emple.apellido%TYPE);
   function datos (v_cod emple.emp_no%TYPE)
      return t_reg_emple;
end buscar_emple;
/* Cuerpo */
create or replace package body buscar_emple as
   vg_emple t_reg_emple;
   procedure ver_emple; /* procedimiento privado*/
   procedure ver_por_numero (v_cod emple.emp_no%TYPE)
   is
   begin
      select emp_no, apellido, salario, dept_no into vg_emple from emple where emp_no=v_cod;
      ver_emple;
   end ver_por_numero;
   procedure ver_por_apellido (v_ape emple.apellido%TYPE)
   is
   begin
      select emp_no,apellido,salario,dept_no into vg_emple from emple where apellido=v_apellido;
      ver_emple;
   end ver_por_apellido;
   function datos (v_cod emple.emp_no%TYPE)
      return t_reg_emple
   is
   begin
      select emp_no,apellido,salario,dept_no into vg_emple from emple where emp_no=v_cod;
   procedure ver_emple
      is
      begin
         DBMS_OUTPUT.PUT_LINE(vg_emple.num_empleado || '*' || vg_emple.apellido || '*' || vg_emple.salario || '*'|| vg_emple.departamento);
   end ver_emple;
end buscar_emple;

Como podéis ver este paquete nos permite buscar un empleado de tres formas distintas y visualizar sus datos.

Utilización de los objetos definidos en los paquetes

Podemos utilizar los objetos definidos en los paquetes básicamente de dos maneras distintas:

• Desde el mismo paquete: esto quiere decir que cualquier objeto puede ser utilizado dentro del paquete por otro objeto declarado en el mismo.
  Para utilizar un objeto dentro del paquete tan sólo tendríamos que llamarlo. La llamada sería algo así: v_emple :=buscar_emple.datos(v_n_ape); (como veis no utilizamos el execute ya que nos encontramos dentro del paquete).
• Desde fuera del paquete: Podemos utilizar los objetos de un paquete siempre y cuando haya sido declarado en la especificación del mismo. Para llamar a un objeto o procedimiento desde fuera del paquete utilizaríamos la siguiente notación: execute nombre_paquete.nombre_procedimiento(lista de parametros);

Declaración de cursores en paquetes

En los paquetes también podemos introducir cursores, para ello debemos declararlo en la cabecera del paquete indicando su nombre, los parámetros y tipo devuelto. Para que lo veáis más claro os dejo un ejemplo a continuación:

CREATE or REPLACE PACKAGE empleados AS
   .....
   CURSOR a RETURN empleado%ROWTYPE;
   ...
   END empleados;
CREATE or REPLACE PACKAGE BODY empleados AS
   ....
   CURSOR a RETURN empleado%ROWTYPE
      SELECT * FROM empleado WHERE salario < 10000;
   ....
END empleados;

Los paquetes suministrados por Oracle son:

Standard : tiene la función to_char y abs
dbms_standard: tiene la función raise_aplication_error
dbms_output: con la función put_line
dbms_sql: que utiliza sql de forma dinámica.

NOTA: sql dinámico significa que el programa es capaz de ejecutar órdenes de definición y manipulación sobre objetos que sólo se conocen al ejecutar el paquete.
Un ejemplo de la utilización de dbms_sql es el siguiente:
   BEGIN
      ......
      id_cursor := DBMS_SQL.OPEN_CURSOR;
      DMBS_SQL.PARSE(id_cursor, instrucción,DMBS_SQL.V3);
      v_dum :=DMBS_SQL.EXECUTE(id_cursor);
      DMBS_SQL.CLOSE_CURSOR(id_cursor);
   ......

Lo que hacemos es abrir el cursor y nos devuelve el id del mismo para poder trabajar con él.
Después tenemos el DMBS_SQL.PARSE que analiza la instrucción que se va a ejecutar. Ya en la siguiente linea ejecutamos la sentencia y por último cerramos el cursor.

 

• CONCLUSIONES

 

Con la idea de facilitarnos las tareas que debemos de desempeñar los humanos, hemos venido inventado diversas herramientas a lo largo de nuestrahistoria, que nos permiten tener una mejor calidad de vida.

Los ordenadores son uno más de los inventos del hombre, aunque debemos decir que las tecnologías para su fabricación y explotación han tenido un desarrollo sorprendente a partir de la segunda mitad del siglo XX.

Esta herramienta por sí sola no es capaz de efectuar ninguna tarea, es tan sólo un conjunto de cables y circuitos que necesitan recibir instrucción por parte de los humanos para desempeñar alguna tarea. El problema entonces, se puede fijar en ¿cómo vamos a poder hacer que un conjunto de circuitos desempeñen una determinada tarea y nos entreguen los resultados que nosotros esperamos?, es decir, ¿de qué manera se puede lograr la comunicaciónentre el hombre y el ordenador?.

Así pues, tratando de dar una solución al problema planteado, surgieron los lenguajes de programación, que son como un lenguaje cualquiera, pero simplificado y con ciertas normas, para poder trasmitir nuestros deseos al ordenador.

El Sistema de Gestión de Bases de Datos (SGBD) Consiste en un conjunto de programas, procedimientos y lenguajes que nos proporcionan las herramientas necesarias para trabajar con una base de datos. Incorporar una serie de funciones que nos permita definir los registros, sus campos, sus relaciones, insertar, suprimir, modificar y consultar los datos.

Microsoft SQL Server 7.0 constituye un lanzamiento determinante para los productos de bases de datos de Microsoft, continuando con la base sólida establecida por SQL Server 6.5. Como la mejor base de datos para Windows NT, SQL Server es el RDBMS de elección para una amplia gama de clientes corporativos y Proveedores Independientes de Software (ISVs) que construyen aplicaciones de negocios.

Las necesidades y requerimientos de los clientes han llevado a la creación de innovaciones de producto significativas para facilitar la utilización, escalabilidad, confiabilidad y almacenamiento de datos.