By looking at other examples I've come up with the following but it doesn't seem to work as I would like: I want it to only update the modified information if the QtyToRepair
value has been updated... but it doesn't do that.
If I comment out the where then the modified information is updated in every case. As I said other examples led me to be optimistic. Any clues appreciated. Thanks.
Walter
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
6条答案
按热度按时间jgzswidk1#
You have two way for your question :
1- Use Update Command in your Trigger.
2- Use Join between Inserted table and deleted table
When you use update command for table
SCHEDULE
and SetQtyToRepair
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.nhhxz33t2#
fyi The code I ended up with:
5us2dqdw3#
One should check if
QtyToRepair
is updated at first.1mrurvl14#
You want to do the following:
Please note that this trigger will fire each time you update the column no matter if the value is the same or not.
qgzx9mmu5#
Whenever a record has updated a record is "deleted". Here is my example:
It works fine
kyvafyod6#
'after update' means the table has the inserted data in it when this trigger fires.
Option 2 from the accepted answer is the best route, with one correction in the where clause - not equal to PREVIOUS (deleted) value.
accepted answer's option 2, with correction: