Triggers y Stored Procedures

En esta introducción veremos que son estas rutinas que se ejecutan en el servidor mediante un lenguaje que es una mezcla entre el declarativo y el procedural.

Trigger

Como lo dice su nombre, un trigger es un evento que se dispara y que se ejecuta dadas ciertas condiciones.
Un trigger debe estar asociado a una tabla o vista, y debe asignarse el momento y el tipo de evento en el cual dispararse.

Los diferentes eventos posibles son INSERT, UPDATE y DELETE. El trigger se ejecuta cuando el servidor ejecuta alguna de estas instrucciones sobre la tabla o vista a la cual pertenece el trigger (y el trigger se fija como ACTIVE). Además puede configurarse el trigger para ser ejecutado a partir de varios tipos de eventos (ejemplo: INSERT OR UPDATE).

También puede especificarse el momento en el cual disparar la ejecución, existen dos posibilidades: antes (BEFORE) que se lleve a cabo la instrucción o después (AFTER).
Dependiendo del momento de disparo, existen variables especiales (NEW y OLD) que nos permiten acceder a los datos de la tupla en cuestión antes y luego de la ejecución de la instrucción.

Es muy importante aclarar que estas variables NEW y OLD no están disponibles en todos los momentos ni tipos de eventos.
La variable NEW existe solo para un trigger del tipo INSERT/UPDATE, accediendo a NEW.columna, accedemos al valor. Si es un trigger de tipo BEFORE se puede utilizar para asignar un valor nuevo a la columna, si es de tipo AFTER solo podemos leer el valor.
Y la variable OLD existe solo para un trigger del tipo AFTER UPDATE/DELETE pero solo para lectura.

Dentro del trigger podremos saber que tipo de evento disparó la ejecución mediante la lectura de INSERTING, UPDATING y DELETING. Esto es especialmente útil cuando se definen condiciones de ejecución para varios tipos de eventos, por ejemplo UPDATE OR DELETE.

Este es el esqueleto de la sintaxis para la creación de triggers:

SET TERM ^ ;



CREATE TRIGGER name [FOR table/view] 

 [IN]ACTIVE 

 [ON {[DIS]CONNECT | TRANSACTION {START | COMMIT | ROLLBACK}} ] 

 [{BEFORE | AFTER} INSERT OR UPDATE OR DELETE] 

 POSITION number 

AS 

BEGIN 

    /* enter trigger code here */ 

END^



SET TERM ; ^

Utilizamos SET TERM para cambiar temporalmente el separador de intrucciones ya que dentro del cuerpo del trigger (lo que colocaremos entre BEGIN y END) será necesario utilizar varios separados.

Veamos un ejemplo. Aqui tenemos un trigger para la tabla TBLPERSONAS, que se ejecuta (ACTIVE) antes de una inserción o una actualización (BEFORE INSERT OR UPDATE) y será el primer trigger que se ejecutará bajo estas condiciones (POSITION 0). Antes no lo aclaré y parece un buen momento, POSITION representa el orden de ejecución del trigger, el valor más bajo se ejecuta primero.
Siguiendo con el código vemos que dentro del cuerpo del trigger asignamos a la columna usuario el valor USER, que es una variable del servidor que nos permite conocer cual es el nombre de usuario logueado, es un varchar. Luego verificamos el dni, si no ha sido incluido en la sentencia INSERT o UPDATE, entonces lo generamos nosotros utilizando el generador gen_persona y la función GEN_ID.

SET TERM ^ ;

CREATE TRIGGER TRI_PERSONA FOR TBLPERSONAS ACTIVE

BEFORE INSERT OR UPDATE POSITION 0

AS

begin

  new.usuario = USER;

  if( new.dni is null ) then

    new.dni = GEN_ID(gen_persona, -1);

end^

SET TERM ; ^

La utilidad de este trigger en particular es la de "preparar" los datos de la tupla con valores válidos antes de ejecutar un INSERT o un UPDATE. Tome en cuenta que el dni está definido en la tabla como NOT NULL por lo tanto, le damos un valor válido y requerido.

Los triggers pueden incluso acceder a datos en otras tablas, realizar selects y modificaciones, pero a eso lo describiré en otro artículo sobre sql procedural. Se utilizan mucho para validar los datos que se quieren escribir en la tabla y generar excepciones en caso de error.
Por ahora con saber que son y como es el uso basico de los triggers alcanza para empezar a practicar.

Y algo muy importante que acabo de recordar y casi omito: ¿Cómo ejecuto el trigger?
La respuesta es simple, no se puede ejecutar directamente. Los trigger se disparan automáticamente cuando ejecutamos intrucciones INSERT, UPDATE y DELETE sobre la tabla en cuestión, el trigger es del tipo de la instrucción ejecutada y está ACTIVE.
Por lo que no debemos preocuparnos por ello, y lo mejor: usando triggers centralizamos muchas reglas de negocios en la propia base de datos, en la capa de datos. Esto nos permite crear clientes más livianos y mucho más simples de mantener.

Stored Procedures

La misma palabra lo dice, son procedimientos almacenados... en la base de datos!
La sintaxis es muy parecida a la de los triggers, algunas cuestiones ya no nos interesan como el momento y tipo de eventos. Un procedimiento es ejecutado a petición directa a diferencia de los triggers.

He aquí la sintaxis:

SET TERM ^ ;



CREATE PROCEDURE name 

 ( input_parameter_name < datatype>, ... ) 

RETURNS 

 ( output_parameter_name < datatype>, ... )

AS 

DECLARE VARIABLE variable_name < datatype>; 

BEGIN

  /* write your code here */ 

END^



SET TERM ; ^

Lo nuevo aquí es la posibilidad de incluir parámetros y devolver datos. Conceptualmente es muy similar a una función en un lenguaje funcional como pascal o php.

Nada mejor que ir directo al ejemplo en este caso.
Este procedimiento se encarga de eliminar los acentos y caracteres considerados "raros". Para ello solicita un varchar de 100 caracteres (ORIGEN) y retorna un varchar de 100 caracteres sin estos caracteres raros.
Cabe aclarar que el uso de parametros y de valores de retorno es opcional.
También vemos por primera vez el uso de variables locales del stored procedure (DECLARE VARIABLE CAR VARCHAR(1);), un simple varchar de 1. En los triggers tambien podemos declarar variables locales.
El cuerpo es muy facil de leer, note el uso de las variables dentro del cuerpo y como funcionan las condiciones del WHILE.

SET TERM ^ ;

CREATE PROCEDURE "AX_EliminarAcentos" (

    ORIGEN Varchar(100) )

RETURNS (

    RESULTADO Varchar(100) )

AS

DECLARE VARIABLE CAR VARCHAR(1);

begin

  CAR = '';

  RESULTADO = '';

  ORIGEN = ORIGEN || '#';  /* Valor Frontera */

  while (:ORIGEN is not null and :ORIGEN <> '') do begin

    if (:CAR = 'á') then CAR = 'a';

    if (:CAR = 'à') then CAR = 'a';

    if (:CAR = 'â') then CAR = 'a';

    if (:CAR = 'ä') then CAR = 'a';

    if (:CAR = 'é') then CAR = 'e';

    if (:CAR = 'è') then CAR = 'e';

    if (:CAR = 'ê') then CAR = 'e';

    if (:CAR = 'ë') then CAR = 'e';

    if (:CAR = 'í') then CAR = 'i';

    if (:CAR = 'ì') then CAR = 'i';

    if (:CAR = 'î') then CAR = 'i';

    if (:CAR = 'ï') then CAR = 'i';

    if (:CAR = 'ó') then CAR = 'o';

    if (:CAR = 'ò') then CAR = 'o';

    if (:CAR = 'ô') then CAR = 'o';

    if (:CAR = 'ö') then CAR = 'o';

    if (:CAR = 'ú') then CAR = 'u';

    if (:CAR = 'ù') then CAR = 'u';

    if (:CAR = 'û') then CAR = 'u';

    if (:CAR = 'ü') then CAR = 'u';

    if (:CAR = 'Á') then CAR = 'A';

    if (:CAR = 'À') then CAR = 'A';

    if (:CAR = 'Â') then CAR = 'A';

    if (:CAR = 'Ä') then CAR = 'A';

    if (:CAR = 'É') then CAR = 'E';

    if (:CAR = 'È') then CAR = 'E';

    if (:CAR = 'Ê') then CAR = 'E';

    if (:CAR = 'Ë') then CAR = 'E';

    if (:CAR = 'Í') then CAR = 'I';

    if (:CAR = 'Ì') then CAR = 'I';

    if (:CAR = 'Î') then CAR = 'I';

    if (:CAR = 'Ï') then CAR = 'I';

    if (:CAR = 'Ó') then CAR = 'O';

    if (:CAR = 'Ò') then CAR = 'O';

    if (:CAR = 'Ô') then CAR = 'O';

    if (:CAR = 'Ö') then CAR = 'O';

    if (:CAR = 'Ú') then CAR = 'U';

    if (:CAR = 'Ù') then CAR = 'U';

    if (:CAR = 'Û') then CAR = 'U';

    if (:CAR = 'Ü') then CAR = 'U';

    if (:CAR = 'ç') then CAR = 'c';

    if (:CAR = 'Ç') then CAR = 'C';

    if (:CAR = 'ñ') then CAR = 'n';

    if (:CAR = 'Ñ') then CAR = 'N';

    if (:CAR = 'º') then CAR = '';

    if (:CAR = 'ª') then CAR = '';

    if (:CAR = '.') then CAR = '';

    RESULTADO = RESULTADO || CAR;

    CAR = substring(ORIGEN from 1 for 1);

    ORIGEN = substring(ORIGEN from 2 for 100);

  end

end^

SET TERM ; ^

Quizá no sea un ejemplo muy elaborado, pero sirve y de mucho para entender, nuevamente, el concepto de un procedimiento almacenado y el tipo de utilidad que podriamos agregarle.
Por ejemplo, podriamos llamar a este procedimiento almacenado desde otro procedimiento almacenado, o incluso desde un trigger en el que "limpiariamos" alguna columna antes de agregarla a la tabla.
El uso de los procedimientos almacenados es muy amplio, aunque aqui no lo hayamos visto, puede retornar una tupla completa utilizando SUSPEND... será en otro artículo.

Que les sirva y comenten!