CODEWITHSUNDEEP
sql-servert-sqltriggers
Khesteg
Khesteg

Reputation: 33

Logging data changes into table with dynamically changing name in MS SQL

I am trying to log data changes in MS SQL with trigger. I want to create a new History table in every month. After I found the answer how to change table name Dynamically I can't access the DELETED and INSERTED tables anymore. It says invalid object name.

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

Thanks for the answers in advance.

Upvotes: 1

Views: 237

Answers (4)

Daniel Stawicki
Daniel Stawicki

Reputation: 1

If you have SQL Server enterprise (check your version) Then better way will be to enable CDC.

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

Upvotes: 0

Khesteg
Khesteg

Reputation: 33

Tanks for the answer @MtwStark. Now it works, I can check if the table exists, and create it if not. And have eaccess to the DELETED and INSERTED tables. I'm not sure, if in your solution I have to create the test_history_9999_99 table in advance. Because when I used your trigger I've got an error about column insertion (I didn't understand the error completly).

Now my code looks like this. I'm not sure if it can handle INSERT/UPDATE many records with a single operation. Probably I still need to insert this code for it? CAST(@guid AS varchar(64)) BATCH . I'm not sure what it really does, I have to look into it deeper.

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

Upvotes: 1

MtwStark
MtwStark

Reputation: 4058

Dynamic sql is executed in his own scope, so you can't acces inserted/deleted objects.

You could write a SQLCLR trigger in C# look this example SQLCLR Trigger but I think the easiest way is to use a temp table to write changes to, so the dynamic part is fixed.

Take a look:

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

My test_table contains only two columns ID and [Desc].
In the history tables I have added a DATETIME column with change date and a UNIQUEIDENTIFIER column so you can group all changes in a batch if you INSERT/UPDATE many records with a single operation

Upvotes: 1

Tdubs
Tdubs

Reputation: 29

Try refreshing intellisense. Ctrl+Shift+R see if that might help. Or do a database table refresh.

Upvotes: 0

Related Questions