Unidad 9. Programación en TRANSACT SQL (VIII)


9.11. Desencadenadores o TRIGGERS

Un desencadenador (o Trigger) es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos.
SQL Server permite crear varios desencadenadores para una instrucción específica.
Según el tipo de evento que los desencadena se clasifican en:
  Desencadenadores DML
  Desencadenadores DDL
  Desencadenadores LOGON
Los desencadenadores DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.
Los desencadenadores DDL se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.
Los desencadenadores logon se activan en respuesta al evento LOGON que se genera cuando se establece la sesión de un usuario.
Nosotros limitaremos nuestro estudio a los desencadenadores DML.

9.12. CREATE TRIGGER

Esta instrucción nos permite definir un trigger:


    CREATE TRIGGER  [NombreEsquema.]NombreTrigger 

      ON {tabla | vista } [,...n] ]

      {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} 

      AS sentencia_sql  [;] [,...n ]   

NombreEsquema es el nombre del esquema al que pertenece el desencadenador DML. Los desencadenadores DML se limitan al esquema de la tabla o vista en la que se crearon.
NombreTrigger es el nombre que le queremos dar al desencadenador. No puede comenzar con los símbolos # o ##.
Tabla | vista es el nombre de la tabla o vista en la que se ejecuta el desencadenador. Sólo se puede hacer referencia a una vista mediante un desencadenador INSTEAD OF. No es posible definir desencadenadores DML en tablas temporales locales o globales.
DELETE  INSERT UPDATE Especifica las instrucciones de modificación de datos que activan el desencadenador cuando se intenta ejecutarlas en esta tabla o vista. Se debe especificar al menos una opción. En la definición del desencadenador se permite cualquier combinación de estas opciones, en cualquier orden.
AFTER indica que el desencadenador sólo se activa cuando todas las operaciones especificadas en la instrucción SQL desencadenadora se han ejecutado correctamente. Además, todas las acciones referenciales en cascada y las comprobaciones de restricciones deben ser correctas para que este desencadenador se ejecute.
AFTER es el valor predeterminado cuando sólo se especifica la palabra clave FOR.
Los desencadenadores AFTER no se pueden definir en las vistas.
INSTEAD OF indica que se ejecuta el desencadenador en vez de la instrucción SQL desencadenadora, por lo que se suplantan las acciones de las instrucciones desencadenadoras.
Como máximo, se puede definir un desencadenador INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio desencadenador INSTEAD OF.
Los desencadenadores INSTEAD OF no se pueden utilizar en vistas actualizables que usan WITH CHECK OPTION.
Los desencadenadores INSTEAD OF DELETE/UPDATE  no se permiten en tablas que tengan una clave ajena definida con ON DELETE/UPDATE CASCADE.
CREATE TRIGGER debe ser la primera instrucción en el proceso por lotes.
Un desencadenador se crea solamente en la base de datos actual; sin embargo, puede hacer referencia a objetos que están fuera de la base de datos actual.
Si se especifica el nombre del esquema del desencadenador hay que calificar también el nombre de la tabla o vista.
En un desencadenador se puede especificar cualquier instrucción SET. La opción SET seleccionada permanece en efecto durante la ejecución del desencadenador y, después, vuelve automáticamente a su configuración anterior.
Un desencadenador está diseñado para comprobar o cambiar los datos en base a una instrucción de modificación o definición de datos; no debe devolver datos al usuario por lo que se aconseja no incluir en un desencadenador instrucciones SELECT que devuelven resultados ni las instrucciones que realizan una asignación variable. Si es preciso que existan asignaciones de variable en un desencadenador, tenemos que utilizar la instrucción SET NOCOUNT al principio del mismo para impedir la devolución de cualquier conjunto de resultados.
Los desencadenadores DML usan las tablas lógicas  deleted e inserted. Son de estructura similar a la tabla en que se define el desencadenador, es decir, la tabla en que se intenta la acción del usuario. Las tablas deleted e inserted guardan los valores antiguos o nuevos de las filas que la acción del usuario puede cambiar.
Si un desencadenador INSTEAD OF definido en una tabla ejecuta una instrucción en la tabla que normalmente volvería a activarlo, al desencadenador no se lo llama de forma recursiva. En su lugar, la instrucción se procesa como si la tabla no tuviera un desencadenador INSTEAD OF e inicia la cadena de operaciones de restricción y ejecuciones de desencadenadores AFTER. Por ejemplo, si para una tabla se define un desencadenador como INSTEAD OF INSERT, y éste ejecuta una instrucción INSERT en la misma tabla, la instrucción INSERT ejecutada por el desencadenador INSTEAD OF no vuelve a llamar al desencadenador. La instrucción INSERT ejecutada por el desencadenador inicia el proceso que realiza las acciones de restricción y activa cualquier desencadenador AFTER INSERT definido para la tabla.
Si un desencadenador INSTEAD OF definido en una vista ejecuta una instrucción en la vista que normalmente volvería a activarlo, no se llamará el desencadenador de forma recursiva. En su lugar, la instrucción se resuelve a modo de modificaciones en las tablas base subyacentes de la vista. En este caso, la definición de la vista debe cumplir todas las restricciones para una vista actualizable.
Aunque una instrucción TRUNCATE TABLE es en realidad un desencadenador DELETE, no puede activar un desencadenador porque la operación no registra las eliminaciones de fila individuales.
Las siguientes instrucciones Transact-SQL no están permitidas en un desencadenador DML:
ALTER DATABASE       CREATE DATABASE            DROP DATABASE
Además, las siguientes instrucciones Transact-SQL no se permiten en el cuerpo de un desencadenador DML cuando éste se utiliza en la tabla o vista que es objeto de la acción desencadenadora:
CREATE INDEX          ALTER INDEX          DROP INDEX           DROP TABLE
ALTER TABLE cuando se utiliza para hacer lo siguiente:

  • Agregar, modificar o quitar columnas.
  • Cambiar particiones.
  • Agregar o quitar restricciones de tipo PRIMARY KEY o UNIQUE.

Ejemplo:


    USE Gestion8

    GO

    CREATE TRIGGER ActualizaVentasEmpleados

    ON pedidos FOR INSERT

    AS

    UPDATE  empleados SET ventas=ventas+inserted.importe

    FROM  empleados, inserted

    WHERE  numemp=inserted.rep;

    GO 
Febrero-2010
Pág. 9.8

Atrás  Inicio  Adelante



.