问题一: create trigger mytrigger for insert,update,delete as udpate thread set replycount = thread.replycount + b.cnt from (select threadid,cnt=sum(cnt) from (select threadid,cnt=1 from inserted union all select threadid,cnt = -1 from deleted) as a group by threadid) as b
呵呵!Chiff(~o~) 大虾真快呀!
pengdali(大力) : 呵呵,过奖过奖。
to: Chiff1.如果用户一次更新了多条记录怎么办?? 且多条记录不是在同一个主题之下怎么办??
to: Chiff 请帮我看下,下面的 语句为何不对?? 我本来想得到的是每次不论更新或是删除时,都用 count(*)的方式 重新得到该thread的reply_count,但为何得到的数值是总的message数量再加上个1呢?? 不解! 实在是不明白啊!!CREATE TRIGGER [trg_bbs_message_ins] ON [dbo].[BBS_Message] FOR INSERT,UPDATE,DELETE ASupdate bbs_thread set reply_count=(select count(*) from bbs_message inner join inserted on bbs_thread.threadid=inserted.threadid)-1 where bbs_thread.threadid = (select threadid from inserted group by threadid)
select count(*) from bbs_message inner join inserted on bbs_thread.threadid=inserted.threadid得到的是什么?where bbs_thread.threadid = (select threadid from inserted group by threadid) 也有问题,当同时更新多条的时候。
按照你的思路,这样写,但只适于每次更新一条: CREATE TRIGGER [trg_bbs_message_ins] ON [dbo].[BBS_Message] FOR INSERT,UPDATE,DELETE ASupdate bbs_thread set reply_count=(select count(*) from bbs_message inner join inserted on bbs_thread.threadid=inserted.threadid) where bbs_thread.threadid = (select threadid from inserted group by threadid)(把“-1”去掉)
to: Chiff 谢谢你的热心解答, 我现在先只是考虑用户每次更新一条的情况。 你说将"-1"去掉,我试了下也不对啊。 我手动删除bbs_message表中的一条记录,结果bbs_thread表中 对应的贴子的reply_count的值变为了bbs_message表中记录数的总和,这是怎么回事呢? 我不知道是哪里出了错,会不会是因为没有装 SP2呢?
删除: CREATE TRIGGER [trg_Delete] ON thread FOR DELETE AS BEGIN DECLARE @ID NVARCHAR(20) SELECT @ID = Thread FROM Deleted DELETE FROM Message WHERE message.ThreadID = @ThreadID END修改: CREATE TRIGGER [trg_Update] ON thread FOR Update AS if update(ThreadID) BEGIN DECLARE @newID NVARCHAR(20) DECLARE @oldID NVARCHAR(20) SELECT @oldID = Thread FROM Deleted SELECT @newID = Thread FROM Inserted update Message set threadid= @newid WHERE message.ThreadID = @oldID END 增加: CREATE TRIGGER [trg_Insert] ON thread FOR Insert AS BEGIN DECLARE @ID NVARCHAR(20) SELECT @ID = Thread FROM Inserted insert into Message values(@id) WHERE message.ThreadID = @ID END
感谢大家,我最后的写法如下: 但我想知道的是如果是针对多条记录触发的话,@newid或是 @oldid的值是什么呢?怎样才可以调试呢? CREATE TRIGGER [trg_bbs_message_ins] ON [dbo].[BBS_Message] FOR INSERT,UPDATE, DELETE ASdeclare @oldid int declare @newid int select @oldid=threadid from deleted select @newid=threadid from insertedif @newid >0 begin update bbs_thread set reply_count=(select count(*) from bbs_message where bbs_message.threadid=@newid) where bbs_thread.threadid=@newid endif @oldid >0 begin update bbs_thread set reply_count=(select count(*) from bbs_message where bbs_message.threadid=@oldid) where bbs_thread.threadid=@oldid end
create trigger mytrigger for insert,update,delete as
udpate thread
set replycount = thread.replycount + b.cnt
from
(select threadid,cnt=sum(cnt)
from (select threadid,cnt=1 from inserted
union all
select threadid,cnt = -1 from deleted) as a
group by threadid) as b
呵呵,过奖过奖。
且多条记录不是在同一个主题之下怎么办??
另,我按照你的办法去做后,我手动更新数据库中一条记录后,
我发现replycount变成了-1, 它的初始值为0.
这是怎么回事???
如果说给用户增加一个将贴子分类的功能,
那么用户使用这个功能时,(程序修改了某个message的threadid),
那么贴子数是会更改的。
我很感谢大家关注这个问题,谢谢。
且多条记录不是在同一个主题之下怎么办??”--没问题,只要更新threadid在thread表存在就可以。
“ 另,我按照你的办法去做后,我手动更新数据库中一条记录后,
我发现replycount变成了-1, 它的初始值为0.
这是怎么回事???”
--首先要保证初始值是正确的。其实不能更新threadid列。
问题三:
如你的意思,更改threadid列就可以了。但要保证threadid必须存在于thread表
请帮我看下,下面的 语句为何不对??
我本来想得到的是每次不论更新或是删除时,都用 count(*)的方式
重新得到该thread的reply_count,但为何得到的数值是总的message数量再加上个1呢??
不解! 实在是不明白啊!!CREATE TRIGGER [trg_bbs_message_ins] ON [dbo].[BBS_Message]
FOR INSERT,UPDATE,DELETE ASupdate bbs_thread set reply_count=(select count(*) from bbs_message inner join inserted on bbs_thread.threadid=inserted.threadid)-1
where bbs_thread.threadid = (select threadid from inserted group by threadid)
from bbs_message inner join inserted
on bbs_thread.threadid=inserted.threadid得到的是什么?where bbs_thread.threadid =
(select threadid from inserted group by threadid)
也有问题,当同时更新多条的时候。
CREATE TRIGGER [trg_bbs_message_ins] ON [dbo].[BBS_Message]
FOR INSERT,UPDATE,DELETE ASupdate bbs_thread set reply_count=(select count(*) from bbs_message inner join inserted on bbs_thread.threadid=inserted.threadid)
where bbs_thread.threadid = (select threadid from inserted group by threadid)(把“-1”去掉)
谢谢你的热心解答,
我现在先只是考虑用户每次更新一条的情况。
你说将"-1"去掉,我试了下也不对啊。
我手动删除bbs_message表中的一条记录,结果bbs_thread表中
对应的贴子的reply_count的值变为了bbs_message表中记录数的总和,这是怎么回事呢? 我不知道是哪里出了错,会不会是因为没有装
SP2呢?
CREATE TRIGGER [trg_Delete] ON thread
FOR DELETE
AS
BEGIN
DECLARE @ID NVARCHAR(20)
SELECT @ID = Thread FROM Deleted
DELETE FROM Message WHERE message.ThreadID = @ThreadID END修改:
CREATE TRIGGER [trg_Update] ON thread
FOR Update
AS
if update(ThreadID)
BEGIN
DECLARE @newID NVARCHAR(20)
DECLARE @oldID NVARCHAR(20)
SELECT @oldID = Thread FROM Deleted
SELECT @newID = Thread FROM Inserted
update Message set threadid= @newid
WHERE message.ThreadID = @oldID
END
增加:
CREATE TRIGGER [trg_Insert] ON thread
FOR Insert
AS BEGIN
DECLARE @ID NVARCHAR(20)
SELECT @ID = Thread FROM Inserted
insert into Message values(@id)
WHERE message.ThreadID = @ID
END
to:jakenIT
你可不可以帮我看看我写的trigger出了什么问题,好吗?
谢谢了。
但我想知道的是如果是针对多条记录触发的话,@newid或是
@oldid的值是什么呢?怎样才可以调试呢?
CREATE TRIGGER [trg_bbs_message_ins] ON [dbo].[BBS_Message]
FOR INSERT,UPDATE, DELETE ASdeclare @oldid int
declare @newid int
select @oldid=threadid from deleted
select @newid=threadid from insertedif @newid >0
begin
update bbs_thread set reply_count=(select count(*) from bbs_message where bbs_message.threadid=@newid) where bbs_thread.threadid=@newid
endif @oldid >0
begin
update bbs_thread set reply_count=(select count(*) from bbs_message where bbs_message.threadid=@oldid) where bbs_thread.threadid=@oldid
end