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.