请教大家, update触发器能否判断出某个字段值是否改变?

初用触发器,  想检查REGISTOR表中某字段A是否改变来决定是否向另一表B插入一纪录,  
我写了如下触发器,  但效果好像不对,  请教大家,  哪里出了问题?  
 
单用if  update(a),  也体现不出效果,  为什么?  

create  trigger  ***  on  registor  
           for  update  
as  
if  update(***)  
begin  
         .................  
end  
else  
if  update(A)  AND  
     (SELECT  COUNT(*)  FROM  DELETED    D  INNER  JOIN  INSERTED  I  ON  D.ID=I.ID AND  D.a=I.a  )>0  
begin  
           insert  b(channel_id,card_id,card_type,serv_id,add_id,b_date,e_date,flag)  
                 SELECT  r.channel_id,  r.devc_id,  r.devc_type,  p.serv_id,r.add_id,  p.b_time,  p.e_time,'5'  
                       FROM  registor  r  INNER  JOIN  inserted  i  ON  r.id  =  i.id  
                             JOIN  payer  p  ON  p.cus_id  =  r.id  
end  

16 个解决方案

#1

update()函数实际并不会告诉用户某列数据是否发生了改变，而是只确定某列是否将会被改变。
下面是update( )函数返回一个真值true的情况：
1。该列在UPDATE语句中SET短语的左边。
2。该列在INSERT语句的插入字段列表中。

#2

update()函数,要注意

create table t(aid int,bid int)
go
insert into t values(2,2)
go
create trigger mytr on t instead of update as
if update(bid)
print '呵呵,bid的值没有改,但执行了update操作!'
go
update t 
set bid = 2
where aid = 2

#3

Chiff,刚在你的贴子里面打竹板呢，呵呵。

楼主如果想达到判断值是否改变的效果，可以考虑在表里加一个字段，LastValueofA,或者AChange?(bit) 这样就可以了

#4

谢谢大家, 
用if(select i.a from inserted i join deleted d on i.id=d.id)<> (select i.a from inserted i join deleted d on i.id=d.id)
来判断能好像能实现, 不知是否科学? 

#5

我觉得knowlittle的方法不太好, 还有其他方法吗?

#6

使用 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

F. 使用 COLUMNS_UPDATED 测试 8 列以上
如果必须测试影响到表中前 8 列以外的列的更新时，必须使用 UBSTRING 函数测试由 COLUMNS_UPDATED 返回的适当的位。下例测试影响 Northwind.dbo.Customers 表中的第 3、第 5 或第 9 列的更新。

USE Northwind
DROP TRIGGER  tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
   IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
      + power(2,(5-1))) 
      AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
      ) 
   PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers 
   SET ContactName=ContactName,
      Address=Address,
      Country=Country
GO

#7

COLUMNS_UPDATED()好像体现不出是否有具体变化啊?
leisa说的对.

#8

是呀！你肯定不能看具体的变化，只有通过系统临时表：
deleted 存储旧数据
inserted 存储新数据

create  trigger  ***  on  registor  
  for  update  
insert 旧数据表 select * from deleted
insert 新数据表 select * from inserted

#9

触发器在数据库里是存放在哪里的？谁知道

#10

你看这样行不行？
declare @oldA varchar(30),@newA varchar(30)

--取变化前的值
select @oldA=A from deleted
--取变化后的值
select @newA=A from inserted

if @oldA<>@newA
begin
......
end

/*
这个办法比较笨，不过可能是最直接的了
*/

#11

从deleted 表中读出旧数据
从inserted 表中读出新数据
然后比较

#12

对阿, 我现在就这么写的.只是有一个问题,
如:leisa这样:

declare @oldA varchar(30),@newA varchar(30)
--取变化前的值
select @oldA=A from deleted
--取变化后的值
select @newA=A from inserted

if @oldA<>@newA
begin
......
end

使用了变量,不知如同时更新了多条纪录, 能不能把多条纪录都处理了?

#13

不行
因为即使你一次更新20条记录，触发器也还是一条一条触发的

#14

也就是说, 我这样写不会漏掉数据. 全都处理了?

由于是一条一条触发, 所以每条删除的纪录都经过了检测.
如一次更新了20条, 则if ****<>*****,分别执行了20次, 如果这样, 我就没有问题了.

#15

偶来学习，：）

ps:
这样做当然是可以的，
但是我认为效率不会高

我非常同意一位星星大哥的观点，（忘了谁了，海阔天空还是j老板，不好意思，呵呵）良好的设计才是关键

#16

给学习同志押阵：
是Haiwer(海阔天空)老大，"好的设计才有好的效率"

好像有点跑题。

不过看看是有好处的。原贴：
http://expert.csdn.net/Expert/topic/1321/1321682.xml?temp=.4093744