IF (SELECT COUNT(*) FROM DELETED)>0
BEGIN
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
END
---------------------------------------------------/*
The problem is in this part. When you update table, it effects table "deleted".
revise it as:
*/if update(字段1) or update(字段2)
update B set b.t1=a.字段1,b.t2=a.字段2 from deleted d,a where b.t1=d.字段1 and a.字段1=b.t1 and b.t3 is not null
else if (SELECT COUNT(*) FROM DELETED)>0
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
BEGIN
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
END
---------------------------------------------------/*
The problem is in this part. When you update table, it effects table "deleted".
revise it as:
*/if update(字段1) or update(字段2)
update B set b.t1=a.字段1,b.t2=a.字段2 from deleted d,a where b.t1=d.字段1 and a.字段1=b.t1 and b.t3 is not null
else if (SELECT COUNT(*) FROM DELETED)>0
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
你的意思是改 DELETED 的这一段,有条件的性的删除
我把 IF (SELECT COUNT(*) FROM DELETED)>0
BEGIN
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
END替换了你那一段
if update(字段1) or update(字段2)
update B set b.t1=a.字段1,b.t2=a.字段2 from deleted d,a where b.t1=d.字段1 and a.字段1=b.t1 and b.t3 is not null
else if (SELECT COUNT(*) FROM DELETED)>0
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)结果:
更新的列都会触发INSER 产生一列在B表
if update(字段1) or update(字段2)
update B set b.t1=a.字段1,b.t2=a.字段2 from deleted d,a where b.t1=d.字段1 and a.字段1=b.t1 and b.t3 is not null
else if (SELECT COUNT(*) FROM DELETED)>0
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
else if (SELECT COUNT(*) FROM INSERTED)>0
BEGIN
INSERT
B(t1,t2,t3)
SELECT
t1=字段1,t2=字段2,t3=NULL
FROM INSERTED
END
主要判断就成了 updated
执行UPDTE 时是有效的,
但当A表插入 删除列时,B列不会执行插入删除
希望大虾们能指点小弟了SET NOCOUNT ON 什么意思啊!
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。语法
SET NOCOUNT { ON | OFF }注释
当 SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。当 SET NOCOUNT 为 OFF 时,返回计数。即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。当 SET NOCOUNT 为 ON 时,将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时,在 Transact-SQL 语句(如 SELECT、INSERT、UPDATE 和 DELETE)结束时将不会在查询结果中显示"nn rows affected"。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。权限
SET NOCOUNT 权限默认授予所有用户。示例
下例在 osql 实用工具或 SQL Server 查询分析器中执行时,可防止显示有关受影响的行数的信息。USE pubs
GO
-- Display the count message.
SELECT au_lname
FROM authors
GO
USE pubs
GO
-- SET NOCOUNT to ON and no longer display the count message.
SET NOCOUNT ON
GO
SELECT au_lname
FROM authors
GO
-- Reset SET NOCOUNT to OFF.
SET NOCOUNT OFF
GO
CREATE TRIGGER TR_AA ON A
FOR INSERT, UPDATE, DELETE
AS
beginSET NOCOUNT ONIF not exists(SELECT 1 FROM DELETED)
BEGIN
INSERT B(t1,t2,t3) SELECT t1=字段1,t2=字段2,t3=NULL FROM INSERTED
ENDIF not exists (SELECT 1 FROM INSERTED)
BEGIN
DELETE B WHERE T1 IN (SELECT 字段1 FROM DELETED)
ENDif exists(select 1 from deleted ) and exists(select 1 from inserted)
begin
update b set b.t1=i.字段1 , b.t2=i.字段1 from b ,inserted i,deleted d
where b.T1=d.字段1 end
SET NOCOUNT off
end
关联 也就是 A表delete B表就delete
A表update B表就UPDATE
A表insert B表就INSER
我的原句出的问题就是 当A表UPDATE 时,B表执行的是 先DELETE B表列 再INSERT 列
这样就导致 我的B表T3 的字段总是为NULL希望大家帮我想想办法咯
同时满足 DELETE 和INSERT 的触发
谢谢!
你写的是对的~ 问题解决了