I want to be able to update a date filed in one table when another table is updated.
I have the following two tables:
Customers table I have a field called
ZLastDateOfSale. I want this field updated with the current date when
Transdetails.TradingDate is updated per Customer.
Can you please help me create this Trigger?
My Example is below:
CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders AFTER UPDATE AS IF UPDATE(TradingDate) BEGIN update dbo.Customers set dbo.Customers.ZLastSale = dbo.Transheaders.TradingDate from dbo.Customers END
Updated Example 11/05/2014:
CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders AFTER UPDATE AS IF UPDATE(TradingDate) BEGIN UPDATE c SET ZLastSales = i.TradingDate FROM dbo.Customers AS c JOIN inserted AS i ON i.UniqueID = c.UniqueID -- use the appropriate column for joining JOIN deleted AS d ON i.AccountID = d.AccountID AND ( i.TradingDate <> d.TradingDate OR d.TradingDate IS NULL ) ; END ;
Thank you so much for your input and response. I changed your provided example, ran the script and it was successful, however, what it did was to update the ZLastSale date on all Customers with todays date. This field should be blank and only update moving forward from now when the Trigger fires. This field should only be updated upon a sale from a Customer.
Also, when I tested entering a new transaction with a date of 30/05/2014 it didn’t update the required ZLastSale field. It is displayed as 11/05/2014 so something is not working right somewhere.
My ID’s for each table are:
Customers.UniqueID and Transheaders.AccountID
Thanks again for your help.