Reading inserted column names and values in a TSQL trigger

I've been asked to create history tables for every table in a database. Then create a trigger that will write to the history table whenever the primary table is updated.

开发者_JAVA技巧

The history tables have the same structure as the primary table, but with a couple of extra rows ('id' and 'update type')

I've never done anything with triggers before, but I would like to do is dynamically go through the columns in 'Inserted' and construct an insert statement to populate the history table.

However I cannot work out how to read the names of the columns and their individual values.

My half finished trigger currently looks like...

CREATE TRIGGER tr_address_history
ON address
FOR UPDATE
AS

DECLARE @colCount int
DECLARE @maxCols int
SET @colCount = 0
SET @maxCols = (SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted')

PRINT 'Number of columns = ' + CONVERT(varChar(10),@maxCols)
WHILE (@colCount <= @maxCols)
BEGIN
    DECLARE @name varchar(255)
    SELECT @name = column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'
    DECLARE @value varchar(255)
    SELECT @value = @name FROM Inserted

    PRINT 'name = ' + @name + ' and value = ' + @value
    SET @colCount = @colCount + 1
END
PRINT 'Done';

When the trigger runs it just says "Number of columns = 0"

Can anyone tell me what's wrong with :

SELECT COUNT(column_name) FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'Inserted'

Thanks...

---

First solution proposed by Beenay25 is good, but you should use affected table instead of 'inserted' pseudotable.

This is:

SELECT @name = column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'AFFECTED_TABLE'

Instead of 'INSERTED'

Also, you should use dynamic SQL.

This will be a complete working solution:

ALTER TRIGGER [dbo].[tr_address_history]
ON [dbo].[address]
AFTER Insert
AS

DECLARE @ColumnName nvarchar(500)
DECLARE @TableName nvarchar(500)
DECLARE @value nvarchar(500)
DECLARE @Sql nvarchar(500)

Set @TableName='address'

DECLARE ColumnsCursor CURSOR FOR
select column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'address'

OPEN ColumnsCursor
FETCH NEXT FROM ColumnsCursor into @ColumnName

WHILE @@FETCH_STATUS=0
BEGIN 

      select * into #tmp from inserted
      Set @Sql= 'SELECT @value =' + @ColumnName + ' FROM #tmp'

      EXEC sp_executesql @Sql, N'@Value nvarchar(500) OUTPUT', @Value OUTPUT

      DROP TABLE #TMP

      print '[' + @ColumnName +'='+ ltrim(rtrim(@Value))+']'

      FETCH NEXT FROM ColumnsCursor into @ColumnName
END   

CLOSE ColumnsCursor
DEALLOCATE ColumnsCursor

---

The 'inserted' table is a pseudo-table; it doesn't appear in INFORMATION_SCHEMA.

There is the UPDATE() operator for use in triggers:

CREATE TRIGGER trigger_name ON tablename
FOR UPDATE
AS
SET NOCOUNT ON
IF (UPDATE(Column1) OR UPDATE(Column2))
BEGIN
  your sql here
END

COLUMNS_UPDATED

UPDATE()

---

There is a way to do what the questioner requires:

I have made something inside a trigger that tests whether all the columns of a particular table actually participated in an insert to that table. If they did, I later copied them to a history table. If they did not, then rollback and print only complete rows may be inserted into the report table. Perhaps they could adapt this to their needs:

here it is:

    [

if exists (select 1 from inserted) and not exists (select 1 from deleted) -- if an insert has been performed
begin -- and we want to test whether all the columns in the report table were included in the insert
declare @inserted_columncount int, @actual_num_of_columns int, @loop_columns int, @current_columnname nvarchar(300),
    @sql_test nvarchar(max), @params nvarchar(max), @is_there bit
set @actual_num_of_columns = (
    select count(*) from (
    select COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'renameFilesFromTable_report') as z)
set @inserted_columncount = 0
set @loop_columns = 1
declare inserted_columnnames cursor scroll for -- these are not really the inserted ones, but we are going to test them 1 by 1
    select COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'renameFilesFromTable_report'
set @params = '@is_there_in bit output'
open inserted_columnnames
fetch next from inserted_columnnames into @current_columnname
select * into #temp_for_dynamic_sql from inserted -- this is necessary because the scope of sp_executesql does not include inserted pseudo table
while (@loop_columns <= @actual_num_of_columns) -- looping with independent integer arithmetic
begin
set @sql_test = '
set @is_there_in = 0
if exists (select ['+@current_columnname+'] from #temp_for_dynamic_sql where ['+@current_columnname+'] is not null)
set @is_there_in = 1'
exec sp_executesql @sql_test, @params, @is_there output
if @is_there = 1
begin
fetch next from inserted_columnnames into @current_columnname
set @inserted_columncount = @inserted_columncount + 1
set @loop_columns = @loop_columns + 1
end
else if @is_there <> 1
begin
fetch next from inserted_columnnames into @current_columnname
set @loop_columns = @loop_columns + 1
end
end 
close inserted_columnnames
deallocate inserted_columnnames
-- at this point we hold in two int variables the number of columns participating in the insert and the total number of columns

    ]

Then you can simply do if @inserted_columncount < @actual_num_of_columns ..........

I did this because i have a sp that inserts 1 complete line to the report table every time it runs. That's fine, but i don't want anyone else touching that table by mistake. not even myself. I also want to keep history. So i made this trigger to keep the history but also to check if an insert was attempted without values for all the columns in the report table, and further down the code it checks if an update or delete was attempted and it rollbacks.

i was thinking of expanding this to allow an update but in which all the columns are set. this could possibly be done as follows:

if update was attempted,

and exists (
select possibly_excluded.COLUMN_NAME from (
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'renameFilesFromTable_report') as possibly_excluded
group by possibly_excluded.COLUMN_NAME
having COLUMN_NAME not in (
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'renameFilesFromTable_report' and
sys.fn_IsBitSetInBitmask(@ColumnsUpdated, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) <> 0)
)
begin
rollback transaction
print 'Only updates that set the values for a complete row are allowed on the report table..'
end