触发器中如何取出不定列的列值？Trigger问题

Question

declare @sql_str varchar(255)
select @sql_str='Declare @NewValue Varchar(100)'+
'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)'+ 'From Inserted'
exec(@sql_str)

Answer 1

To:Cabo Thanks
  问题是:Inserted 是原表在插入、更改时产生的虚拟表。
  因此：
'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)'+ 'From Inserted'
Sql会说：对象名 Inserted 无效.

Answer 2

To:Cabo 
是的
其实我要做的是对基础资料表的数据更改做记录。
相关源码如下：
CREATE TRIGGER [ModifyUpdate] ON [dbo].[ModifyTest] 
After Insert,UPDATE
AS
Set Nocount Off
Declare @CntColumn Int,@Column Int,@OldValue Varchar(1000),@NewValue Varchar(1000)
----Count Columns in the table
Select @CntColumn=Count(a.FldName)
From (Select Name As FldName
      From SysColumns Where ID=OBJECT_ID('ModifyTest')) a
Set @Column=1
/*
declare @sql_str varchar(255)
select @sql_str='Declare @NewValue Varchar(100)'+
'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From Inserted'
exec(@sql_str)
*/
----Loop through columns
/*
While @Column<=@CntColumn
Begin
----以下两句没有返回预期值----指定列的值,而是指列的列名
--  Select @OldValue=Col_Name(OBJECT_ID('ModifyTest'),@Column) From Deleted
--  Select @NewValue=Col_Name(OBJECT_ID('ModifyTest'),@Column) From Inserted
  If Columns_Updated()&@Column=@Column
  Begin
     Insert Into ModifyHistory(TblName,FldName,OldValue,NewValue,OprTime)
       Values('ModifyTest',Col_Name(OBJECT_ID('ModifyTest'),@Column),IsNull(@OldValue,''),IsNull(@NewValue,''),GetDate())
----     Print 'Column ('+Cast(@Column as Varchar)+')'+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' has been changed!'
  End
  Set @Column=@Column+1
End
*/

Answer 3

刚才试了一下，的确如此，
要不，你就先把INSERTED中的数据放进临时表吧alter TRIGGER [ModifyUpdate] ON [dbo].[a] 
After Insert,UPDATE
AS
select * into #a from inserted
declare @sql_str varchar(255)
select @sql_str='Declare @NewValue Varchar(100) '+
'Select @NewValue='+Col_Name(OBJECT_ID('a'),1)+ ' From #a select @newvalue '
exec(@sql_str)这样写，我调试过
可能还有更好的方法

Answer 4

To:Cabo
   Thanks!
我将：
select @sql_str='Declare @NewValue Varchar(100) '+
'Select @NewValue='+Col_Name(OBJECT_ID('a'),1)+ ' From #a select @newvalue '
exec(@sql_str)
不能得到正确的返回值，将是Null
改为：
Set @sql_str=N'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),1)+' From #T1'
Exec sp_ExecuteSql @sql_str,N'@NewValue Varchar(100) Output,@Column Int,@Inserted Varchar(20)',@NewValue Output,@Column,@Inserted
能得到列值，但问题是
('ModifyTest'),1)中的参数，需要由变量设定，事先并不知道应是第几列，
但当把1换成变量时就只能得到列名了。
还会不会有什么好方法呢？
总之，多谢你的解答。
期待各路高手参与。

Answer 5

To:Cabo
   已实现Insert触发器，但Update好象还不行。
将实现代码贴出，供兄弟们参考、指点
CREATE TRIGGER [Data_Insert] ON dbo.ModifyTest 
After Insert，Update
AS
Set Nocount Off
Declare @CntColumn Int,@Column Int,@OldValue Varchar(1000),@NewValue Varchar(1000)
----Count Columns in the table
Select @CntColumn=Count(a.FldName)
From (Select Name As FldName
      From SysColumns Where ID=OBJECT_ID('ModifyTest')) a
Set @Column=1
----Get Old and New field value
Declare @SqlStr nVarchar(3000)------Length(@SqlStr)<=4000Select * Into #T1 From Deleted
Set @SqlStr=N'Select @OldValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From #T1'
Exec sp_ExecuteSql @SqlStr,N'@OldValue Varchar(1000) Output',@OldValue Output
Select * Into #T2 From Inserted
Set @SqlStr=N'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From #T2'
Exec sp_ExecuteSql @SqlStr,N'@NewValue Varchar(1000) Output',@NewValue Output
----Loop through columns
While @Column<=@CntColumn
Begin
  IF Columns_Updated()&@Column=@Column
  Begin
     Insert Into ModifyHistory(TblName,FldName,OldValue,NewValue,OprTime)
       Values('ModifyTest',Col_Name(OBJECT_ID('ModifyTest'),@Column),IsNull(@OldValue,''),IsNull(@NewValue,''),GetDate())
----     Print 'Column ('+Cast(@Column as Varchar)+')'+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' has been changed!'
  End
  Set @Column=@Column+1
  Set @SqlStr=N'Select @OldValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From #T1'
  Exec sp_ExecuteSql @SqlStr,N'@OldValue Varchar(1000) Output',@OldValue Output
  Set @SqlStr=N'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From #T2'
  Exec sp_ExecuteSql @SqlStr,N'@NewValue Varchar(1000) Output',@NewValue Output
End
Drop Table #T1
Drop Table #T2

Answer 6

更新操作时：
  IF Columns_Updated()&@Column=@Column
怎么会没有检测到有更新列？
源码如下：
CREATE TRIGGER [Data_Update] ON [dbo].[ModifyTest] 
After UPDATE
AS
Set Nocount ON
Declare @CntColumn Int,@Column Int,@OldValue Varchar(1000),@NewValue Varchar(1000)
----Count Columns in the table
Select @CntColumn=Count(a.FldName)
From (Select Name As FldName From SysColumns Where ID=OBJECT_ID('ModifyTest')) aDeclare @SqlStr nVarchar(3000)------Length(@SqlStr)<=4000
----Get Old and New field values
Select * Into #T1 From Deleted
Select * Into #T2 From Inserted
----Loop through columns
Set @Column=1
While @Column<=@CntColumn
Begin
  IF Columns_Updated()&@Column=@Column
  Begin
    Print 'Update!'
    Set @SqlStr=N'Select @OldValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From #T1'
    Exec sp_ExecuteSql @SqlStr,N'@OldValue Varchar(1000) Output',@OldValue Output
    Set @SqlStr=N'Select @NewValue='+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' From #T2'
    Exec sp_ExecuteSql @SqlStr,N'@NewValue Varchar(1000) Output',@NewValue Output    Insert Into ModifyHistory(TblName,FldName,OldValue,NewValue,OprTime)
       Values('ModifyTest',Col_Name(OBJECT_ID('ModifyTest'),@Column),IsNull(@OldValue,''),IsNull(@NewValue,''),GetDate())
     Print 'Column ('+Cast(@Column as Varchar)+')'+Col_Name(OBJECT_ID('ModifyTest'),@Column)+' has been changed!'
  End
  Set @Column=@Column+1
End
Drop Table #T1
Drop Table #T2