仅当列被修改时才触发 SQL 更新

通过查看其他例子，我得出了以下结论，但它似乎并不像我希望的那样工作: 我希望它只更新修改后的信息，如果 QtyToRepair值已经更新... 但它没有这样做。

如果我注释出在哪里，那么修改后的信息在每种情况下都会更新。正如我所说，其他的例子让我变得乐观。任何线索都可以。谢谢。

沃尔特

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
ON [dbo].[SCHEDULE]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;


UPDATE SCHEDULE SET modified = GETDATE()
, ModifiedUser = SUSER_NAME()
, ModifiedHost = HOST_NAME()
FROM SCHEDULE S
INNER JOIN Inserted I on S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
WHERE S.QtyToRepair <> I.QtyToRepair
END
398677 次浏览
小开

You want to do the following:

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
ON [dbo].[SCHEDULE]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;


IF (UPDATE(QtyToRepair))
BEGIN
UPDATE SCHEDULE SET modified = GETDATE()
, ModifiedUser = SUSER_NAME()
, ModifiedHost = HOST_NAME()
FROM SCHEDULE S
INNER JOIN Inserted I ON S.OrderNo = I.OrderNo AND S.PartNumber = I.PartNumber
WHERE S.QtyToRepair <> I.QtyToRepair
END
END

Please note that this trigger will fire each time you update the column no matter if the value is the same or not.

小开

One should check if QtyToRepair is updated at first. 

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
ON [dbo].[SCHEDULE]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE (QtyToRepair)
BEGIN
UPDATE SCHEDULE
SET modified = GETDATE()
, ModifiedUser = SUSER_NAME()
, ModifiedHost = HOST_NAME()
FROM SCHEDULE S INNER JOIN Inserted I
ON S.OrderNo = I.OrderNo and S.PartNumber =    I.PartNumber
WHERE S.QtyToRepair <> I.QtyToRepair
END
END
小开
最佳答案

You have two way for your question :

1- Use Update Command in your Trigger.

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
ON [dbo].[SCHEDULE]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;
IF UPDATE (QtyToRepair)
BEGIN
UPDATE SCHEDULE
SET modified = GETDATE()
, ModifiedUser = SUSER_NAME()
, ModifiedHost = HOST_NAME()
FROM SCHEDULE S INNER JOIN Inserted I
ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
WHERE S.QtyToRepair <> I.QtyToRepair
END
END

2- Use Join between Inserted table and deleted table

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
ON [dbo].[SCHEDULE]
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON;


UPDATE SCHEDULE
SET modified = GETDATE()
, ModifiedUser = SUSER_NAME()
, ModifiedHost = HOST_NAME()
FROM SCHEDULE S
INNER JOIN Inserted I ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
INNER JOIN Deleted D ON S.OrderNo = D.OrderNo and S.PartNumber = D.PartNumber
WHERE S.QtyToRepair <> I.QtyToRepair
AND D.QtyToRepair <> I.QtyToRepair
END

When you use update command for table SCHEDULE and Set QtyToRepair Column to new value, if new value equal to old value in one or multi row, solution 1 update all updated row in Schedule table but solution 2 update only schedule rows that old value not equal to new value.

小开

fyi The code I ended up with: 

IF UPDATE (QtyToRepair)
begin
INSERT INTO tmpQtyToRepairChanges (OrderNo, PartNumber, ModifiedDate, ModifiedUser, ModifiedHost, QtyToRepairOld, QtyToRepairNew)
SELECT S.OrderNo, S.PartNumber, GETDATE(), SUSER_NAME(), HOST_NAME(), D.QtyToRepair, I.QtyToRepair FROM SCHEDULE S
INNER JOIN Inserted I ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
INNER JOIN Deleted D ON S.OrderNo = D.OrderNo and S.PartNumber = D.PartNumber
WHERE I.QtyToRepair <> D.QtyToRepair
end
小开

Whenever a record has updated a record is "deleted". Here is my example:

ALTER TRIGGER [dbo].[UpdatePhyDate]
ON  [dbo].[M_ContractDT1]
AFTER UPDATE
AS
BEGIN
-- on ContarctDT1 PhyQty is updated
-- I want system date in Phytate automatically saved
SET NOCOUNT ON;


declare @dt1ky as int


if(update(Phyqty))
begin
select @dt1ky = dt1ky from deleted


update M_ContractDT1 set PhyDate=GETDATE() where Dt1Ky=  @dt1ky


end


END

It works fine


提交答案