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