有A、B、C三个表,表 A 结构如下:表A
userName nvarchar(20)
replyNumber int表B
bbsID int
lastUpdateTime datetime
lastUpdateUser nvarchar(20)
表C
replyID int
bbsID int
userName nvarchar(20)
addDate datetime
现在我想当表C的记录被删除时自动更新表B的lastUpdateTime和lastUpdateUser(select top 1 userName,addDate from 表C where bbsID=表B.bbsID order by addDate DESC)自动减少表A的replyNumber(减1)
userName nvarchar(20)
replyNumber int表B
bbsID int
lastUpdateTime datetime
lastUpdateUser nvarchar(20)
表C
replyID int
bbsID int
userName nvarchar(20)
addDate datetime
现在我想当表C的记录被删除时自动更新表B的lastUpdateTime和lastUpdateUser(select top 1 userName,addDate from 表C where bbsID=表B.bbsID order by addDate DESC)自动减少表A的replyNumber(减1)
for delete
as
update b set lastUpdateTime = (select top 1 addDate from C where bbsID<>(select bbsID from deleted) order by addDate DESC) where bbsID=(select bbsID from deleted)
update a set eplyNumber=eplyNumber-1 from a where a.userName=(select userName from deleted)
for delete
as
update t1 set t1.lastUpdateTime=t2.addDate from B t1,(select top 1 addDate from C where bbsID=t1.bbsID order by addDate DESC) t2
update t1 set t1.userName=t2.userName from B t1,(select top 1 userName from C where bbsID=t1.bbsID order by addDate DESC) t2
update A set replyNumber=replyNumber-1
go
for delete
as
update B
set lastUpdateTime = addDate,
lastUpdateUser =userName
from B,deleted d
where B.bbsID=d.bbsIDupdate A
set replyNumber = isnull(replyNumber,0) - 1
from A,deleted d
where A.userName=d.userName
for delete
as
update a set replynumber=replynumber-1 where username in (select username from deleted)
go
update b set lastupdatetime=getdate(),lastupdateuser=..... where bbsid in (select bbsid from deleted)
as
begin
declare @v1 int
delete from 表b where 表b.bbsID=deleted.bbsID and 表b.lastUpdateTime=deleted.addDate
update 表A set replyNumber=replyNumber-1
end
A,B,C三个表是通过会么关联的?
insert into A values('张三',2)
insert into A values('李四',3)
insert into A values('王五',5)insert into B values(1,'2007-3-21','张三')
insert into B values(2,'2007-4-21','李四')insert into C values(2,'王五','2007-1-6')
insert into C values(2,'李四','2007-4-1')
insert into C values(2,'王五','2007-4-5')
insert into C values(2,'张三','2007-4-9')
insert into C values(2,'王五','2007-4-13')
insert into C values(2,'李四','2007-4-21')insert into C values(1,'王五','2007-1-2')
insert into C values(1,'李四','2007-1-5')
insert into C values(1,'王五','2007-1-7')
insert into C values(1,'张三','2007-1-21')
go
create table A(userName varchar(10),replyNumber int)
insert into A values('张三',2)
insert into A values('李四',3)
insert into A values('王五',5)
create table B(bbsID int,lastUpdateTime datetime,lastUpdateUser varchar(10))
insert into B values(1,'2007-3-21','张三')
insert into B values(2,'2007-4-21','李四')
create table C(bbsID int,userName varchar(10),addDate datetime)
insert into C values(2,'王五','2007-1-6')
insert into C values(2,'李四','2007-4-1')
insert into C values(2,'王五','2007-4-5')
insert into C values(2,'张三','2007-4-9')
insert into C values(2,'王五','2007-4-13')
insert into C values(2,'李四','2007-4-21')
insert into C values(1,'王五','2007-1-2')
insert into C values(1,'李四','2007-1-5')
insert into C values(1,'王五','2007-1-7')
insert into C values(1,'张三','2007-1-21')create trigger td_C on C
for delete
as
update B
set lastUpdateTime = addDate,
lastUpdateUser =userName
from B,deleted d
where B.bbsID=d.bbsIDupdate A
set replyNumber = isnull(replyNumber,0) - 1
from A,deleted d
where A.userName=d.userNamedelete from C where bbsID=1 and userName='张三'select * from B
/*
bbsID lastUpdateTime lastUpdateUser
----------- ------------------------------------------------------ --------------
1 2007-01-21 00:00:00.000 张三
2 2007-04-21 00:00:00.000 李四(所影响的行数为 2 行)
*/
select * from A
/*
userName replyNumber
---------- -----------
张三 1
李四 3
王五 5(所影响的行数为 3 行)
*/
truncate table A
truncate table B
truncate table C
insert into A values('张三',2)
insert into A values('李四',3)
insert into A values('王五',5)insert into B values('2007-1-21','张三')
insert into B values('2007-4-21','李四')insert into C values(2,'王五','2007-1-6')
insert into C values(2,'李四','2007-4-1')
insert into C values(2,'王五','2007-4-5')
insert into C values(2,'张三','2007-4-9')
insert into C values(2,'王五','2007-4-13')
insert into C values(2,'李四','2007-4-21')insert into C values(1,'王五','2007-1-2')
insert into C values(1,'李四','2007-1-5')
insert into C values(1,'王五','2007-1-7')
insert into C values(1,'张三','2007-1-21')
*/--drop trigger td_C
/*
create trigger td_C on C
for delete
as
update B
set lastUpdateTime = addDate,
lastUpdateUser =userName
from B,deleted d
where B.bbsID=d.bbsIDupdate A
set replyNumber = isnull(replyNumber,0) - 1
from A,deleted d
where A.userName=d.userName
*/select * from B
delete from C where replyID=10
select * from B
1 2007-01-21 00:00:00 张三
2 2007-04-21 00:00:00 李四执行 delete from C where replyID=10 之后
1 2007-01-21 00:00:00 张三
2 2007-04-21 00:00:00 李四正确的结果应该为
1 2007-01-07 00:00:00 五五
2 2007-04-21 00:00:00 李四
而且delete from C where replyID=10,有重复值的话会删除多条的,应该再加条件and name=''
set lastUpdateTime = addDate,
lastUpdateUser =userName
from B,deleted d
where B.bbsID=d.bbsID这一段我觉得有点问题,应该问题就出在这里吧!
=============================================================================
Declare @lastUpdateUser nvarchar(20)
Declare @lastUpdateTime smalldatetime SET @lastUpdateTime=SELECT TOP 1 addTime FROM C WHERE bbsID=(SELECT bbsID FROM deleted) ORDER BY addTime DESC
SET @lastUpdateUser=SELECT TOP 1 userName FROM C WHERE bbsID=(SELECT bbsID FROM deleted) ORDER BY addTime DESC是不是应该这样写啊?这样写对不对啊?
表A
userName replyNumber
张三 2
李四 3
王五 5表B
bbsID lastUpdateTime lastUpdateUser
1 2007-01-21 张三
2 2007-04-21 李四表C
replyID bbsID userName addDate
1 2 王五 2007-1-6
2 2 李四 2007-4-1
3 2 王五 2007-4-5
4 2 张三 2007-4-9
5 2 王五 2007-4-13
6 2 李四 2007-4-21
7 1 王五 2007-1-2
8 1 李四 2007-1-5
9 1 王五 2007-1-7
10 1 张三 2007-1-21
deleted出现多条时,你写的代码就会报错.这样改试试
create trigger td_C on C
for delete
as
update B
set lastUpdateTime = deleted.addDate,
lastUpdateUser =deleted.userName
from B,(select d.bbsID,d.userName,max(d.addDate) as addDate from deleted d group by d.bbsID,d.userName)deleted
where B.bbsID=deleted.bbsIDupdate A
set replyNumber = isnull(replyNumber,0) - deleted.qty --如果删除同一姓名的多条记录就减几次
from A,(select d.userName,count(*) as qty from deleted d group by d.userName)deleted
where A.userName=deleted.userName
每一次操作只会触发一次啊?为什么deleted中为什么会出现多条呢?
这样就会触发多条.
没有触发多条是因为你的条件限制是一条了,但是SQL是允许对表进行多条删除的,所以你的代码就有可能出错.
这也是很多人对触发器临时表deleted的错误认识.
你可以将=号改为in
WHERE bbsID in (SELECT bbsID FROM deleted)
或用exists存在性判断
where exists(select 1 from deleted where deleted.bbsID=B.bbsID)
或用内联接,就像我上面的写法.
对于 replyID(标识,自动增长) 是唯一的
难道程序一定要写一个有bug的吗?
replyID现在是自增列不会重复,并不代表replyID永远都不会有重复的.
drop table C
go
create table C(replyID int identity(1,1),bbsID int,userName varchar(10),addDate datetime)
insert into C values(2,'王五','2007-1-6')
insert into C values(2,'李四','2007-4-1')set identity_insert C on
insert into C(replyID,bbsID,userName,addDate) values(2,2,'王五','2007-1-6')
set identity_insert C offselect * from C
/*
replyID bbsID userName addDate
----------- ----------- ---------- ------------------------------------------------------
1 2 王五 2007-01-06 00:00:00.000
2 2 李四 2007-04-01 00:00:00.000
2 2 王五 2007-01-06 00:00:00.000
(所影响的行数为 3 行)
*/delete from C where replyID=2
select * from C
/*
replyID bbsID userName addDate
----------- ----------- ---------- ------------------------------------------------------
1 2 王五 2007-01-06 00:00:00.000(所影响的行数为 1 行)
*/
delete from C where replyID=10
select * from B没有更新过来啊!
没有删除之前的结果
1 2007-01-21 00:00:00 张三
2 2007-04-21 00:00:00 李四执行 delete from C where replyID=10 之后
1 2007-01-21 00:00:00 张三
2 2007-04-21 00:00:00 李四我要的结果应该为
1 2007-01-07 00:00:00 五五
2 2007-04-21 00:00:00 李四
用户名 回复次数
张三 2
李四 3
王五 5表B
贴子ID(标识,自动增长) 最后回复时间 最后回复人
1 2007-01-21 张三
2 2007-04-21 李四表C
回复ID(标识,自动增长) 贴子ID 用户名 添加时间
1 2 王五 2007-1-6
2 2 李四 2007-4-1
3 2 王五 2007-4-5
4 2 张三 2007-4-9
5 2 王五 2007-4-13
6 2 李四 2007-4-21
7 1 王五 2007-1-2
8 1 李四 2007-1-5
9 1 王五 2007-1-7
10 1 张三 2007-1-21现在我们看 B 表,贴子ID为 1 的最后回复人是“张三”,最后回复时间是“2007-1-21”当删除表 C 中的 回复ID 为 10 这条记录后,我想更新表 B 中最后回复人和回复时间,此时表B中对应的贴子ID为 1 的最后回复人应该是“王五”,最后回复时间应该是 “2007-1-7”,而表 A 中用户为“张三”的回复次数应该减 1
for delete
as
update B
set lastUpdateTime = C.addDate,
lastUpdateUser =C.userName
from B,(select C.bbsID,C.userName,max(C.addDate) as addDate from C group by C.bbsID,C.userName)C
where B.bbsID=C.bbsID and C.bbsID in (select distinct bbsID from deleted)update A
set replyNumber = isnull(replyNumber,0) - deleted.qty
from A,(select d.userName,count(*) as qty from deleted d group by d.userName)deleted
where A.userName=deleted.userName