Ir al contenido principal

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.

Comentarios

  1. necesito es para el insert en trigger

    ResponderEliminar
  2. 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
  3. ¡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
  4. 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
  5. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  6. 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
  7. Este comentario ha sido eliminado por un administrador del blog.

    ResponderEliminar
  8. buen día, espero siga vigente este tema tengo una necesidad, pero no encuentro como acomodarla


    necesito hacer un trigger que después de insertar un registro solamente valide el campo email, en caso de que este sea nullo este sea cambiado a campo vacio.


    este es lo que tengo,



    create trigger email_tabla_insert on tabla
    after insert
    begin
    UPDATE tabla SET email = '' WHERE email IS NULL
    end email_tabla_insert

    ResponderEliminar
    Respuestas
    1. Hola, Marcos:

      Gracias por tu pregunta.

      En primer lugar, yo afrontaría el problema desde otra perspectiva: ¿por qué no cambias la columna para que no admita nulos en lugar de forzar esa restricción mediante un trigger? Sería mejor dejar a SQL Server que haga ese trabajo. Siempre lo hará mejor que nosotros :)

      Si no puedes hacerlo así, lo que le falta a tu trigger es hacer uso de la tabla especial inserted, para modificar solamente los registros afectados por la consulta que dispara el trigger.

      En primer lugar, añadiría la comprobación

      IF NOT UPDATE (email)
      RETURN

      Después el trigger quedaría así :

      UPDATE tabla SET email = ''
      FROM email_tabla_insert e
      INNER JOIN inserted ON inserted.emailId = e. emailId
      WHERE email IS NULL

      Saludos!

      Eliminar
    2. Hola, porque darle esta función tan simple de verificar, mediante código simple, a un trigger. antes de grabar el registro en tu aplicacion, verifica el contenido del campo y le envías un mensaje al operador en caso de que no este bien.

      Eliminar
    3. Hola, Adolfo:

      El que planteas es un debate muy interesante. La aplicación, como dices, debe hacer esa comprobación, pero eso no exime a la base de datos de hacerla también. Al fin y al cabo, los datos residen en la base de datos y no debería estar limitado el acceso a ellos a una única aplicación. ¿Qué pasa si, de repente, alguien inserta datos en tu base de datos a través de una hoja Excel? ¿O de diversas aplicaciones? La base de datos debe asegurar la integridad de los datos.

      Saludos.

      Eliminar
  9. Hola! quisiera orientación sobre cómo hacer para obtener el valor anterior de un campo cuando actualizo una tabla.
    Pretendo hacer un trigger que inserte en una tabla de log algunos datos de la tabla modificada (valores anteriores y valores nuevos) pero no tengo mucha idea de cómo hacerlo, de hecho no estoy segura que se pueda hacer con un trigger o deba hacerse con un stored procedure.
    Muchas gracias!!

    ResponderEliminar
    Respuestas
    1. Hola:

      Respecto a tu primera duda: para obtener el valor anterior tienes que hacer uso de la tabla deleted. Recuerda que hemos explicado que en el trigger tienes acceso a dos tablas especiales: deleted e inserted. La primera tiene los datos anteriores y la última, los actuales.

      Sobre si es la mejor solución posible... Depende. Al final, lo que quieres hacer es un mecanismo de auditoría. SQL Server ya implementa mecanismos de auditoría, dependiendo de que versión y qué edición estés usando. Sin embargo, hacerlo mediante triggers te va a funcionar siempre.

      Espero haberte ayudado :)

      Saludos,

      Jaime

      Eliminar
  10. Hola, necesito hacer un trigger que me impida insertar cuando haya un registro especifico, es decir que si tengo un consecutivo que va en el 333, cuando llegue al 400 no deje pasar mas consecutivos a partir de este

    ResponderEliminar
    Respuestas
    1. Hola:

      Para el deberás implementar un trigger para INSERT que is los valores de la pseudo tabla inserted y cuando detecte values iguales o superiores a 400 haga un ROLLBACK.

      Saludos

      Eliminar
  11. hola saludos, gracias por información tan valiosa. Una consulta para realizar un trigger como de validación por ejemplo en una base de datos de notas de cursos pero este trigger tiene que impedir que se cree el registro o se actualice si el valor de la nota que se esta ingresando es menor a 0 o mayor a 100. Muchas gracias desde ya. Saludos

    ResponderEliminar
    Respuestas
    1. Hola:

      Muchas gracias por tu comentario.

      Programar el trigger que indicas es sencillo. Basta con comprobar que ningún registro de la tabla inserted tiene un valor menor que 0 o mayor que 100 en el campo nota.

      Eliminar
  12. Buenas, una pregunta,

    quiero hacer un trigger el cual al insertar un registro en una tabla, cambie el campo llamado ESTADO a 'FACTURADA' de otra tabla lo que tengo es más o menos asi

    ALTER trigger [dbo].[tractualiza_estado]
    on [dbo].[detalle_orden_liberada]
    after insert
    as
    update orden set estado = 'FACTURADA'
    from orden inner join
    inserted as dol on dol.idorden = orden.idorden

    el problema es que al tener ese trigger, cuando intento insertar los campos desde la aplicación cliente NO me los inserta, es decir no inserta ningún campo, el trigger lo detiene.

    Alguna idea de cómo puedo solucionar ésto?

    ResponderEliminar
    Respuestas
    1. Hola:

      ¿Recibes algún mensaje de error? El trigger, a priori, parece correcto. Puede que el nombre de alguna tabla o algún campo esté mal, el trigger falle y eso deshaga la operación completa, incluido el insert.

      Saludos.

      Eliminar
  13. ESTOY HACIENDO ALGO ASI:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    create TRIGGER [dbo].[Validar_update]
    ON [dbo].[t_statusvb]
    AFTER UPDATE
    AS
    --IF UPDATE(serie)
    BEGIN

    INSERT INTO dbo.t_statusvb2(old.serie
    ,old.imei
    ,old.foliovb
    ,old.economico
    ,old.placa
    ,old.consesion
    ,old.titular
    ,old.empresa
    ,old.tip_vehi
    ,old.provee
    ,old.deleg
    ,old.muni
    ,old.gps
    ,old.btnp
    ,old.camins
    ,old.camcon
    ,old.camvid
    ,old.elemiden
    ,old.fecha
    ,old.estatus
    ,old.Nomcap
    ,old.fechavali
    ,observacion)

    SELECT serie
    ,[imei]
    ,[foliovb]
    ,[economico]
    ,[placa]
    ,[consesion]
    ,[titular]
    ,[empresa]
    ,[tip_vehi]
    ,[provee]
    ,[deleg]
    ,[muni]
    ,[gps]
    ,[btnp]
    ,[camins]
    ,[camcon]
    ,[camvid]
    ,[elemiden]
    ,[fecha]
    ,[estatus]
    ,[Nomcap]
    ,[fechavali]
    ,'update'
    FROM inserted

    END
    PERO NO FUNCIONA

    ResponderEliminar
    Respuestas
    1. Hola, Sandra:

      Gracias por tu comentario. Cuando dices que no funciona, ¿qué error se produce exactamente?

      Me llama la atención que todos los campos de la tabla sobre la que haces el INSERT INTO, dbo.t_statusvb2, se llamen old.nombre_de_columna.

      ¿Se llaman así en realidad las columnas de esa tabla? Si es así, al contener un punto en el nombre, deberías escribir las columnas entre corchetes, como haces en la parte del SELECT: [old.serie], ...

      Espero haber servido de ayuda.

      Saludos.

      Eliminar
  14. hola gracias por responder, le pongo el old. antes del campo porque quiero el registro anterior antes de hacer el update y que me lo envie a otra tabla a la de dbo.t_statusvb2 al ejecutar el query no me envia error pero al hacer un update en la tabla no me lo hace y no se inserta nada en la tabla, de echo intente cambiar la sentencia a BEFORE UPDATE pero me manda este error Msg 195, Level 15, State 1, Procedure Validar_update, Line 4 [Batch Start Line 7]
    'BEFORE' no es un desencadenador reconocido... mil graicas

    ResponderEliminar
    Respuestas
    1. Hola, Sandra:

      En ese caso debes usar la tabla del trigger deleted, tal y como se explica en el artículo. La sintaxis old. no es de SQL Server y por tanto te genera un error. Además, cuando hay un error en un trigger, SQL Server hace rollback de la sentencia que lo desencadena también, por eso te falla el update. Debes corregir el trigger para que todo funcione.

      Eliminar
  15. Hola espero me puedan ayudar, tengo un problema al tratar de insertar varios datos a una tabla y por medio de un trigger crear un registro en otra tabla por cada fila creada en la primera tabla, soy nuevo en esto de SQL Server y no sé como hacer este procedimiento, se me ocurría un foreach o algo similar, leí de los cursores pero no se aplicarlo

    Esto es lo que tengo ahora en mi trigger:
    Declare @id_seccion int
    Declare @sum_req int, @id_actividad int, @avance_req int, @avance_tareas int, @cant_tareas int
    BEGIN
    SET NOCOUNT ON;

    Declare @id_reqq int



    Set @id_actividad = (Select id_actividad From inserted)
    Set @id_seccion = (Select id_seccion From Actividades Where id_actividad = @id_actividad)

    Insert Into Logs (ds_log, id_actividad, ds_razon, dt_creacion)
    Select 'Se creó la actividad a: '+inserted.ds_actividad, inserted.id_actividad, 'Creación de actividad', CONVERT(varchar, getdate(), 111) From inserted

    Set @id_reqq = (Select id_req From inserted)

    If (@id_reqq != '')
    Begin
    Set @sum_req = (Select COUNT(*) From SE_Requirements
    inner join Actividades a on ds_Code = id_req
    Where id_actividad = @id_actividad)

    Set @cant_tareas = (Select cant_tareas From Actividades Where id_actividad = @id_actividad)

    Update Actividades set cant_tareas += @sum_req Where id_actividad = @id_actividad

    If (@sum_req != 0)
    Begin
    exec DB_Gestor_Actividades_Update_Advance @id_actividad = @id_actividad
    exec DB_Gestor_Proyectos_Update_Advance @id_seccion = @id_seccion
    End
    Insert Into Logs (ds_log, id_actividad, ds_razon, dt_creacion)
    Select 'Se asoció el requerimiento: '+inserted.id_req, inserted.id_actividad, 'Asociación de requerimiento', CONVERT(varchar, getdate(), 111) From inserted
    End

    END

    ResponderEliminar
    Respuestas
    1. Hola, Ferney:

      Estás intentando aplicar lógica de lenguajes de programación a SQL Server y eso no es siempre una buena idea. SQL Server trabaja mejor con conjuntos de resultados, operando con muchos registros a la vez, en lugar de uno a uno. Tal y como explicas, estás intentando hacer algo similar a un "foreach" o a trabajar registro a registro. Por desgracia no puedo ayudarte con un simple comentario, ya que hay varios conceptos que sin duda aprenderás pronto conforme vayas adentrándote en el mundo de SQL Server.

      Te animo a seguir con ello y verás como poco a poco lo verás más claro. Por ahora sólo puedo recomendarte que intentes pensar siempre en trabajar con todos los resultados a la vez. Es decir, un trigger puede afectar a uno o más registros simultáneamente. Si actualizas una tabla entera, por ejemplo, el trigger actuará sobre todos los registros de la tabla, y estos estarán en inserted y/o deleted, según el caso.

      Siento no poder ser más concreto.

      Saludos,

      Jaime

      Eliminar
  16. Buenos días. Muchas graciar por el aporte, la verdad es que es muy útil

    Me ha surgido una duda, en el caso de usar UPDATE(campo), si queremos comprobar más de uno? Es decir, en tu ejemplo, si solo quiero que se actualizen registros si el campo fecha y el campo nombre se actualizan como seria?

    UPDATE(campo1, campo2) es correcto?

    Gracias y saludos.

    ResponderEliminar
    Respuestas
    1. Hola.
      Creo que deberias ser asi!
      UPDATE(campo1) OR UPDATE(campo2) AND UPDATE(campo3)

      Saludos

      Eliminar

Publicar un comentario

Entradas populares de este blog

Aprendiendo a usar LEFT OUTER JOIN

En esta entrada pretendemos explicar los diferentes resultados obtenidos por distintas construcciones de consultas que, aparentemente, deberían producir el mismo conjunto de resultados. Así, veremos las diferencias entre filtrar los resultados de una query en la unión (Join) mediante condiciones ON y mediante cláusulas WHERE.

Variantes del SELECT COUNT con DISTINCT

Seguramente, muchos de vosotros habréis usado en innumerables ocasiones la función de T-SQL COUNT , que no hace sino devolver un número de registros: de una tabla, de un conjunto de resultados, etc... En una de sus aplicaciones, combinado con el DISTINCT -uno de los dos argumentos que admite- COUNT nos devuelve el número de valores únicos no nulos de la tabla o conjunto de resultados que estemos consultando. Pero ¡ojo! Cuidado con la sintaxis , o podemos obtener el valor equivocado sin darnos cuenta. No es lo mismo: SELECT COUNT (DISTINCT NombreCampo) FROM NombreTabla que: SELECT COUNT(*), DISTINCT NombreCampo FROM NombreTabla

Script para obtener el tamaño de todas las tablas de la base de datos

En algunas ocasiones podemos vernos con la necesidad de conocer qué tablas de nuestra base de datos están ocupando más espacio en disco. Por ejemplo, si disponemos de SQL Server Express , cuyas bases de datos están limitadas a 4GB o 10GB, según la versión que estemos usando -4, hasta 2005; 10, a partir de 2008-, aparte de usar las opciones de comprimir la base de datos, poner el log en el modo simple de recuperación o ajustar las políticas de crecimiento automático de nuestros ficheros, podemos necesitar averiguar qué tablas crecen más para tomar las decisiones oportunas.