Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
5  
What did you try yourself? –  Colin 't Hart May 8 at 9:00
3  
Start by looking for documentation about CREATE TRIGGER - if you then have a particular problem update your question with the details. Thanks! –  Max Vernon May 8 at 12:08
    
Hello, this is my example. It runs and up dates the custom field, however when a new transaction is entered into the database the custom field is not updated which is what I want. –  Bill James May 9 at 9:19
1  
Please do not put code samples or sample data into comments - since you cannot format it, it's extremely hard to read it.... Instead: update your question by editing it to provide that additional information! Thank you. –  marc_s May 9 at 11:21
    
Good morning, I have updated as requested. –  Bill James May 9 at 22:06

1 Answer 1

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.