自己写的sql server触发器练练--高手请您跳过吧

时间:2022-03-06 02:29:26

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [insertReplyToic]
ON [dbo].[bbsReplyTopic]
AFTER insert
AS
BEGIN
--SET NOCOUNT ON;

-- Insert statements for trigger here

declare @uid int,@topicId int,@Rcontent nvarchar(max),@Rtime datetime,@ccode varchar(max)
,@uname nvarchar(50),@beReplyedUid int,@beReplyedUname nvarchar(50),@replyTid int,@insertId int;
insert into bbsReplyTopic values(
@uid,@topicId,@Rcontent,@Rtime,@ccode,@uname,@beReplyedUid,@beReplyedUname,@replyTid
) select @insertId=@@identity
if(@insertId!=0)

select @topicId=topicId from inserted
update bbsTopic set replyCount=replyCount+1 where topicId=@topicId
print '您刚刚插入的id是'+convert(varchar(10),@insertId)+',success'

END

insert into bbsReplyTopic values(125,291,'test','2014/10/6 00:00:00','chadao','zhangziyi',125,'zhangziyi',0)

自己写的sql server触发器,当回帖帖子被回复时,帖子的回复数量字段就加1

测试成功!

后来,我自己想了想,如果我要实现我插入一条数据的话,与这个表关联的另一个表也相应地更新一下,那么我该怎么做,后来参考下资料,发觉如果我照上面去做的话,是实现不了的,所以我把上面的改了,如下:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [insertReplyToic]
ON [dbo].[bbsReplyTopic]
AFTER insert
AS
BEGIN

declare @uid int,@topicId int,@Rcontent nvarchar(max),@Rtime datetime,@ccode varchar(max)
,@uname nvarchar(50),@beReplyedUid int,@beReplyedUname nvarchar(50),@replyTid int,@insertId int;

select @insertId=@@identity
select @topicId=topicId from inserted
update bbsTopic set replyCount=replyCount+1 where topicId=@topicId
print '您刚刚插入的id是'+convert(varchar(10),@insertId)+',success'

END

这样,也就实现我要的功能,嘿嘿!