******高手进来帮我看看，为什么我的触发器在批量更新时，触发器的代码只影响了更新的最后一行？？？

Question

我的触发器代码：
CREATE Trigger EM_CheckCycleMonitor on EM_EquipmentArchives
AFTER Insert,Update
AS
IF Update(CheckCycle) OR Update(CheckDate) OR Update(CheckDepartment) OR Update(LicenseDepartment)
BEGIN
  Declare @NextCheckDate  as DateTime,
 @EquipmentID as int,
 @CheckDate_old as DateTime,
 @CheckDepartment_old as Varchar(100),
 @LicenceDepartment_old as Varchar(100),
 @CheckCycle_old as Varchar(20),
 @NextCheckDate_old as DateTime,
 @CheckCycle_new as Varchar(20),
 @CheckDate_new as DateTime Select @CheckDate_old = CheckDate,@CheckDepartment_old = CheckDepartment,@LicenceDepartment_old = LicenseDepartment, @CheckCycle_old = CheckCycle from Deleted
Select @CheckDate_new = CheckDate, @CheckCycle_new = CheckCycle, @EquipmentID = EquipmentID from Inserted ---这里打印出来的结果只有最后一条
         print @CheckDate_new
print @CheckCycle_new
print @EquipmentID ---备份原来的标准检定信息
Insert Into EM_StandardCheckRecord(EquipmentID,Check_Org,GOV_NO,CheckDate,NextCheckDate,CheckCycle) values(@EquipmentID,@CheckDepartment_old,@LicenceDepartment_old,@CheckDate_old,@NextCheckDate_old,@CheckCycle_old) --自动计算NextCheckDate，根据检定周期和检定日期
Set @NextCheckDate = CTS.dbo.CalcNextCheckDate(@CheckCycle_new, @CheckDate_new) ---更新计算出来的下一个检定日期
         Update EM_EquipmentArchives Set NextCheckDate = @NextCheckDate Where EquipmentID = @EquipmentID
        END---当我执行下面的语句时，触发器只更新了在EM_EquipmentArchives中EM_CheckReport记录集中的最后一条,Select EquipmentID from EM_CheckReport where ReportKind = 1 语句得到7条数据，7条数据全部都可以和EM_EquipmentArchives关联。Update EM_EquipmentArchives Set CheckDate = NextCheckDate Where EquipmentID in (Select EquipmentID from EM_CheckReport where ReportKind = 1)

Answer 1

Select @CheckDate_old = CheckDate,@CheckDepartment_old = CheckDepartment,@LicenceDepartment_old = LicenseDepartment, @CheckCycle_old = CheckCycle from Deleted
Select @CheckDate_new = CheckDate, @CheckCycle_new = CheckCycle, @EquipmentID = EquipmentID from Inserted
这样赋值是只赋值了一行.你要定义个游标,逐行处理.

Answer 2

给你一个我的例子，（批量加入每日车次后触发批量加入每日车次对应的座位）
CREATE TRIGGER [TRIGGER_InsertBusOrderDaily] ON dbo.TWangs_BusOrderDailyInfo 
FOR INSERT
AS
DECLARE @FDateTime DATETIME,
                  @FBusOrderID VARCHAR(50),
                  @FSeating INT,
                  @FSeatingFlag INT,
                  @FSeatingID INT
DECLARE Inserted_Cursor CURSOR FOR
SELECT FDateTime, FBusOrderID, FBusOrderSeating, FSeatingFlag = 0  FROM Inserted 
OPEN  Inserted_Cursor
FETCH NEXT FROM Inserted_Cursor
INTO  @FDateTime, @FBusOrderID, @FSeating ,@FSeatingFlag
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @FSeatingID = 1
   WHILE @FSeatingID <= @FSeating 
   BEGIN
     INSERT TWangs_BusOrderSeatingDailyInfo(FDateTime, FBusOrderID, FSeatingID, FSeatingFlag) VALUES(@FDateTime, @FBusOrderID, @FSeatingID, @FSeatingFlag)
     SET @FSeatingID = @FSeatingID + 1
   END
   FETCH NEXT FROM Inserted_Cursor
   INTO  @FDateTime, @FBusOrderID, @FSeating ,@FSeatingFlag
END
CLOSE Inserted_Cursor
DEALLOCATE Inserted_Cursor