录入A、B表对比，如果记录数据完全相同，就插入C表，但为什么不能处理空值的情况?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
ALTER TRIGGER [CHECK_A] ON [DBO].[A]
FOR INSERT,UPDATE
AS
DECLARE @I INT ---审核状态 1:审核通过 0:审核不通过 -1:未审核
SET ANSI_NULLS OFF
-----------------------添加数据-------------------------
IF NOT EXISTS(SELECT 1 FROM B WHERE ID=(SELECT ID FROM INSERTED))
SET @I=-1
IF EXISTS(SELECT 1 FROM B WHERE ID=(SELECT ID FROM INSERTED) AND T1=(SELECT T1 FROM INSERTED) AND T2=(SELECT T2 FROM INSERTED))
SET @I=1
IF EXISTS(SELECT 1 FROM B WHERE ID=(SELECT ID FROM INSERTED))
AND NOT EXISTS(SELECT 1 FROM B WHERE ID=(SELECT ID FROM INSERTED) AND T1=(SELECT T1 FROM INSERTED) AND T2=(SELECT T2 FROM INSERTED))
SET @I=0
IF @I=1
BEGIN
INSERT INTO T(ID,T1,T2)
SELECT ID,T1,T2 FROM INSERTED
ALTER TABLE [DBO].[A] DISABLE TRIGGER ALL
ALTER TABLE [DBO].[B] DISABLE TRIGGER ALL
UPDATE A SET checkstat=1 WHERE ID=(SELECT ID FROM INSERTED)
UPDATE B SET checkstat=1 WHERE ID=(SELECT ID FROM INSERTED)
ALTER TABLE [DBO].[A] ENABLE TRIGGER ALL
ALTER TABLE [DBO].[B] ENABLE TRIGGER ALL
END
IF @I=0
BEGIN
ALTER TABLE [DBO].[A] DISABLE TRIGGER ALL
ALTER TABLE [DBO].[B] DISABLE TRIGGER ALL
UPDATE A SET checkstat=0 WHERE ID=(SELECT ID FROM INSERTED)
UPDATE B SET checkstat=0 WHERE ID=(SELECT ID FROM INSERTED)
ALTER TABLE [DBO].[A] ENABLE TRIGGER ALL
ALTER TABLE [DBO].[B] ENABLE TRIGGER ALL
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------
上面是A表的触发器，B表中的触发器与此类似，如果A、B表中ID相同的记录，其对应字段的值完全相同，就插入C表，可是当A、B表对应字段都是NULL时，就无法处理了，已经在触发器前加了SET ANSI_NULLS OFF 语句，为什么还是不能判断呢？
[解决办法]
这触发器在一次操作多条记录的时候肯定有问题
[解决办法]
ALTER TRIGGER [CHECK_A] ON [DBO].[A]
FOR INSERT,UPDATE
AS
INSERT INTO T(ID,T1,T2)
SELECT ID,T1,T2 FROM INSERTED i,b
where i.id=b.id
and (i.T1=b.T1 or i.T1 is null and b.T1 is null)
and (i.T2=b.T2 or i.T2 is null and b.T2 is null)
ALTER TABLE [DBO].[A] DISABLE TRIGGER ALL
ALTER TABLE [DBO].[B] DISABLE TRIGGER ALL
UPDATE A SET checkstat=case when (i.T1=b.T1 or i.T1 is null and b.T1 is null)
and (i.T2=b.T2 or i.T2 is null and b.T2 is null)
then 1
else 0
end
from a,INSERTED i,b
WHERE a.ID=i.id and i.id=b.id
UPDATE B SET checkstat=case when (i.T1=b.T1 or i.T1 is null and b.T1 is null)
and (i.T2=b.T2 or i.T2 is null and b.T2 is null)
then 1
else 0
end
from INSERTED i,b
WHERE i.id=b.id
ALTER TABLE [DBO].[A] ENABLE TRIGGER ALL
ALTER TABLE [DBO].[B] ENABLE TRIGGER ALL
go
[解决办法]
一次处理多条记录的时候,你的语句中
where ID=(SELECT ID FROM INSERTED)
由于子查询(SELECT ID FROM INSERTED)返回多条记录而报错
结果是事务失败回滚
[解决办法]
Haiwer(海阔天空) 说得没错,一次处理多条记录的时候,你的语句中
where ID=(SELECT ID FROM INSERTED)就会报错.
如:
insert a select 1, 'df ', 'dfds ',1
union all select 1,null, 'dfds ',1
用Haiwer(海阔天空) 写的trigger可以避免这个问题.但建议将每个判断条件:
i.T1=b.T1 or i.T1 is null and b.T1 is null
改为:i.T1=b.T1 or (i.T1 is null and b.T1 is null)
或者:isnull(i.T1, ' ')=isnull(b.t1, ' ')
[解决办法]
不好意思,刚才弄清了一个问题:
i.T1=b.T1 or i.T1 is null and b.T1 is null
的执行顺序是先与后或,所以不加括号也可以.
Haiwer(海阔天空)的写法没有问题
