Menu
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 
564
In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.
AFTER UPDATE Trigger is a kind of trigger in SQL that will be automatically fired once the specified update statement is executed. It can be used for creating audit and log files which keep details of last update operations on a particular table.
Using a SQL Server trigger to check if a column is updated, there are two ways this can be done; one is to use the function update(<col name>) and the other is to use columns_updated().
As mentioned earlier, the UPDATE() function can only be used within a trigger. If the purpose of using the UPDATE() function is to determine if any rows were updated in a table after an UPDATE statement, then the @@ROWCOUNT function can be used instead.
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.
159
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With