CREATE TRIGGER dbo.tri_Cancelled
ON Orders
FOR UPDATE
AS
  --objective: set the cancel date to GETDATE if the CANC value <>0 
  BEGIN
    UPDATE Orders
    SET    CancDate = GETDATE()
    FROM   INSERTED
    WHERE  Orders.OrderID = INSERTED.OrderID
       AND INSERTED.Canc <> 0
--only change if Canc is changing in this transaction: 
--we don't want to change the date if the value of Calc is the same, but other columns are vbeing changed.
       AND Orders. Canc <> INSERTED.Canc 
  END 

CREATE TRIGGER dbo.tri_Cancelled
ON Orders
FOR UPDATE
AS
  --objective: set the cancel date to GETDATE if the CANC value <>0 
  BEGIN
    UPDATE Orders
    SET    CancDate = GETDATE()
    FROM   INSERTED
    WHERE  Orders.OrderID = INSERTED.OrderID
       AND INSERTED.Canc <> 0
--only change if Canc is changing in this transaction: 
--we don't want to change the date if the value of Calc is the same, but other columns are vbeing changed.
       AND Orders. Canc <> INSERTED.Canc 
  END 

create trigger dbo.tri_Cancelled
on
	Orders
after
	update
as
begin

update	Orders
set
	CancDate = getdate()
from
	Orders
	inner join
	deleted
	on Orders.OrderID = deleted.OrderID
where
	Orders.Canc <> 0	and
	( deleted.Canc = 0 or deleted.Canc is null )

end