Estoy tratando de registrar cambios de datos en MS SQL con el disparador. Quiero crear una nueva tabla de historial cada mes. Después de encontrar la respuesta sobre cómo cambiar el nombre de la tabla dinámicamente, ya no puedo acceder a las tablas ELIMINADAS e INSERTADAS. Dice un nombre de objeto no válido.

ALTER TRIGGER [dbo].[teszttablatrigger] ON [teszt].[dbo].[teszt] FOR DELETE, INSERT, UPDATE AS

declare @hist nvarchar(40)
set @hist='teszthistory_' + CAST(YEAR(getdate()) as NCHAR(4))+ '_' + (case when Month(GETDATE())<10 then '0' + CAST (Month(GETDATE()) as NCHAR(1))
                                                                            when Month(GETDATE())>=10 then CAST (Month(GETDATE()) as NCHAR(2)) end)

declare @DynamicSql1 nvarchar(2000)
declare @DynamicSql2 nvarchar(2000)

set @DynamicSql1 = N'IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N''[History][dbo].[@hist]'')
AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
CREATE TABLE [History].[dbo].[@hist] ( kulcs int, szoveg varchar(40), modtip varchar(40), datum datetime default getdate())'

Exec sp_executesql @DynamicSql1, N'@hist nvarchar(40)', @hist=@hist

set @DynamicSql2 = N'INSERT INTO [History].[dbo].[@hist] (kulcs, szoveg, modtip)
SELECT kulcs, szoveg, ''delete''
  FROM DELETED

INSERT INTO [History].[dbo].[@hist] (kulcs, szoveg, modtip)
SELECT kulcs, szoveg, ''insert''
  FROM INSERTED'

Exec sp_executesql @DynamicSql2, N'@hist nvarchar(40)', @hist=@hist

Gracias por las respuestas de antemano.

1
Khesteg 12 sep. 2016 a las 17:14

4 respuestas

La mejor respuesta

El sql dinámico se ejecuta en su propio ámbito, por lo que no puede acceder a los objetos insertados / eliminados.

Podría escribir un disparador SQLCLR en C # mire este ejemplo SQLCLR Activador pero creo que la forma más fácil es usar una tabla temporal para escribir cambios, por lo que la parte dinámica es fija.

Echar un vistazo:

DROP TRIGGER [test_history] 
GO

CREATE TRIGGER [test_history] ON [test_table] 
FOR DELETE, INSERT, UPDATE 
AS
BEGIN
    declare @date datetime = getdate()  
    declare @guid uniqueidentifier = newid()
    declare @hist nvarchar(40)= 'test_history_' + CAST(YEAR(@date ) as VARCHAR(4))+ '_' + right('0' + CAST(Month(@date) as VARCHAR(2)), 2)

    DECLARE @T1 BIT = 0
    SELECT top 1 @T1 = 1 FROM sys.tables WHERE [TYPE] = 'U' AND name = 'test_history_9999_99' 
    IF @T1 = 1 TRUNCATE table test_history_9999_99          

    DECLARE @T2 BIT = 0
    SELECT top 1 @T2 = 1 FROM sys.tables WHERE [TYPE] = 'U' AND name = @hist

    IF @T1=0 BEGIN

        SELECT ID, [desc], @date DATE_TIME, cast('delete' as varchar(20)) as operation, CAST(@guid AS varchar(64)) BATCH
        INTO test_history_9999_99 
        FROM DELETED

    END else begin

        INSERT INTO test_history_9999_99 
        SELECT ID, [desc], @date, cast('delete' as varchar(20)) as operation, CAST(@guid AS varchar(64)) BATCH
        FROM DELETED

    end

    INSERT INTO test_history_9999_99 
    SELECT ID, [desc], @date, cast('insert' as varchar(20)) as operation, CAST(@guid AS varchar(64)) BATCH
    FROM inserted

    IF @T2 = 0 BEGIN
        EXEC sp_rename 'test_history_9999_99', @hist
    END ELSE BEGIN
        declare @DynamicSql nvarchar(2000)
        SET @DynamicSql = 'INSERT INTO ' + @hist + ' SELECT * FROM test_history_9999_99;'

        Exec sp_executesql @DynamicSql
    END
END

Mi tabla_de_prueba contiene solo dos columnas ID y [Desc].
En las tablas del historial, agregué una columna DATETIME con la fecha de cambio y una columna UNIQUEIDENTIFIER para que pueda agrupar todos los cambios en un lote si INSERT/UPDATE muchos registros con una sola operación

1
Community 13 abr. 2017 a las 12:42

Si tiene una empresa de SQL Server (verifique su versión), la mejor manera será habilitar CDC.

https://msdn.microsoft.com/en-us/library/cc645937(v=sql.110).aspx

0
Daniel Stawicki 14 sep. 2016 a las 09:41

Intente refrescar intellisense. Ctrl + Shift + R mira si eso puede ayudar. O actualice la tabla de la base de datos.

0
Tdubs 12 sep. 2016 a las 14:56

Tanques para la respuesta @MtwStark. Ahora funciona, puedo comprobar si la tabla existe y crearla si no. Y tener acceso a las tablas DELETED e INSERTED. No estoy seguro, si en su solución tengo que crear la tabla test_history_9999_99 por adelantado. Porque cuando usé su disparador, recibí un error sobre la inserción de columnas (no entendí completamente el error).

Ahora mi código se ve así. No estoy seguro de si puede manejar INSERT / UPDATE muchos registros con una sola operación. ¿Probablemente todavía necesito insertar este código? CAST (@guid AS varchar (64)) LOTE. No estoy seguro de lo que realmente hace, tengo que investigarlo más a fondo.

CREATE TRIGGER [dbo].[teszttablatrigger] ON [teszt].[dbo].[teszt] FOR DELETE, INSERT, UPDATE AS

declare @hist nvarchar(40)
set @hist='teszthistory_' + CAST(YEAR(getdate()) as NCHAR(4))+ '_' + (case when Month(GETDATE())<10 then '0' + CAST (Month(GETDATE()) as NCHAR(1))
                                                                            when Month(GETDATE())>=10 then CAST (Month(GETDATE()) as NCHAR(2)) end)

select * into #ins from inserted
select * into #del from deleted

declare @DynamicSql nvarchar(2000)

DECLARE @T2 BIT = 0
    SELECT top 1 @T2 = 1 FROM sys.tables WHERE [TYPE] = 'U' AND name = @hist

if @T2=0 begin
    set @DynamicSql = N'CREATE TABLE [' + @hist + '] ( kulcs int, szoveg varchar(40), modtip varchar(40), datum datetime default getdate())'
    Exec sp_executesql @DynamicSql
end

set @DynamicSql = N'INSERT INTO ' + @hist + ' (kulcs, szoveg, modtip)
SELECT kulcs, szoveg, ''delete''
  FROM #del

INSERT INTO ' + @hist + ' (kulcs, szoveg, modtip)
SELECT kulcs, szoveg, ''insert''
  FROM #ins'

Exec sp_executesql @DynamicSql
1
Khesteg 13 sep. 2016 a las 09:51