sql-server - update - trigger sql server w3schools

¿Cómo agrego una columna "última modificación" y "creada" en una tabla de SQL Server?

Question

Diseño un nuevo esquema db para una base de datos SQL Server 2012.

Cada tabla debe tener dos columnas adicionales llamadas modified y created que se deben cambiar automáticamente tan pronto se inserta o actualiza una fila.

No sé cómo es la mejor manera de llegar allí.

Supongo que el disparador es la mejor manera de manejarlo.

Estaba tratando de encontrar ejemplos con desencadenadores ... pero los tutoriales que encontré insertan datos en otra tabla, etc.

Supuse que era un escenario bastante común, pero no pude encontrar la respuesta todavía.

Answer 1

En general, puede tener las siguientes columnas:

• Ultima modificacion por
• LastModifiedOn
• Creado por
• Creado en

donde LastModifiedBy y CreatedBy son referencias a una tabla de users ( UserID ) y las columnas LastModifiedOn y CreatetOn son columnas de fecha y hora.

Usted tiene las siguientes opciones:

1. Solución sin desencadenantes: he leído en alguna parte que " la mejor manera de escribir activadores no es escribirlos " y debe saber que generalmente están perjudicando el rendimiento. Por lo tanto, si puede evitarlos, es mejor hacerlo, incluso el uso de activadores puede parecer lo más fácil de hacer en algunos casos.

   Por lo tanto, simplemente edite todas las INSERT y UPDATE para incluir el UserID actual y la fecha y hora actuales. Si dicho user ID no se puede definir (usuario anónimo) puede usar 0 lugar y el valor predeterminado de las columnas (en caso de que no se especifique ningún user ID , será NULL ). Cuando ve valores NULL insertados, debe encontrar los enunciados "culpables" y editarlos.

2. Solución con activadores: puede crear el desencadenante AFTER INSERT, UPDATE y rellenar las columnas de los usuarios allí. Es fácil obtener la fecha y hora actual en el contexto del desencadenador (use GETUTCDATE() por ejemplo). El problema aquí es que los desencadenantes no permiten pasar / aceptar parámetros. Entonces, como no está insertando el valor de user ID y no puede pasarlo al desencadenador. ¿Cómo encontrar el usuario actual?

   Puede usar SET CONTEXT_INFO y CONTEXT_INFO . Antes de que insert y update declaraciones, debe usar SET CONTEXT_INFO para agregar el current user ID actual al contexto actual y en el desencadenante está utilizando la función CONTEXT_INFO para extraerlo.

Por lo tanto, al utilizar activadores, nuevamente debe editar todas sus cláusulas INSERT y UPDATE , por eso prefiero no usarlas.

De todos modos, si necesita tener solo columnas de fecha y hora y no creadas / modificadas por columnas, el uso de activadores es más duradero y más fácil, ya que no va a editar ninguna otra declaración ahora y en el futuro.

Con SQL Server 2016 ahora podemos usar la función SESSION_CONTEXT para leer los detalles de la sesión. Los detalles se establecen utilizando sp_set_session_context (como read-only read and write o read and write ). Las cosas son un poco fáciles de usar:

EXEC sp_set_session_context ''user_id'', 4;  
SELECT SESSION_CONTEXT(N''user_id'');  

Un buen ejemplo .

Answer 2

La columna created es simple: solo una columna DATETIME2(3) con una restricción predeterminada que se establece cuando se inserta una nueva fila:

Created DATETIME2(3) 
   CONSTRAINT DF_YourTable_Created DEFAULT (SYSDATETIME())

Por lo tanto, cuando inserte una fila en YourTable y no especifique un valor para Created , se establecerá en la fecha y hora actuales.

La modified es un poco más laboriosa, ya que tendrás que escribir un disparador para el caso AFTER UPDATE y actualizarlo; no puedes declarar declarativamente a SQL Server que haga esto por ti ....

Modified DATETIME2(3)

y entonces

CREATE TRIGGER updateModified
ON dbo.YourTable
AFTER UPDATE 
AS
   UPDATE dbo.YourTable
   SET modified = SYSDATETIME()
   FROM Inserted i
   WHERE dbo.YourTable.PrimaryKey = i.PrimaryKey

Debe unirse a la pseudo tabla Inserted que contiene todas las filas que se actualizaron con su tabla base en su clave principal para esa tabla.

Y deberá crear este disparador AFTER UPDATE para cada tabla en la que desee tener una columna modified .