Sql-server – Trigger to update a date field on one table when another table is updated

sql serversql-server-2008-r2trigger

I want to be able to update a date filed in one table when another table is updated.
I have the following two tables: Transdetails, Customers.

In the 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 ;

Hello,

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.

Best Answer

You should be using the inserted and deleted pseudo tables to find out the rows for which the column was affected by the trigger - and then update only the related rows in the second table:

CREATE TRIGGER trg_LastSaleDate ON dbo.Transheaders
    AFTER UPDATE
AS
    IF UPDATE(TradingDate)
    BEGIN
        UPDATE c
            SET ZLastSale = i.TradingDate
        FROM dbo.Customers AS c
          JOIN inserted AS i
            ON i.CustomerID = c.CustomerID     -- use the appropriate column for joining
          JOIN deleted AS d
            ON  i.TransheadersID = d.TransheadersID
            AND ( i.TradingDate <> d.TradingDate
               OR d.TradingDate IS NULL
                ) ;
    END ;
Related Question