Update Field on another table when a Field is changed in first table 2

[phweston]

I am putting together a trigger to update 2 fields in a second table when a field is changed in the first table.

The Trigger should work like this. The user updates a customer name in Table 1, and the customer name is updated to reflect the changes in Table2. In addition, the trigger should update the field "edit_date" in table2 with the current system date.


CREATE TRIGGER Customer_Name_Update
ON Table1
for update
as

If update (Customer_Name)
UPDATE Table2
SET Table2.CUSTOMER_NAME = Table1.customer_name
FROM Table2
INNER JOIN Table1
ON Table2.customer_code = Table1.customer_code

if update(Customer_Name)
UPDATE TABLE2
SET TABLE2.edit_Date = getdate()
FROM TABLE2INNER JOIN TABLE1
ON TABLE2.customer_code = TABLE1.customer_code

The first part where the customer name is updated in table when it is changed in Table 1 works fine.

What is not working is the part where a date field is updated. This part updates the entire Table 2 for the Edit Date Field.

Any suggestions?

 

[SQLSister]

You should be joining to the inserted psuedotable instead of table1. Just substitute the word inserted for the tablename.

In a trigger there are two pseudotables called inserted and deleted which hold the data being inserted, updated or deleted in the transaction. In an update, the old data is stored inthe deleted table and the new data is stored in the inserted table. In a trigger you normally want to refernce these tables instead of the orginal table the trigger is on if you only want to have the trigger actions happen against the data that was actually changed or new or gotten rid of.

Your first update is also updating all the rows but you may not have noticed since the data would not have changed except inthe few records which were in the transaction. It will need to be changed as well for efficiency sake as it is a bad idea to update 1,000,000 records when only 1 changed.

Questions about posting. See faq183-874

 

[phweston]

Thanks. This is what I came up with, and it seems to work okay.


ALTER TRIGGER Customer_Name_Update
ON Table1
for update
as


if update(Customer_Name)
UPDATE TABLE2
SET TABLE2.CUSTOMER_NAME = INSERTED.customer_name
FROM INSERTED
WHERE TABLE2.CUSTOMER_CODE = INSERTED.CUSTOMER_CODE

if update(Customer_Name)
UPDATE TABLE2
SET TABLE2.edit_Date = getdate()
FROM INSERTED
WHERE TABLE2.CUSTOMER_CODE = INSERTED.CUSTOMER_CODE

.....

 

[ShankarJ]

Hi!

You can combine the two UPDATE's if they are updating the same table :

if update(Customer_Name)
UPDATE TABLE2
SET TABLE2.CUSTOMER_NAME = INSERTED.customer_name,
TABLE2.edit_Date = getdate()
FROM INSERTED
WHERE TABLE2.CUSTOMER_CODE = INSERTED.CUSTOMER_CODE

Regards

 

[phweston]

Thanks as well. I usual break out my code into separate pieces if I am having an issue with. That way, I can find what the problem or issue is.