Buscar este blog

lunes, 2 de septiembre de 2013

Programar triggers correctamente: qué son y cómo se usan inserted y deleted

Revisando recientemente la programación de algunos triggers, se han detectado algunos errores que se suelen cometer cuando se desconoce la idiosincrasia de estos objetos y sus tablas especiales inserted y deleted.

En uno de los triggers revisados, se quería copiar el valor de un campo datetime de la tabla para la que se había programado el trigger a otra. El código usado en el trigger (simplificado) era algo como lo siguiente:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  UPDATE Table2  
  SET Fecha = Table1.Fecha  
  FROM Table2  
  INNER JOIN Table1 ON Table1.PrimaryKeyField = Table2.PrimaryKeyField  
 END  

Como comprenderéis inmediatamente, un trigger así hace muchas más cosas que las pretendidas inicialmente. Sí, copiará la fecha de Table1 a Table2, pero, cada vez que actualicemos un solo registro en Table1, se actualizarán ¡todas las fechas! en Table2 a partir de Table1.

Observad el código detenidamente y veréis que en el trigger no hay ninguna condición que le diga a SQL Server "haz esto solamente para los registros afectados por la instrucción desencadenante". El resultado puede ser desastroso, ya que si se trata de tablas muy grandes estaremos actualizando multitud de registros innecesariamente, perjudicando el rendimiento de nuestro servidor gravemente.

Como sabréis, un trigger se ejecuta cada vez que hay una sentencia que modifica la tabla que contiene el trigger. Es decir, podemos definir triggers que se ejecuten tras un DELETE, un INSERT o un UPDATE. Asimismo, también podemos definir triggers que se ejecuten para dos de estos tipos de instrucciones o para las tres. Para ello, debemos especificar tras la palabra clave AFTER a qué tipo de instrucciones queremos que afecte el trigger.

Bien, una vez hemos definido cuando ha de actuar el trigger, ¿cómo sabemos qué registros han sido afectados por la ejecución de la sentencia desencadenante?

¡inserted y deleted, al rescate!


Los triggers definen dos tablas especiales que contienen toda la información que necesitamos: inserted y deleted. Ambas son subconjuntos de la tabla que contiene el trigger, justamente con los registros que nos interesan: los afectados por la sentencia desencadenante.

inserted contiene los registros con los nuevos valores para triggers que se desencadenan con sentencias INSERT (nuevos registros) y UPDATE (nuevo valor para registros actualizados).

deleted, por su parte, contiene los registros con los viejos valores para triggers que se desencadenan con sentencias DELETE (registros borrados) y UPDATE (valor anterior para los registros actualizados).

Así pues, en un trigger definido como AFTER INSERT sólo dispondremos de la tabla inserted, en uno definido como AFTER DELETE solamente tendremos la tabla deleted, mientras que, finalmente, ambas tablas estarán disponibles en triggers definidos para ejecutarse tras un UPDATE con AFTER UPDATE, pudiendo consultar así de los valores antes y después de actualizarse los registros correspondientes.

Así pues, podemos pensar ahora en cómo reescribir el trigger anterior para que solamente los registros afectados actualicen el valor del campo Fecha en Tabla1.

El resultado sería el siguiente:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  UPDATE Table2  
  SET Fecha = inserted.Fecha  
  FROM Table2  
  INNER JOIN inserted ON inserted.PrimaryKeyField = Table2.PrimaryKeyField  
 END  

Así, tendremos todos nuestros registros actualizados cada vez que se ejecute un UPDATE sobre Table1, sin alterar otros registros no implicados en la operación (cada registro de Table2 se actualizará únicamente en el momento en que se modifique el valor del campo Fecha para su registro correspondiente en Table1).

Otros errores típicos al programar un trigger


Finalmente, hay otro par de detalles que podemos comentar para mejorar nuestra programación de triggers. Como alguno ya os habréis preguntado: ¿y qué pasa si el campo Fecha no ha sido actualizado? ¿No estamos haciendo UPDATES innecesarios sobre Table2?

Efectivamente, pero también existe una solución. En un trigger del tipo AFTER UPDATE podemos comprobar si un campo determinado ha sido afectado por la operación desencadenante mediante la función UPDATE(campo).

Así, nuestro trigger podría (y debería) mejorarse de la siguiente manera:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  SET NOCOUNT ON;  
  IF UPDATE(Fecha)  
  BEGIN  
   UPDATE Table2  
   SET Fecha = inserted.Fecha  
   FROM Table2  
   INNER JOIN inserted ON inserted.PrimaryKeyField = Table2.PrimaryKeyField  
  END  
 END  
*Sobre el uso de SET NOCOUNT ya hablamos en otra entrada anterior.

Finalmente, los triggers deben programarse para que funcionen independientemente de si el numero de registros afectados es uno, muchos o ninguno (¡Ojo! También se desencadenan los triggers si no hay registros afectados, y esto hay que tenerlo muy en cuenta).

Con inserted y con deleted hay que trabajar siempre como si de una tabla más se tratase y, ¡muy importante!, pensar que puede contener más de un registro. Siempre hay que programar los triggers pensando en que no solamente se verá afectada una fila. Muchas veces se programan así porque conocemos la aplicación cliente y sabemos que solamente opera con los registros de uno en uno. Pero, creedme, llegará el día en que haréis un UPDATE para más de un registro y vuestro trigger dejará de funcionar correctamente en ese momento.

El siguiente código es muy habitual en muchos triggers, y sólo funcionaría cuando hay un registro afectado:

 CREATE TRIGGER Trigger1 ON Table1  
 AFTER UPDATE  
 AS  
 BEGIN  
  SET NOCOUNT ON;  
  IF UPDATE(Fecha)  
  BEGIN  
   DECLARE @fecha datetime  
   DECLARE @primaryKeyField int  
   SELECT @primaryKeyField = PrimaryKeyField, @fecha = Fecha FROM inserted  
   UPDATE Table2  
   SET Fecha = @fecha  
   WHERE Table2.PrimaryKeyField = @primaryKeyField  
  END  
 END  

Y, por supuesto, ¡no vale abrir un cursor sobre inserted para iterar por los registros afectados y hacer lo que se muestra en el código anterior fila a fila! En el 99% de las veces que vayáis a usar un cursor, existe una alternativa basada en conjuntos de registros, mucho más eficiente, que podréis usar. Los cursores tienen su aplicación en SQL Server, pero casi nunca es la de iterar por conjuntos de resultados para operar con ellos uno a uno. Normalmente, un JOIN con inserted o deleted y la otra tabla o tablas con las que queremos operar en el trigger suele ser el mecanismo apropiado trabajar con todos los registros a la vez.

12 comentarios:

  1. un trigger que cada vez que se inserte un dato en una tabla.
    Registre esos mismos datos, junto con la fecha en que hizo la inserción y la operación, en
    una tabla nueva. añadiendo otros campos llamados operación y
    fecha_registro.

    ResponderEliminar
    Respuestas
    1. Hola Jorge:

      Por lo que comentas, lo que necesitas es una auditoría sobre los cambios que se producen en esa tabla. Existen varias formas de generar una auditoría en SQL Server, dependiendo de la versión y edición que tengas.
      Si tienes una edición Enterprise, puedes probar SQL Audit: https://msdn.microsoft.com/es-es/library/cc280386(v=sql.120).aspx
      Si tienes ya un SQL 2016 puedes probar con temporal tables: https://msdn.microsoft.com/en-us/library/dn935015.aspx
      Si no es ninguno de los casos anteriores, la forma habitual de auditar cambios en una tabla es, como supones, mediante triggers. Hay varias formas de hacerlo, yo elegí seguir el método expuesto en este artículo: http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL
      Te ofrece un procedimiento almacenado al que simplemente pasándole el nombre de la tabla te crea la tabla de audit y tres triggers para INSERT, DELETE y UPDATE que escriben en la tabla de audit.
      La única modificación que le hice yo es añadir una columna así:
      [HostName] [nvarchar](500) NOT NULL CONSTRAINT [DF_VariablesGlobales_audit_HostName] DEFAULT (HOST_NAME()),

      Eliminar
  2. ¡Hola como hago para crear un trigger que me enlace dos bases de datos una en sql y otra en un servidor local como seria la sintaxis del trigger y en que parte iria el update y el insert?

    ResponderEliminar
    Respuestas
    1. No soy ningún experto, pero creo que sería utilizando la opcion de configurar ese segundo servidor no local como un linked server junto con el acceso a su base de datos, de forma que puedas utilizarla en el mismo trigger mediante la sentencia de inserción, aunque mi recomendación es que utilices una replicación merge transaccional entre ambos servidores dado que esto te permite tener control sobre fallos de sincronización entre ambos y el performance de un trigger así es muy lento y te pueden aparecer roll-backs inesperados.

      En cualquier caso a la hora de hacer el insert

      sería algo como esto:

      (depués de tener linkeado el server con la base de datos necesaria)

      insert into NombreDenominaciónLinkedServer.NombreBasedeDatos.dbo.Tabla_Objeto(nombre de los campos afectados en la actualización separados por comas)
      values(nombre de los campos o variables de insert de la actualización separados por comas).

      Eliminar
  3. Hola Jaime,

    Tengo un problema con un insert trigger, hasta ahora por cada registro que me insertaba desde una replica merge generaba con este trigger una segunda inserción por cada registro en otra tabla, pero ahora necesito controlar el orden en el que me llegan los registros desde la replica merge y aunque los inserto en el origen en un orden determinado y correcto el proceso interno de replicación merge me varía dicho orden de inserción en el destino y me descoloca el conjunto de los registros en la tabla de recepción.

    He pensado en ir comprobando por cada inserción el número del conjunto de registros que necesito insertar en orden y si cuando llega el ultimo disparar en el trigger un insert con select de varios registros cuando el orden es el adecuado pero para esto necesito hacer un segundo cursor en el propio trigger que me recoja uno a uno los datos de cada registro a insertar en esa segunda tabla, esto no es lo óptimo puesto que hasta no tener el ultimo de los registros insertados no controlo 1 a 1 cada registro, sino que cuando recibo la ultima de las inserciones hago una inserción de todos con un orden especifico. A nivel operativo de los triggers ¿ves esto correcto y esto se puede realizar así o ves otra manera de comprobar por cada registro y sean ordenados por un trigger al completar un conjunto de ellos?.

    ResponderEliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. fíjate que tuve un caso así, con una tabla no podía hacer el update a un conjunto de resultado, y tuve que hacer un job para que trabajará con cursores, esto será correcto?¿?. digo, no había tocado situaciones con triggers, según sé son mala practica. que tanto hay de cierto? y como pdríahacerle para que mi trabajo fuera más rápido?

    ResponderEliminar