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

Question

IF Columns_Updated()&@Column=@Column不能准确测到更新的列，因为如果update tablename set col1=col1 ....
col1实际没有改变，但是IF Columns_Updated()&@Column=@Column返回true建议：
用
from inserted i,deleted d
where i.id=d.id
and i.colname<>d.colname
来判断这个列是否改变

Answer 1

To :CCEO(),liujianjun
我只关心真正发生改变的列，而并不考虑以下情况。
  如果update tablename set col1=col1
同时，请各位留意我的要求。

Answer 2

使用 COLUMNS_UPDATED
下例创建两个表：一个 employeeData 表和一个 auditEmployeeData 表。人力资源部的成员可以修改 employeeData 表，该表包含敏感的雇员薪水信息。如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户，则生成审核记录并插入到 auditEmployeeData 审核表。通过使用 COLUMNS_UPDATED() 功能，可以快速测试对这些包含敏感雇员信息的列所做的更改。只有在试图检测对表中的前 8 列所做的更改时，COLUMNS_UPDATED() 才起作用。USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'employeeData')
   DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'auditEmployeeData')
   DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
   emp_id int NOT NULL,
   emp_bankAccountNumber char (10) NOT NULL,
   emp_salary int NOT NULL,
   emp_SSN char (11) NOT NULL,
   emp_lname nchar (32) NOT NULL,
   emp_fname nchar (32) NOT NULL,
   emp_manager int NOT NULL
   )
GO
CREATE TABLE auditEmployeeData (
   audit_log_id uniqueidentifier DEFAULT NEWID(),
   audit_log_type char (3) NOT NULL,
   audit_emp_id int NOT NULL,
   audit_emp_bankAccountNumber char (10) NULL,
   audit_emp_salary int NULL,
   audit_emp_SSN char (11) NULL,
   audit_user sysname DEFAULT SUSER_SNAME(),
   audit_changed datetime DEFAULT GETDATE()
   )
GO
CREATE TRIGGER updEmployeeData 
ON employeeData 
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/   IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
      BEGIN
-- Audit OLD record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'OLD', 
            del.emp_id,
            del.emp_bankAccountNumber,
            del.emp_salary,
            del.emp_SSN
         FROM deleted del-- Audit NEW record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'NEW',
            ins.emp_id,
            ins.emp_bankAccountNumber,
            ins.emp_salary,
            ins.emp_SSN
         FROM inserted ins
   END
GO/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
   VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeData
   SET emp_salary = 51000
   WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/UPDATE employeeData
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
   WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO

Answer 3

To:enhydraboy()
   这段帮助我已经看懂了。
   现在的问题是，不能准确地记录被修改的字段。
   示例：
源表<带触发器的表>
ID   Code          Caption          FNAME
1    100N       测试终于成功!    测试终于成功!
2    dfddf      sl4jkhddf        更新未成功????,
3    100N1      优化触发器       优化触发器
（所影响的行数为 3 行）
对源表ID=2的Caption值更改，得到如下记录
Id   TblName     FldName  OldValue   NewValue   OprTime            
14   ModifyTest      ID        2       2       2003-02-13 16:24:48.077
15   ModifyTest    Code      dfddf      dfddf  2003-02-13 16:24:48.077
16   ModifyTest  Caption  sl454ddf  sl4jkhddf  2003-02-13 16:24:48.077
17   ModifyTest    NULL   sl454ddf  sl4jkhddf  2003-02-13 16:24:48.077
（所影响的行数为 4 行）
我想要的是仅记录
16   ModifyTest  Caption  sl454ddf  sl4jkhddf  2003-02-13 16:24:48.077
即可<况其它记录内容也是错的>。CREATE TRIGGER [Data_Update] ON [dbo].[ModifyTest] 
After UPDATE
AS
If @@Rowcount=0 Return
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')) a
-------以下代码更严谨<否则:表字段有删除情况时,colID 值并不连续,就可能导致后续代码不能正确执行>
Create Table #TabInf(colID Int,FldName varchar(100))
Insert Into #TabInf(colID,FldName)
  Select a.colID,a.FldName
  From (Select colID,Name As FldName From SysColumns Where ID=OBJECT_ID('ModifyTest')) a
Set @CntColumn=@@RowcountDeclare @SqlStr nVarchar(3000)------Length(@SqlStr)<=4000
----Get Old and New field values
Select * Into #T1 From Deleted
Select * Into #T2 From Inserted
----
Declare @ColumnsValues Int
Set @ColumnsValues=0
Set @Column=1If  @CntColumn<=8
Begin
  While @Column<=@CntColumn
  Begin
     Set @ColumnsValues=@ColumnsValues+Power(2,(@Column-1))-----Power(2,(2-1))+Power(2,(3-1))+Power(2,(4-1))+Power(2,(5-1))+Power(2,(6-1))+Power(2,(7-1))+Power(2,(8-1))
     Set @Column=@Column+1
  End
End
Else
Begin
  Set @ColumnsValues=255------检测前8列值是否改变
  Set @Column=1
  While @Column<=@CntColumn-8
  Begin
--   IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))+ power(2,(5-1))) 
--      AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
--      ) 
     Set @ColumnsValues=@ColumnsValues+Power(2,(@Column-1))
     Set @Column=@Column+1
  End
End
Print @ColumnsValues
----Loop through columns
Set @Column=1
Declare @colStr varchar(200)
Set @colStr=''
While @Column<=@CntColumn
Begin
---  IF (Columns_Updated()&@Column)>@Column
  IF @CntColumn<=8
    Set @colStr='(Columns_Updated()&@ColumnsValues)>0'
  Else
    Set @colStr='((SubString(Columns_Updated(),1,1) >0) and ((SubString(Columns_Updated(),2,1) >0)'
Begin
    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())
  End
  Set @Column=@Column+1
End
Drop Table #T1
Drop Table #T2
Drop Table #TabInf

Answer 4

呵呵！
学习中，关注ing！

Answer 5

IF UPDATE (column)测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。若要测试在多个列上进行的 INSERT 或 UPDATE 操作，请在第一个操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中 IF UPDATE 将返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。说明  IF UPDATE (column) 子句的功能等同于 IF、IF...ELSE 或 WHILE 语句，并且可以使用 BEGIN...END 语句块。有关更多信息，请参见控制流语言。 
可以在触发器主体中的任意位置使用 UPDATE (column)。IF (COLUMNS_UPDATED()) 测试是否插入或更新了提及的列，仅用于 INSERT 或 UPDATE 触发器中。COLUMNS_UPDATED 返回 varbinary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED 函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含 8 列以上，则 COLUMNS_UPDATED 返回多个字节，最左边的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。可以在触发器主体中的任意位置使用 COLUMNS_UPDATED。刚看到的啊！
学习学习学习中！

Answer 6

To:flyinger(琉璃翡翠) 
   多谢关注！
   关于在线帮助我已查阅，就请不必贴上来----好象Csdn对问题的总长度有限制。

Answer 7

http://expert.csdn.net/Expert/topic/1440/1440696.xml?temp=.3912928