Diseñando un Trigger T-SQL

En ocasiones, cuando construimos un proyecto que involucra un front-end de Access y un back-end de SQL Server, nos hemos encontrado con esta pregunta. ¿Deberíamos usar un disparador para algo? El diseño de un disparador de SQL Server para la aplicación Access puede ser una solución, pero solo después de consideraciones cuidadosas. En algún momento, esto se sugiere como una forma de mantener la lógica empresarial dentro de la base de datos, en lugar de la aplicación. Normalmente, me gusta tener la lógica de negocios definida lo más cerca posible de la base de datos. Entonces, ¿desencadenar la solución que queremos para nuestro front-end de Access?

Descubrí que la codificación de un activador de SQL requiere consideraciones adicionales y, si no tenemos cuidado, podemos terminar con un desorden más grande de lo que comenzamos. El artículo tiene como objetivo cubrir todas las trampas y técnicas que podemos usar para garantizar que cuando construimos una base de datos con desencadenantes, funcionen para nuestro beneficio, en lugar de simplemente agregar complejidad por el bien de la complejidad.

Consideremos las reglas …

Regla # 1: ¡No uses un trigger

Seriamente. Si está buscando el trigger a primera hora de la mañana, se arrepentirá de noche. El mayor problema con los trigger en general es que pueden ofuscar efectivamente la lógica de su negocio e interferir con los procesos que no deberían necesitar un trigger. He visto algunas sugerencias para desactivar los trigger cuando realiza una carga masiva o algo similar. Afirmo que este es un gran olor a código. No debe usar un trigger si tiene que estar activado o desactivado condicionalmente.

De forma predeterminada, primero deberíamos escribir procedimientos almacenados o vistas. Para la mayoría de los escenarios, harán el trabajo bien. No agreguemos magia aquí.

Entonces, ¿por qué el artículo sobre trigger entonces?

Porque los trigger tienen sus usos. Necesitamos reconocer cuándo debemos usar los trigger. También necesitamos escribirlos de una manera que nos ayude más en lugar de perjudicarnos.

Regla # 2: ¿Realmente necesito un trigger?

En teoría, los trigger suenan bien. Nos proporcionan un modelo basado en eventos para gestionar los cambios tan pronto como se modifiquen. Pero si todo lo que necesita es validar algunos datos, o asegurarse de que algunas columnas ocultas o tablas de registro estén pobladas … Creo que encontrará que un procedimiento almacenado hace el trabajo de manera más eficiente y elimina el aspecto mágico. Además, escribir un procedimiento almacenado es fácil de probar; simplemente configure algunos datos simulados y ejecute el procedimiento almacenado, verifique que los resultados sean lo que esperaba. Espero que estés usando un marco de prueba como tSQLt.

Y es importante tener en cuenta que, por lo general, es más eficiente usar restricciones de la base de datos que un trigger. Entonces, si solo necesita validar que un valor es válido en otra tabla, use una restricción de clave externa. Validar que un valor está dentro de cierto rango requiere una restricción de verificación. Esas deberían ser su opción predeterminada para ese tipo de validaciones.

Entonces, ¿cuándo realmente necesitaremos un trigger?

Se reduce a casos en los que realmente desea que la lógica de negocios esté en la capa SQL. Tal vez porque tiene varios clientes en diferentes lenguajes de programación haciendo inserciones / actualizaciones en una tabla. Sería muy complicado duplicar la lógica de negocios en cada cliente en su respectivo lenguaje de programación y esto también significa más errores. Para los escenarios en los que no es práctico crear una capa de nivel medio, los trigger son su mejor curso de acción para hacer cumplir la regla empresarial que no se puede expresar como una restricción.

Para usar un ejemplo específico de Access. Supongamos que queremos imponer la lógica empresarial al modificar los datos a través de la aplicación. Tal vez tengamos múltiples formularios de ingreso de datos vinculados a una misma tabla, o tal vez necesitemos admitir formularios de ingreso de datos complejos donde múltiples tablas base deben participar en la edición. Tal vez el formulario de entrada de datos debe admitir entradas no normalizadas que luego volvemos a componer en datos normalizados. En todos esos casos, podríamos simplemente escribir código VBA pero eso puede ser difícil de mantener y validar para todos los casos. Triggers nos ayuda a mover la lógica de VBA a T-SQL. La lógica empresarial centrada en los datos generalmente se coloca mejor cerca de los datos como sea posible.

Regla n. ° 3: el disparador debe estar basado en conjuntos, no en filas

Con mucho, el error más común cometido con un disparador es hacer que se ejecute en filas. A menudo vemos un código similar a este:

--Bad code! Do not use!
CREATE TRIGGER dbo.SomeTrigger
ON dbo.SomeTable AFTER INSERT 
AS
BEGIN
  DECLARE @NewTotal money;
  DECLARE @NewID int;
 
  SELECT TOP 1
    @NewID = SalesOrderID,
    @NewTotal = SalesAmount
  FROM inserted;
 
  UPDATE dbo.SalesOrder 
  SET OrderTotal = OrderTotal + @NewTotal
  WHERE SalesOrderID = @SalesOrderID
END;

El sorteo debería ser el mero hecho de que había un SELECT TOP 1 en una mesa insertada. Esto solo funcionará mientras insertemos solo una fila. Pero cuando se trata de más de una fila, ¿qué pasa con esas desafortunadas filas que vinieron en segundo lugar? Podemos mejorar eso haciendo algo similar a esto:

--Still bad code! Do not use!
CREATE TRIGGER dbo.SomeTrigger
ON dbo.SomeTable AFTER INSERT 
AS
BEGIN
  MERGE INTO dbo.SalesOrder AS s
  USING inserted AS i
  ON s.SalesOrderID = i.SalesOrderID
    OrderTotal = OrderTotal + @NewTotal
  ;
END;

Esto ahora está basado en conjuntos y, por lo tanto, ha mejorado mucho, pero todavía tiene otros problemas que veremos en las siguientes reglas …

Regla # 4: use una vista en su lugar.

Una vista puede tener un activador adjunto. Esto nos da la ventaja de evitar problemas asociados con los desencadenantes de una tabla. Podríamos importar fácilmente datos limpios en masa en la tabla sin tener que desactivar ningún desencadenante. Además, un disparador a la vista lo convierte en una opción explícita de aceptación. Si tiene funcionalidades relacionadas con la seguridad o reglas comerciales que requieren la ejecución de desencadenantes, simplemente puede revocar los permisos en la tabla directamente y, por lo tanto, canalizarlos hacia la nueva vista. Eso asegura que pasará por el proyecto y notará dónde se necesitan actualizaciones de la tabla para que luego pueda rastrearlas en busca de posibles errores o problemas.

La desventaja es que una vista solo puede tener un trigger INSTEAD OF adjunto, lo que significa que debe realizar explícitamente las modificaciones equivalentes en la tabla base usted mismo dentro del trigger. Sin embargo, tiendo a pensar que es mejor así porque también garantiza que sepa exactamente cuál será la modificación y, por lo tanto, le brinda el mismo nivel de control que normalmente tiene dentro de un procedimiento almacenado.

Regla # 5: El trigger debe ser mudo.

¿Recuerdas el comentario sobre depuración y prueba de un procedimiento almacenado? El mejor favor que podemos hacernos a nosotros mismos es mantener la lógica de negocios en un procedimiento almacenado y hacer que el trigger la invoque en su lugar. Nunca debe escribir lógica de negocios directamente en el trigger; eso efectivamente vierte concreto en la base de datos. Ahora está congelado a la forma y puede ser problemático probar adecuadamente la lógica. Su arnés de prueba ahora debe implicar alguna modificación en la tabla base. Esto no es bueno para escribir pruebas simples y repetibles. Esto debería ser lo más complicado, ya que se debería permitir que tu trigger sea:

CREATE TRIGGER [dbo].[SomeTrigger]
ON [dbo].[SomeView] INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
  DECLARE @SomeIDs AS SomeIDTableType
 
  --Perform the merge into the base table
  MERGE INTO dbo.SomeTable AS t
  USING inserted AS i
  ON t.SomeIDID = i.SomeID
  WHEN MATCHED THEN UPDATE SET 
    t.SomeStuff = i.SomeStuff,
    t.OtherStuff = i.OtherStuff
  WHEN NOT MATCHED THEN INSERT (
    SomeStuff,
    OtherStuff
  ) VALUES (
    i.SomeStuff,
    i.OtherStuff
  )
  OUTPUT inserted.SomeID INTO @SomeIDs(SomeID);
 
  DELETE FROM dbo.SomeTable
  OUTPUT deleted.SomeID INTO @SomeIDs(SomeID)
  WHERE EXISTS (
    SELECT NULL
    FROM deleted AS d
    WHERE d.SomeID = SomeTable.SomeID
  ) AND NOT EXISTS (
    SELECT NULL
    FROM inserted AS i
    WHERE i.SomeID = SomeTable.SomeID
  );
 
  EXEC dbo.uspUpdateSomeStuff @SomeIDs;
END;

La primera parte del trigger es básicamente realizar las modificaciones reales en la tabla base porque es un trigger INSTEAD OF, por lo que debemos realizar todas las modificaciones que serán diferentes dependiendo de las tablas que necesitemos administrar. Vale la pena enfatizar que las modificaciones deben ser principalmente textuales. No volvemos a calcular ni transformar ninguno de los datos. Guardamos todo ese trabajo adicional al final, donde todo lo que estamos haciendo dentro del trigger es completar una lista de registros que fueron modificados por el trigger y proporcionarn un procedimiento almacenado utilizando un parámetro con valores de tabla. Tenga en cuenta que ni siquiera estamos considerando qué registros se cambiaron ni cómo se cambió. Todo lo que se puede hacer dentro del procedimiento almacenado.

Regla # 6: El trigger debe ser idempotente siempre que sea posible.

En términos generales, los trigger DEBEN ser idempotentes. Esto se aplica independientemente de si se trata de un trigger basado en tablas o en vistas. Se aplica especialmente a aquellos que necesitan modificar los datos en las tablas base desde donde se está monitoreando el trigger . ¿Por qué? Porque si los humanos están modificando los datos que serán recogidos por el trigger, podrían darse cuenta de que cometieron un error, lo volvieron a editar o tal vez simplemente editen el mismo registro y lo guarden 3 veces. No estarán contentos si descubren que los informes cambian cada vez que realizan una edición que no se supone que modifique la salida del informe.

Para ser más explícito, podría ser tentador intentar optimizar el disparador haciendo algo similar a esto:

WITH SourceData AS (
  SELECT OrderID, SUM(SalesAmount) AS NewSaleTotal
  FROM inserted
  GROUP BY OrderID
)
MERGE INTO dbo.SalesOrder AS o
USING SourceData AS d
ON o.OrderID = d.OrderID
WHEN MATCHED THEN UPDATE SET
  o.OrderTotal = o.OrderTotal + d.NewSaleTotal;

Podemos evitar volver a calcular el nuevo total simplemente revisando las filas modificadas en la tabla insertada, ¿verdad? Pero cuando el usuario edita el registro para corregir un error tipográfico en el nombre del cliente, ¿qué sucederá? Terminamos con un total falso, y el disparador ahora está trabajando en nuestra contra.

En este momento, debería ver por qué la regla n. ° 4 nos ayuda al extraer solo las claves principales del procedimiento almacenado, en lugar de tratar de pasar cualquier información al procedimiento almacenado o hacerlo directamente dentro del desencadenador como lo habría hecho la muestra .

En cambio, queremos tener un código similar a este dentro de un procedimiento almacenado:

CREATE PROCEDURE dbo.uspUpdateSalesTotal (
  @SalesOrders SalesOrderTableType READONLY
) AS
BEGIN
  WITH SourceData AS (
    SELECT s.OrderID, SUM(s.SalesAmount) AS NewSaleTotal
    FROM dbo.SalesOrder AS s
    WHERE EXISTS (
      SELECT NULL
      FROM @SalesOrders AS x 
      WHERE x.SalesOrderID = s.SalesOrderID 
    )
    GROUP BY OrderID
  )
  MERGE INTO dbo.SalesOrder AS o
  USING SourceData AS d
  ON o.OrderID = d.OrderID
  WHEN MATCHED THEN UPDATE SET
    o.OrderTotal = d.NewSaleTotal;
END;

Usando @SalesOrders, todavía podemos actualizar selectivamente solo las filas que fueron afectadas por el trigger, y también podemos recalcular el nuevo total por completo y convertirlo en el nuevo total. Entonces, incluso si el usuario cometió un error tipográfico en el nombre del cliente y lo editó, cada guardado arrojará el mismo resultado para esa fila.

Más importante aún, este enfoque también nos proporciona una manera fácil de arreglar los totales. Supongamos que tenemos que hacer una importación masiva, y la importación no contiene el total, por lo que debemos calcularla nosotros mismos. Podemos escribir el procedimiento almacenado para escribir directamente en la tabla. Luego podemos invocar el procedimiento almacenado anterior pasando los ID de la importación, y estamos todos bien. Por lo tanto, la lógica que utilizamos no está ligada al trigger detrás de la vista. Eso ayuda cuando la lógica es innecesaria para la importación masiva que estamos realizando.

Si se encuentra con problemas para hacer que su trigger sea idempotente, es una fuerte indicación de que podría necesitar usar un procedimiento almacenado y llamarlo directamente desde su aplicación en lugar de confiar en los triggers. Una excepción notable a esta regla es cuando el trigger está destinado principalmente a ser un trigger de auditoría. En este caso, desea escribir una nueva fila en la tabla de auditoría para cada edición, incluidos todos los errores tipográficos que realiza el usuario. Esto está bien porque en ese caso, no hay cambios en los datos con los que el usuario está interactuando. Desde el punto de vista del usuario, sigue siendo el mismo resultado. Pero cada vez que el trigger necesita manipular los mismos datos con los que trabaja el usuario, es mucho mejor cuando es idempotente.