Twitter Facebook RSS Feed

jueves, 26 de noviembre de 2009 a las 10:27hs por Dario Krapp

Introducción, conceptos y definiciones básicas

Los cursores en SQL Server pueden definirse como un elemento que representará a un conjunto de datos determinado por una consulta T-SQL. Los cursores permiten recorrer fila a fila, leer y eventualmente modificar dicho conjunto de resultados.

Existe una amplia variedad de opciones y de funciones para crear y operar sobre cursores en SQL Server. En este artículo, donde hemos utilizado SQL Server 2008 Enterprise Edition para los ejemplos, damos un paseo por las posibilidades disponibles.

La creación y utilización de un cursor estará compuesta, como es de esperarse, por una serie de instrucciones T-SQL, las cuales podrán separarse en grupos bien diferenciados. Estos son: Declaración, Apertura, Acceso a datos, Cierre y Desalojo. A continuación detallamos cada grupo de instrucciones.

Declaración

El primer paso constará de la declaración del cursor, donde se indicarán (junto con el nombre del cursor) la consulta que el mismo representará y algunas otras características bastante interesantes.

Un ejemplo de declaración de cursor es el siguiente:

DECLARE ProdInfo CURSOR FOR SELECT Name FROM Production.Product

Donde ProdInfo representa el nombre del cursor y la sentencia “SELECT ProductNumber,Name FROM Production.Product” es el conjunto de datos del mismo.

Como comentamos previamente, en este paso es posible definir algunas características del comportamiento del cursor, por ejemplo la sentencia:

DECLARE ProdInfo CURSOR READ_ONLY FOR SELECT Name FROM Production.Product

Indica que el cursor será de solo lectura. Más adelante veremos en detalle las opciones disponibles, por el momento nuestro objetivo es crear un cursor lo más simple posible.

Apertura

La apertura del cursor ejecutará la consulta definida en el paso previo y cargará los datos en el mismo. La función OPEN de T-SQL permite efectuar esta terea. Para continuar con el ejemplo previo, la forma de abrir el cursor será la siguiente:

OPEN ProdInfo

Recorrido del cursor y acceso a los datos

Este paso consta de recorrer los resultados del cursor. La instrucción FETCH permite efectuar dicha operación. Las filas leídas podrán copiarse a variables utilizando la sentencia INTO en combinación con la sentencia FETCH, por ejemplo:

FETCH NEXT FROM ProdInfo INTO @Description

Esta sentencia toma la siguiente fila de resultados del cursor y lo aloja en la variable @Description.

Un detalle a comentar es que en la sentencia INTO (como puede verse en el ejemplo anterior) el mapeo entre columnas del cursor y variables se realiza implícitamente, asignándose la primera columna a la primera variable, la segunda columna a la segunda variable y así sucesivamente. Esto implica que deberán crearse tantas variables como columnas se definan en la declaración del cursor y las mismas deberán ubicarse en el mismo orden que se encuentran definidas las columnas en la sentencia SELECT de la declaración.

Como cada sentencia FETCH leerá un registro, una pregunta interesante que podríamos hacernos es, ¿de qué manera podremos saber si existe un próximo o previo registro, o si hemos llegado al límite (ya sea superior o inferior)?. La respuesta se encontrará en una variable de SQL Server llamada @@FETCH_STATUS que tomará el valor 0 si la lectura del registro ha sido correcta.

En este punto será también posible modificar o eliminar las filas que se van recorriendo, como veremos al final del artículo.

Cierre del cursor

En el cierre del cursor se liberarán los registros tomados por el mismo. Una vez que el cursor es cerrado ya no podrá recorrerse el conjunto de resultados hasta que el mismo sea reabierto, la sentencia CLOSE cerrará un cursor abierto y la sintaxis puede verse a continuación:

CLOSE  ProdInfo

Desalojo del cursor

Este paso eliminará la referencia al cursor definido previamente, por lo que ya no será posible realizar una reapertura del mismo, en este caso la sintaxis será:

DEALLOCATE ProdInfo

Ejemplo de cursor

A continuación juntaremos todos los pasos descriptos previamente y crearemos el ejemplo más simple de un cursor el cual recorrerá la tabla Production.ProductDescription de la base de datos AdventureWorks y mostrará por pantalla el valor del registro Description luego que el mismo ha sido copiado a la variable @Description.

DECLARE @Description AS nvarchar(400)
DECLARE ProdInfo CURSOR FOR SELECT [Description] FROM Production.ProductDescription
OPEN ProdInfo
FETCH NEXT FROM ProdInfo INTO @Description
WHILE @@fetch_status = 0
BEGIN
    PRINT @Description
    FETCH NEXT FROM ProdInfo INTO @Description
END
CLOSE ProdInfo
DEALLOCATE ProdInfo

En el ejemplo puede verse la utilización de la variable @@FETCH_STATUS para verificar la existencia de la fila leída, por otra parte la sentencia FETCH NEXT devolverá la próxima fila de datos, siempre que la misma exista.
Este ejemplo, a pesar de parecer tan trivial y claro esconde un amplia variedad de comportamientos que el cursor asumirá, y que en este caso han tomado el valor por defecto, ya que no los hemos definido explícitamente.
El próximo paso será dar una recorrida por estas opciones y entender cuales se adaptarán mejor a nuestras necesidades.

Opciones y más opciones

Hasta este momento todo es muy sencillo, una pregunta que al menos a mí me parece interesante de hacernos es ¿qué sucederá si mientras estamos recorriendo los registros en nuestro cursor los datos son modificados externamente?, para poder representar este escenario una posibilidad es ejecutar el cursor que habíamos definido previamente en modo debug en el SQL Server Management Studio, como se muestra a continuación:


Cursor en modo debug

Si mientras los datos son recorridos por el cursor, (En el ejemplo puede verse que la variable @Description ha tomado el valor “Serious back-country riding….”) en forma paralela ejecutamos una consulta de actualización (mientras dejamos el cursor esperando en la sentencia FETCH) desde otro SQL Server Management Studio, como se muestra en el siguiente ejemplo:


Query Externo

Notaremos que en la próxima iteración cuando el comando FETCH es ejecutado, los cambios actualizados externamente serán automáticamente leídos. En el ejemplo que hemos propuesto, puede verse que la variable @Description ha tomado el valor “!A true multi-sport bike that offers…” agregándose el signo «!» al comienzo del contenido del campo Description. Si probamos eliminar o insertar registros notaremos que los cambios son también reflejados.


Cursor Dynamic

Este comportamiento dependerá del tipo de cursor a crearse y se determinará en el momento de su definición, para este caso específico, el tipo de cursor se denominará cursor dinámico y es la definición asumida por defecto, aunque puede definirse explícitamente de la siguiente forma:

DECLARE ProdInfo CURSOR DYNAMIC FOR SELECT….

Otra posibilidad disponible es la siguiente:

DECLARE ProdInfo CURSOR STATIC FOR SELECT…..

Si realizamos las mismas pruebas veremos que el comportamiento del cursor es completamente distinto y la actualización (tanto como las inserciones y eliminaciones de registros) externas serán ignorada hasta que el cursor sea reabierto.

La tercera opción disponible es la denominada KEYSET que permitirá al cursor detectar las actualizaciones y eliminaciones, pero no registros insertados, la forma de definir un cursor KEYSET es la siguiente:

DECLARE ProdInfo CURSOR KEYSET FOR SELECT….

Hay un detalle que creo que es muy interesante en este punto, para poder dar un ejemplo, supongamos que poseemos la tabla llamada Ejemplo1 (con unos pocos registros insertados), como se muestra en la imagen a continuación:


Tabla Ejemplo1

Y definimos un cursor KEYSET que recorrerá la tabla mientras que paralelamente realizamos modificaciones (UPDATES) externamente sobre los registros que el cursor va recorriendo, al igual que lo habíamos hecho previamente sobre la tabla Production.ProductDescription.

Si hacemos esta prueba veremos que algo extraño sucede, y es que el cursor se comporta como un cursor STATIC a pesar de haberse definido como KEYSET. Si observamos más detalladamente la estructura de la tabla Ejemplo1 notaremos otro detalle, y es que no hay clave primaria definida. Si definimos una clave primaria (obviamente sobre la columna ID en este caso) todo funcionará según lo esperado.
En este caso aunque a primera vista este comportamiento parezca incomprensible, es en realidad bastante lógico, ya que todo cursor KEYSET copiará las claves de la consulta que recorrerá en una tabla temporal en la base de datos tempdb, y utilizará esta clave para acceder a las tablas originales, pero en este caso no hay ninguna clave que pueda copiar, el cursor no tiene forma de referenciar a la tabla original. La estrategia que tomará el cursor bajo esta situación es copiar directamente todas las filas a una tabla temporal en tempdb y esta es exactamente la estrategia que utilizan los cursores STATIC (creo esta observación nos servirá de paso para cuestionarnos si los cursores STATIC o KEYSET son la opción más deseable en temas relacionados con la performance). La conclusión de esta prueba es que en ciertas ocasiones un cursor de un tipo determinado podrá modificar su comportamiento dependiendo de otras condiciones, este comportamiento es conocido como conversión implícita de cursor. Más adelante retormaremos este concepto.

Finalmente existe una cuarta opción denominada FAST_FORWARD, la cual creará un cursor de solo avance y solo lectura. Aún no hemos mencionado que significa un cursor de solo avance ni un cursor de solo lectura, pero lo haremos en unos pocos párrafos.

Si nos detenemos en la sentencia FETCH del código que hemos escrito hasta el momento podría parecernos interesante el hecho que la misma se encuentra acompañada de la sentencia NEXT, la cual como hemos visto en los ejemplos devuelve la próxima fila de resultados, pero no es de sorprender que exista además la posibilidad de retroceder el cursor para acceder la fila anterior a la actual. Las sentencias FIRST, PRIOR, NEXT y LAST (en combinación con la sentencia FETCH) perimirán acceder a la primera, previa, próxima y última fila respectivamente. Por otra parte las opciones ABSOLUTE y RELATIVE permitirán avanzar o retroceder una cantidad determinada de registros (en forma absoluta o relativa al registro actual) pudiéndose utilizar, además de constantes, variables del tipo smallint, tinyint o int para indicar la magnitud del desplazamiento. En el siguiente ejemplo (que si bien no parece darle mucha importancia al algoritmo del viajero) se muestran algunas de estas posibilidades:


Cursor Scroll

Para que todas estas opciones de desplazamiento se encuentren disponibles el cursor deberá definirse como SCROLL, que es la opción por defecto, por lo que no es necesario definirlo explícitamente, pero podrá hacerse tal como se muestra en el ejemplo previo.

Otra posibilidad es la de definir el cursor como FORWARD_ONLY. Esta opción, como su nombre nos indica, no permitirá realizar movimientos de retroceso en el cursor, en realidad la opción es aún más restrictiva y solo permitirá efectuar operaciones FETCH NEXT, pero a cambio nos bridará una mucho mejor performance que la opción SCROLL.

Actualización de datos y modos de bloqueo

Hasta el momento, los cursores que hemos visto, realizaban operaciones de lectura de datos, no vale la pena mencionar que es totalmente factible incluir operaciones de actualización de datos mientras que los mismos son leídos, como se muestra en el ejemplo:


Update leyendo

No me imagino una opción menos performante para actualizar la columna Description, pero omitamos ese detalle, ya que solo hemos incluido estas líneas de código para mencionar que es posible definir cursores actualizables y realizar la misma tarea de la siguiente forma (aunque obviamente la performance sigue siendo igual de lamentable):


Cursor Update

Un detalle a mencionar es que en este caso el cursor deberá ser DYNAMIC o KEYSET (siempre que existan las claves primarias necesarias para la segunda opción) ya que en caso contrario la actualización fallará.

Es posible incluso indicar en el cursor que columnas se modificarán, utilizando la sentencia FOR UPDATE FOR, (en caso contrario se asumirá que cualquier columna podrá modificarse, como sucede en el ejemplo previo). Para probarlo realizaremos la siguiente modificación en el codigo:


Cursor For Update Of

Se ha producido un error debido a que la columna “Description” no ha sido informada como una columna de modificación. La modificación funcionará correctamente si la columna Description es agregada a la lista de campos modificables.

Cabe mencionar que la sentencia FOR UPDATE OF no protegerá al campo generando un error si el registro es eliminado, para verificar esto podrá reemplazarse la sentencia UPDATE por DELETE como se muestra a continuación:

DELETE FROM Production.ProductDescription
WHERE CURRENT OF ProdInfo

En este punto, donde ya nos hemos formulado infinidad de preguntas, podremos continuar con nuestras interrogantes y peguntarnos si la actualización utilizando las sentencia UPDATE o DELETE con WHERE CURRENT OF tendrá algún detalle del cual no nos hemos percatado aún. Si estamos tranquilos porque en los casos previos todo ha funcionado según lo esperado, deberíamos hacer la siguiente prueba; En un cursor de modificación luego de que una fila es leída por el cursor, pero antes que el mismo la modifique, la modificaremos externamente, por ejemplo utilizando otro SQL Server Management Studio.

Realizar esta tarea es sencillo si es posible debugear el código, como ya hemos hecho previamente y como se indica a continuación:


Cursor Optimista

En este caso la actualización de la fila en cuestión (o sea la fila que ha sido leída pero actualizada luego que la sentencia update externa ha modificado los datos) no podrá realizarse y se obtendrá un mensaje similar al siguiente:


Error concurrencia optimista

Lo que ha sucedido estará relacionado con el tipo de bloqueo del cursor y el mensaje “Optimistic concurrency check failed” es todo un indicio. Los cursores son por defecto optimistas, esto significa que cuando leen un registro utilizan un número de versión para saber si el mismo ha sido externamente modificado, si el número de versión ha cambiado del leído en la sentencia FETCH la actualización fallará como nos ha sucedido en el ejemplo previo. Esta táctica de bloqueos es bastante lógica para los cursores, si consideramos que son operaciones extremadamente lentas, por lo que no es deseable que mantengan registros bloqueados. La opción OPTIMISTIC es la asumida por defecto, aunque podrá especificarse explícitamente utilizando la no sorpresiva palabra clave OPTIMISTIC tal como se muestra en el ejemplo:

DECLARE ProdInfo CURSOR DYNAMIC FORWARD_ONLY OPTIMISTIC FOR SELECT ...

Es posible modificar este comportamiento y definir cursores que bloqueen la fila mientras la misma es modificada, la palabra clave SCROLL_LOCKS permitirá realizar esta poco recomendable modificación del comportamiento del cursor. Bajo este contexto, si repetimos la prueba previa notaremos que la sentencia UPDATE externa quedará en espera, ya que ha sido bloqueda por el cursor. Ni pensar en la escalabilidad del proyecto que emplee esta posibilidad, quizás el termino pesimista sea más que adecuado.

Finalmente la última opción disponible de bloqueo, conocida como READ_ONLY, no realizará bloqueos ni controles de versión pero no permitirá actualizar registros.

Previamente habíamos mencionado a los cursores del tipo FAST_FORWARD, este tipo de cursor definirá un cursor READ_ONLY y FORWARD_ONLY conjuntamente y será la opción de cursor más performante posible.

Los cursores además podrán definirse como locales (LOCAL) o globales (GLOBAL), en el primer caso tendrán el alcance del stored procedure, trigger o batch (grupo de sentencias) que lo ha definido, en el segundo caso su alcance será a nivel de conexión.

Por otra parte, podrá incluirse en la definicion del cursor la palabra clave TYPE_WARNING, que indicará que se informen las conversiones implícitas de un cursor, y si nos preguntamos ¿qué es una conversión implícita de cursor?, recordaremos que habíamos mencionado un ejemplo hace algunos párrafos cuando un cursor definido como KEYSET se comportaba como cursor STATIC, eso es exactamente una conversión implícita de cursor (o degradación del cursor). Existen varias conversiones de este tipo dependiendo del tipo de cursor definido y la condición subyacente (y es indudablemente útil estar familiarizado con ellas para evitar confusiones), para quienes estén interesados en conocerlas en profundidad les dejo el siguiente enlace:

Conversiones implícitas de cursor

la forma de incluir esta palabra clave en la declaración se muestra en el siguiente ejemplo:

DECLARE ProdInfo CURSOR KEYSET TYPE_WARNING FOR SELECT [Descripcion] FROM Ejemplo1

Si el cursor es convertido, se indicará con un mensaje similar al siguiente:

The created cursor is not of the requested type. 

Finalmente, un último detalle a comentar es que además del diverso conjunto de opciones que hemos visto en este articulo, los cursores en SQL Server aceptarán las opciones de sintaxis SQL-92 de las cuales podemos destacar, la posibilidad de crear cursores de un tipo denominado INSENSITIVE, que no son más que cursores de tipo SCROLL Y READ_ONLY conjuntamente.

Como resumen final, podemos comentar que los cursores son una posibilidad potente y de fácil uso para acceder y modificar datos sobre SQL Server, pero no debemos olvidar que son muy costosos en términos de performance, por lo que cuando sea aplicable otro tipo de opción (como por ejemplo una variable tabla) la misma será en el común de los casos una elección menos costosa. Por otra parte, cuando no sea posible evitar el uso de cursores es importante recordar que los cursores READ_ONLY serán la opción de bloqueo menos costosa y que un cursor FORWARD_ONLY será la mejor opción de recorrido. Si tenemos la suerte de poder combinar ambas opciones (READ_ONLY y FORWARD_ONLY) en un cursor FAST_FORWARD estaremos en el mejor de los casos.

También es importante recordar que las opciones STATIC y KEYSET no son las más deseables ya que copiarán datos (ya sea claves o filas enteras) a la tabla tmpdb y que el bloqueo SCROLL_LOCKS es el menos popular, ya que comprometerá más la escalabilidad que las otras opciones de bloqueo.

Terminando con las conclusiomes solo me queda por desear que este artículo haya sido de ayuda a quienes no estén familiarizados con cursores y para quienes ya los conozcan, espero que hayan encontrado en estas líneas algún detalle inesperado o que habían olvidado. Nos vemos en el próximo artículo!.

52 comentarios »

  1. HMB dice:

    Claro y sencillo… me ayudo.

  2. Rayco dice:

    Una buena iniciación al uso de los cursores en Transact SQL, buen aporte.

  3. Kristian dice:

    Excelente esta muy entendible, gracias por el aporte, pero desearia obtener mas info tal vez me podrian ayudar con un manual. O donde puedo conseguir

  4. jcore dice:

    muy buen aporte

  5. andrestkd dice:

    Practico y concreto, buen aporte.

  6. Jose Zarate dice:

    Excelente!! mas claro lo tengo

  7. jeremiasm dice:

    En que parte se guardan los cursores, tengo sql server 2008 y no encuentro en el explorador de objetos el cursor creado.

    • Dario Krapp dice:

      Hola jeremiasm, buenos días

      El tema es que un cursor no es un objeto persistente que se almacena en tablas del SQL como por ejemplo las tablas, los stored procedures o los indices entre otros.
      Los cursores son objetos que se crean en memoria (por eso es importante utilizar la sentencia deallocate para desalojarlo de la memoria que utiliza SQL Server). Por ese motivo no los vas a poder encontrar en el object explorer.
      Slds

  8. Luis dice:

    entendi muy bien, se agradece

  9. Edwin dice:

    Gracias Por la Ayuda …
    Buen Aporte .

  10. Sebastian dice:

    Gracias por el aporte, por cierto que buen nombre el del sitio, me veo muy identificado con este!.

  11. ERNESTO dice:

    Muy buen comentario , quisiera me pudieran ayudar , quiero realizar un cursor haciendo lo siguiente espero me puedan ayudar tengo 2 tablas
    Tabla 1 Folio
    Tabla 2 Folio + Precio

    quisiera actualizar mi tabla 1 de la 2 por medio de un cursor,quedo en espera de sus valiosos comentarios

  12. manuel lora dice:

    excelente me gusto

  13. Leo dice:

    Tremendo!!

  14. KS dice:

    muy bueno, pero quisiera que me puedas ayudar, quiero hacer un cursor que me ayude a actualizar un campo por ejemplo:

    telefono DNI1
    telefono DNI1

    de lo anterior quisiera lo siguiente:

    telefono1 DNI1
    telefono2 DNI1

    Tendras algun material que me pueda ayudar.

    Saludos

  15. jrcastillof dice:

    Muy Buen Aporte,, lo voy a revisar, mas tarde (a la media noche), pero por lo que leí se nota claro y sencillo.

  16. VerSanCheZ dice:

    Hola a todos, tengo dos tablas ( Empleados y Entradas ), lo que quiero saber es como le hago para que la consulta me devuelva lo siguiente:

    No. Nombre Reg1 Reg2 Reg3 Reg4
    01 Pedro 07:10:20 11:45:00 12:36:14 18:50:44
    02 Luis 09:15:20 11:05:00 11:36:14 17:50:44

    La Tabla Empleado tienen los siguientes Campos;
    No. int
    Nombre varchar

    Registros de la tabla de Empleados
    01 Pedro
    02 Luis
    03 Paco

    Y la Tabla Entradas
    No. int
    Fecha date
    Registro time(0)

    Registros de la tabla Entradas
    01 2015-01-01 07:10:20
    02 2015-01-01 09:15:20
    01 2015-01-01 11:45:00
    02 2015-01-01 11:05:00
    01 2015-01-01 12:36:14
    02 2015-01-01 11:36:14
    01 2015-01-01 18:50:44
    02 2015-01-01 17:50:44

  17. carlos dice:

    Realmente muy útil!

  18. Jonathan dice:

    Muy bien explicado, excelente (y)

  19. Jesus Ob Ac dice:

    gracias por el aporte, me ha servido como no tienes idea.

    saludos

  20. Roman dice:

    Muchas gracias! Mas claro imposible!!!

  21. Jaqueline Pérez dice:

    Muchas Gracias, es muy util esta información.

    Pero tengo una duda, cree un cursor dentro de un procedimiento almacenado para hacer mas sencilla su ejecución, al ejecutarlo por primera vez no hay problema funciona bien, pero si lo ejecuto por segunda vez no me regresa nada, tengo que esperar para volverlo a ejecutar, a pesar de que tengo el «Dellocate» y el «Close», ¿Alguna idea de que pudiera pasar?

  22. Jack SK dice:

    Oye, muchas gracias por compartir esta información tan importante de una manera tan clara.

  23. Daniel dice:

    Muy buena explicación!!! Clara y sencilla

  24. Eduardo R. A. dice:

    Hola Dario. Primero gracias por su ayuda.

    Como puedo hacer en el uso de cursores para ver los resultados del proceso de cada registro del cursor. He notado que cuando utilizo cursores los resultados solo se muestran al finalizar el proceso completo del cursor.

    Saludos.
    Nuevamente GRACIAS.

  25. Oscar Ocar's Quiñonez dice:

    Buenísima explicación!!! Me sirvió bastante!

  26. Edgard Huillca dice:

    Como llamo a un cursor dentro de un store

  27. efuv0464 dice:

    Se puede declarar un cursor con la base de datos pasada como parámetro al sp????

  28. kunaguaro007 dice:

    El dia que quiten esta pagina me quedo sin cursores, tengo como 5 años copiando y pegando el codigo jajajajaja

  29. juanmadhardy dice:

    muy bueno, y muy bien escrito.

  30. Sergio Euan dice:

    Muy bueno amigo mio, he revisado este post dos veces y las dos ocasiones me ha servido de mucho. Muchas gracias por el aporte.

  31. Gerardo Chavarria dice:

    Gracias por el aporte, estoy empezando en el uso de cursores

  32. Chava ch dice:

    tengo dudaa

    DECLARE ProdInfo CURSOR FOR SELECT Nombre , precio , cantidadtotal FROM Production.Product

    se puede hacer esto??? el cursor puede guardar mas parametros?

  33. omar dice:

    muy bueno ,siempre ocupe este ejemplo para mis cursores. Gracias

  34. Juan José dice:

    ¡Que pro!

  35. Angel dice:

    Gracias totales la pagina tiene articulos muy interesantes!

  36. Excelente y al Grano.

  37. cesar dice:

    Muy conciso…gracias

  38. jeanytc dice:

    Excelente explicación, gracias por el aporte.

  39. Jaxpf dice:

    Excelente, me ha servido en el trabajo.

  40. Chema dice:

    Muchas gracias. Muy clarito el uso de los cursores.
    Pero a mí me está dando un error cerca de FOR.

    DECLARE @cursor CURSOR FOR
    SELECT DISTINCT actualizacion FROM......
  41. laura dice:

    excelente gracias

  42. Juan Carlos Badillo Goy dice:

    Saludos, necesito ayuda con este trigger que su objetivo sería guardar una auditoría de los datos de la tabla paises,
    donde quisiera guardar en el formato NombreColumna:-:Valor::-::NombreColumna:-:Valor, de todas las columnas que su
    DATA_TYPE sea (‘INT’, ‘VARCHAR’, ‘CHAR’).

    Cualquier ayuda o sugerencia la agradecería, estoy tratando de hacer el insert primero, la sería para las tres accciones, en el update solo guardaría las columnas que fueron modificadas, no todas.

    CREATE TRIGGER TR_Audit_Paises ON dbo.Paises
        FOR INSERT, UPDATE, DELETE
    AS
        DECLARE @SysUser	varchar(100)
        DECLARE @FullName	varchar(250)
        DECLARE @TableName  varchar(250)
        DECLARE @Action     varchar(50)	
    	DECLARE @OldValue	varchar(Max)
    	DECLARE @NewValue	varchar(Max)
    	DECLARE @COLUMN_NAME AS varchar(100)
    	DECLARE @DATA_TYPE AS varchar(100)
    	DECLARE @SQLTEXT AS nvarchar(Max)
    	DECLARE @SEPARATOR AS VARCHAR(5)
    	DECLARE @SEPARATORCOLUMN AS VARCHAR(5)
    	DECLARE @COLUMN_VALUE AS nvarchar(Max)
    	DECLARE @DATAFULLTEXT AS NVARCHAR(Max) 
    	
    	SET @TableName = 'Paises';
    
    	DECLARE ColumnInfo CURSOR FOR SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='paises' AND DATA_TYPE IN ('INT', 'VARCHAR', 'CHAR') ORDER BY ORDINAL_POSITION
    
    	SET @SQLTEXT = ''
    	SET @SEPARATOR = ':::'
    	SET @SEPARATORCOLUMN = '::::'
    	SET @DATAFULLTEXT = ''
    	
        IF EXISTS ( SELECT 0 FROM Deleted )
            BEGIN
                IF EXISTS ( SELECT 0 FROM Inserted )
                    BEGIN
                        --UPDATE
    					SET @OldValue = ''
                    END
                ELSE
                    BEGIN
                        --DELETE
    					SET @OldValue = ''
                    END  
            END
        ELSE
            BEGIN
                --INSERT
    			SELECT @SysUser = I.LastUserUpdate FROM Inserted I;
    			IF @SysUser  '' 
    				BEGIN
    					SELECT @FullName = FullName FROM dbo.Users WHERE UserName = @SysUser	
    				END
    			ELSE
    				BEGIN
    					SELECT  @SysUser = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID
    					SET @FullName = 'Admin for console'	
    				END
    
    			OPEN ColumnInfo
    			FETCH NEXT FROM ColumnInfo INTO @COLUMN_NAME, @DATA_TYPE
    			WHILE @@fetch_status = 0
    			BEGIN
    				SET @SQLTEXT = 'DECLARE @SEPARATOR AS VARCHAR(5);';
    				SET @SQLTEXT = @SQLTEXT + 'DECLARE @SEPARATORCOLUMN AS VARCHAR(5);';
    				SET @SQLTEXT = @SQLTEXT + 'DECLARE @COLUMN_VALUE AS nvarchar(Max);';
    				SET @SQLTEXT = @SQLTEXT + 'DECLARE @DATAFULLTEXT AS NVARCHAR(Max);';
    
    				SET @SQLTEXT = @SQLTEXT + 'SET @SEPARATOR = '':-:'';';	
    				SET @SQLTEXT = @SQLTEXT + 'SET @SEPARATORCOLUMN = ''::-::'';';
    				
    				SET @SQLTEXT = CONCAT(@SQLTEXT, N'SELECT @COLUMN_VALUE = Coalesce(CAST( ', @COLUMN_NAME, ' AS VARCHAR(Max)), '''') FROM ', 'paises ', ' WHERE id = 1 ;');
    				SET @SQLTEXT = CONCAT(@SQLTEXT, N' SET @DATAFULLTEXT = @DATAFULLTEXT + @SEPARATORCOLUMN +''', @COLUMN_NAME,''' + @SEPARATOR + @COLUMN_VALUE;');
    
    				EXECUTE sp_executesql @SQLTEXT;
    				
    				FETCH NEXT FROM ColumnInfo INTO @COLUMN_NAME, @DATA_TYPE
    			END
    			CLOSE ColumnInfo
    			DEALLOCATE ColumnInfo
    
    			INSERT  INTO dbo.AuditLogs ( SysUser, FullName, TableName, Action, OldValue, NewValue)
                        SELECT  @SysUser, @FullName, @TableName, 'INSERT', '', @DATAFULLTEXT FROM Inserted
            END
    GO
    • Dario Krapp dice:

      Hola Juan Carlos, buenos días

      Si tenés que reconocer lo que cambió para las tres acciones yo usaría un trigger INSTEAD OF que se ejecuta antes de la acción y te va a permitir conocer los valores viejos y nuevos de la entidad afectada y los vas a necesitar para el UPDATE y para el DELETE si querés guardar en el log los últimos valores que tenía la entidad antes de ser eliminada. Lo que complica la operación es que en este tipo de trigger vos vas a tener que hacer el insert, delete o update manualmente en el trigger.

      Si usas este tipo de trigger vas a tener en la tabla «Paises» los valores viejos y en inserted los valores nuevos en un update y vas a poder comparar los valores viejos contra los nuevos,
      Si querés hacerlo en forma genérica usando el esquema tenes un problema extra, no creo que puedas hacer un sp_executesql de «inserted» en forma directa, ya que no es una tabla, en cambio deberías pasar los valores a una tabla temporal de esta forma.

      SELECT * INTO #entidad FROM inserted

      Esta instruccion define la tabla temporal #entidad con las mismas columnas que inserted y le copia los valores, lo cual es ideal para para usarlo en forma genérica, ya que, si usas tablas variable no podés hacer esto, deberías declarar los campos manualmente de cada entidad y perdés la posibilidad de hacerlo genérico (luego recordá hacer un drop de la tabla temporal ya que vivirá en tu sesión hasta que la elimines)

      Haciéndolo así vas a tener en #entidad los valores nuevos y en la tabla «Paises where id=xxx» los valores viejos y vas a poder compararlos uno a uno y ver si son iguales o no basándose en el tipo de dato, (recordá que los valores podrían ser null, deberías manejar esos casos también)

      En altas o eliminación es todo más sencillo porque no hay que comparar.

      como último detalle, en el caso de Paises, que parece ser una tabla base no vas a tener problemas, pero en entidades más complejas seguramente vas a tener referencias a otras tablas a través de FK, por lo que guardar en el log el ID no creo que vaya a servir por que la entidad original no estar más en el futuro y no vas a poder saber que dato tenía, creo que en esos casos deberías ir a la tabla y campo referenciados, recuperar el valor y guardar eso en el log.

      Lo último de todo, en los triggers existe una función que se llama COLUMNS_UPDATED() que a través de bits enmascados te informan las columnas afectadas por una operación UPDATE, pero hay que tener cuidado con la misma, ya que no te indica que campos cambiaron, sino cuales fueron afectados por el update, que no es lo mismo.

      https://learn.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-ver16

      Espero que te ayude!
      Saludos

  43. Claudio Gabriel Depalma dice:

    Excelente artículo, muy bien explicado.

Deja un comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.