在触发器中是否可以提取到被修改列的列名?

Question

我需要建立一个日志表,用来记录数据库中各表触发改动时其被修改的列名和值,不知道是否能实现,各位高手给点意见了。

Answer 1

结合系统表可以不过首先要搞清楚你需要登记什么，是内容改变，还是在set中出现
比如update 语句是：update test set a=1,b=b where ...，这里a字段作了修改，记录没问题,b字段在set中出现，但内容未改变，是否登记

Answer 2

deleted修改前的
inserted修改后的

Answer 3

可以实现，要用到数据申计,AUDIT,网上有动态的AUDIT
主要是用触发器实现的

Answer 4

假设日志表结构如下：id  列名  改前值（varchar） 改后值（varchar） 改时间--可以不结合系统表
create trigger tr_test_update
on test
for update
as
IF ( COLUMNS_UPDATED() & 1 = 1 )
begin
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'表的第一列名' as 列名,d.表的第一列名,i.表的第一列名,getdate()
    from inserted i,deleted d
    where i.id=d.id
end
IF ( COLUMNS_UPDATED() & 2 = 2 )
begin
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'表的第二列名' as 列名,d.表的第二列名,i.表的第二列名,getdate()
    from inserted i,deleted d
    where i.id=d.id
end
IF ( COLUMNS_UPDATED() & 4 = 4 )
begin
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'表的第三列名' as 列名,d.表的第三列名,i.表的第三列名,getdate()
    from inserted i,deleted d
    where i.id=d.id
end
IF ( COLUMNS_UPDATED() & 8 = 8 )
begin
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'表的第四列名' as 列名,d.表的第四列名,i.表的第四列名,getdate()
    from inserted i,deleted d
    where i.id=d.id
end
...               --根据字段数写多个go
--结合系统表
create trigger tr_test_update
on test
for update
as
declare @sql varchar(8000）IF ( COLUMNS_UPDATED() & 1 = 1 )
begin
    select @sql='
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'''+name''' as 列名,d.'+name+',i.'+name+',getdate()
    from inserted i,deleted d
    where i.id=d.id'
    from syscolumns 
    where id=object_id('test') and colorder=1
    exec(@sql)
end
IF ( COLUMNS_UPDATED() & 2 = 2 )
begin
    select @sql='
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'''+name''' as 列名,d.'+name+',i.'+name+',getdate()
    from inserted i,deleted d
    where i.id=d.id'
    from syscolumns 
    where id=object_id('test') and colorder=2
    exec(@sql)
end
IF ( COLUMNS_UPDATED() & 4 = 4 )
begin
    select @sql='
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'''+name''' as 列名,d.'+name+',i.'+name+',getdate()
    from inserted i,deleted d
    where i.id=d.id'
    from syscolumns 
    where id=object_id('test') and colorder=3
    exec(@sql)
end
IF ( COLUMNS_UPDATED() & 8 = 8 )
begin
    select @sql='
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'''+name''' as 列名,d.'+name+',i.'+name+',getdate()
    from inserted i,deleted d
    where i.id=d.id'
    from syscolumns 
    where id=object_id('test') and colorder=4
    exec(@sql)
end
...               --根据字段数写多个go

Answer 5

create table oper_log(tabel_name varchar(20),   --修改表名
                      oper_type  char(1),       --修改类型，新增／修改／删除
                      column_name varchar(20),  --修改列名
                      new_value varchar(150),   --新值
                      old_value varchar(150),　 --旧值
                      update_time datetime);    --修改时间
create trigger on table_name
for insert,update,delete
...
在每个需要记录操作日志的表上建触发器在数据变更时将操作类型＼时间＼新值＼旧值＼列名等信息插入到定义好的日志表

Answer 6

这段SQL不对啊，多出了from  where ,楼上少些了吧。
    insert 日志表(id,列名,改前值,改后值,改时间)
    select i.id,'''+name''' as 列名,d.'+name+',i.'+name+',getdate()
    from inserted i,deleted d
    where i.id=d.id'
    from syscolumns 
    where id=object_id('test') and colorder=2