SQL 同时操作多行记录时 触发器 问题
方法一
在delete触发器中可以使用deleted表(虚表),这个表有本次操作删除的所有记录,结构与原表一样。
在update触发器中可以使用inserted和deleted 2个表. 其中deleted有update前的记录数据,inserted有有update后的记录数据。
----
CREATE TABLE Class
(
ClassID int identity(1,1),
ClassName nvarchar(50)
)
CREATE TABLE Student
(
StudentID int identity(1,1),
StudentName nvarchar(50),
ClassID int
)
----
DECLARE @I INT
DECLARE @J INT
SET @I=1
WHILE @I<=10 BEGIN
INSERT INTO Class(ClassName) VALUES(CAST(@I AS nvarchar(50)))
SET @J=1
WHILE @J<=5 BEGIN
INSERT INTO Student(StudentName,ClassID) VALUES(CAST(@J AS nvarchar(50)),CAST(@I AS nvarchar(50)))
SET @J=@J+1
END
SET @I=@I+1
END
----
SELECT * FROM Class
SELECT * FROM Student
----
CREATE TRIGGER TDeleteStudents ON Class
FOR DELETE
AS
BEGIN TRAN Tran1
SAVE TRAN Point1
DELETE FROM Student WHERE ClassID IN (SELECT ClassID FROM deleted)
IF @@error <> 0
ROLLBACK TRAN Point1
COMMIT TRAN Tran1
GO
----TEST
DELETE FROM Class WHERE ClassID<=3
方法二
调用存储过程,使用游标代替触发器。例子如下:
/* 增/减文章,然后对发表用户加分/扣分,
如果是回复,还需要处理对应主题的回复数 */
ALTER PROCEDURE [dbo].[spSetScore]
@strOptions nvarchar(100)=NULL,
@strWhere nvarchar(2000)=NULL,
@TopicID int=NULL
AS
BEGIN
DECLARE @intTopicID int
DECLARE @intUserID int
DECLARE @intParentTopicID int
DECLARE @intPointAddMaster int
DECLARE @intPointAddReplay int
DECLARE @intPointDelMaster int
DECLARE @intPointDelReplay int
DECLARE @Sql nvarchar(2000)
/* 删除时 @strWhere传没有带'where'的条件*/
IF @strOptions='DEL' AND NOT @strWhere IS NULL AND @strWhere!='' BEGIN
SELECT @intPointDelMaster=Score FROM BBSRuleTypes WHERE RuleType='A'--删除主题分数
SELECT @intPointDelReplay=Score FROM BBSRuleTypes WHERE RuleType='R'--删除回复分数
/* 把删除的文章数据存储到临时表
SET @Sql=N'
SELECT
TopicID,
UserID,
ParentTopicID
INTO #tempTable
FROM BBSTopics WHERE ' + @strWhere
*/
CREATE TABLE #tempTable
(
TopicID int,
UserID int,
ParentTopicID int
)
SET @Sql=N'
INSERT INTO #tempTable
SELECT
TopicID,
UserID,
ParentTopicID
FROM BBSTopics WHERE ' + @strWhere
EXEC(@Sql)
--定义游标
DECLARE CoursorTopic SCROLL CURSOR FOR
SELECT TopicID, UserID, ParentTopicID FROM #tempTable FOR READ ONLY
OPEN CoursorTopic
FETCH FROM CoursorTopic INTO @intTopicID,@intUserID,@intParentTopicID
/* 通过游标循环从临时表中逐条取出数据进行处理 */
WHILE @@fetch_status=0 BEGIN
/*===============删除时 更新用户文章数量 /积分 / 等级 ================*/
IF(@intParentTopicID=0 OR @intParentTopicID IS NULL) BEGIN --没有父ID,即删除主题
UPDATE Users
SET TopicCount=TopicCount - 1,
Point=Point - @intPointDelMaster,
GradeID=(SELECT GradeID FROM BBSGrades WHERE DownLimit<=Point + @intPointDelMaster AND UpLimit>Point + @intPointDelMaster)
FROM Users
WHERE UserID=@intUserID
END
ELSE BEGIN --删除回复贴子
UPDATE Users
SET TopicCount=TopicCount - 1,
Point=Point - @intPointDelReplay,
GradeID=(SELECT GradeID FROM BBSGrades WHERE DownLimit<=Point + @intPointDelReplay AND UpLimit>Point + @intPointDelReplay)
FROM Users
WHERE UserID=@intUserID
--修改回复文章数
UPDATE BBSTopics SET ResponseCount = ResponseCount-1 WHERE TopicID = @intParentTopicID
END
/* 继续取数据 */
FETCH FROM CoursorTopic INTO @intTopicID,@intUserID,@intParentTopicID
END
CLOSE CoursorTopic
--删除临时表
DROP TABLE #tempTable
END
/* 添加时 @strWhere传TopicID */
IF @strOptions='ADD' AND (NOT @TopicID IS NULL AND @TopicID!=0) BEGIN
SELECT
@intTopicID=TopicID,
@intUserID=UserID,
@intParentTopicID=ParentTopicID
FROM BBSTopics
WHERE TopicID=@TopicID
/*===============更新时 更新用户文章数量 /积分 / 等级 ================*/
IF(@intParentTopicID=0 OR @intParentTopicID IS NULL) BEGIN --没有父ID,即发表新主题
--添加主题分数
SELECT @intPointAddMaster=Score FROM BBSRuleTypes WHERE RuleType='A'
UPDATE Users
SET TopicCount=TopicCount + 1,
Point=Point + @intPointAddMaster,
GradeID=(SELECT GradeID FROM BBSGrades WHERE DownLimit<=Point + @intPointAddMaster AND UpLimit>Point + @intPointAddMaster)
FROM Users
WHERE UserID=@intUserID
END
ELSE BEGIN --回复贴子
--添加回复分数
SELECT @intPointAddReplay=Score FROM BBSRuleTypes WHERE RuleType='R'
UPDATE Users
SET TopicCount=TopicCount + 1,
Point=Point + @intPointAddReplay,
GradeID=(SELECT GradeID FROM BBSGrades WHERE DownLimit<=Point + @intPointAddReplay AND UpLimit>Point + @intPointAddReplay)
FROM Users
WHERE UserID=@intUserID
--修改回贴数
UPDATE BBSTopics SET ResponseCount = ResponseCount+1 WHERE TopicID = @intParentTopicID
END
END
END
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)