下面的触发器适用于单行更新，但在多行上失败。任何帮助表示赞赏。谢谢 [英] Below trigger works on single row update, but fails on multiple rows. Any help is appreciated. Thanks
问题描述
CREATE TRIGGER [dbo]。[trStudentlMealPlanUpdate]
ON [dbo]。[tblStudent]
FOR UPDATE
AS
BEGIN
IF @@ ROWCOUNT = 0返回;
设置NOCOUNT ON;
如果更新（lMealPlan）
BEGIN
DECLARE @NewValue INT，@ OldValue INT;
SET @NewValue =（SELECT lMealPlan FROM Inserted）;
SET @OldValue =（SELECT lMealPlan FROM Deleted）;
IF @NewValue<> @OldValue
BEGIN
INSERT INTO tblMealPlanChange（dIDNumber，lMealPlan，lOldMealPlan，lMealsThisPeriod，lOldMealsThisPeriod，dtDate）
VALUES（
（SELECT DIDNumber FROM Inserted），
（SELECT lMealPlan FROM Inserted），
（SELECT lMealPlan FROM Deleted），
（SELECT lMealsThisPeriod FROM Inserted），
（SELECT lMealsThisPeriod FROM Deleted），
GETDATE（）
）;
RETURN ;
结束
结束
结束
GO
我尝试过：
CREATE TRIGGER [dbo]。[trStudentlMealPlanUpdate]
ON [dbo]。[tblStudent]
FOR UPDATE
AS
BEGIN
IF @@ ROWCOUNT = 0返回;
设置NOCOUNT ON;
如果更新（lMealPlan）
BEGIN
DECLARE @NewValue INT，@ OldValue INT;
SET @NewValue =（SELECT lMealPlan FROM Inserted）;
SET @OldValue =（SELECT lMealPlan FROM Deleted）;
IF @NewValue<> @OldValue
BEGIN
INSERT INTO tblMealPlanChange（dIDNumber，lMealPlan，lOldMealPlan，lMealsThisPeriod，lOldMealsThisPeriod，dtDate）
VALUES（
（SELECT DIDNumber FROM Inserted），
（SELECT lMealPlan FROM Inserted），
（SELECT lMealPlan FROM Deleted），
（SELECT lMealsThisPeriod FROM Inserted），
（SELECT lMealsThisPeriod FROM Deleted），
GETDATE（）
）;
RETURN ;
结束
结束
结束
GO
CREATE TRIGGER [dbo].[trStudentlMealPlanUpdate]
ON [dbo].[tblStudent]
FOR UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF UPDATE(lMealPlan)
BEGIN
DECLARE @NewValue INT, @OldValue INT;
SET @NewValue = (SELECT lMealPlan FROM Inserted);
SET @OldValue = (SELECT lMealPlan FROM Deleted);
IF @NewValue <> @OldValue
BEGIN
INSERT INTO tblMealPlanChange (dIDNumber, lMealPlan, lOldMealPlan, lMealsThisPeriod, lOldMealsThisPeriod, dtDate)
VALUES(
(SELECT dIDNumber FROM Inserted),
(SELECT lMealPlan FROM Inserted),
(SELECT lMealPlan FROM Deleted),
(SELECT lMealsThisPeriod FROM Inserted),
(SELECT lMealsThisPeriod FROM Deleted),
GETDATE()
);
RETURN;
END
END
END
GO
What I have tried:
CREATE TRIGGER [dbo].[trStudentlMealPlanUpdate]
ON [dbo].[tblStudent]
FOR UPDATE
AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
IF UPDATE(lMealPlan)
BEGIN
DECLARE @NewValue INT, @OldValue INT;
SET @NewValue = (SELECT lMealPlan FROM Inserted);
SET @OldValue = (SELECT lMealPlan FROM Deleted);
IF @NewValue <> @OldValue
BEGIN
INSERT INTO tblMealPlanChange (dIDNumber, lMealPlan, lOldMealPlan, lMealsThisPeriod, lOldMealsThisPeriod, dtDate)
VALUES(
(SELECT dIDNumber FROM Inserted),
(SELECT lMealPlan FROM Inserted),
(SELECT lMealPlan FROM Deleted),
(SELECT lMealsThisPeriod FROM Inserted),
(SELECT lMealsThisPeriod FROM Deleted),
GETDATE()
);
RETURN;
END
END
END
GO
推荐答案
你需要在Inserted / Deleted上创建一个游标并逐步浏览
you need to create a cursor on the Inserted/Deleted and walk through that
这篇关于下面的触发器适用于单行更新，但在多行上失败。任何帮助表示赞赏。谢谢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
