Creating an update trigger to update a column in table1 based on a value in table2

Question

UPDATE AT BOTTOM OF THIS POST

---

I have two tables in SQL Server, a simplified view of each is below.

Table1

| ObjectID | Name | Email |
+----------+------+-------+
| 1        | Ben  |       |
| 2        | Tom  |       |

Table2

| ObjectID  | Name |     Email     |
+-----------+------+---------------+
| 52        | Ben  | [email protected] |
| 53        | Tom  | [email protected] |

I want to create a trigger in the database which updates table1, whereby when I update the name field the trigger fires, and inserts the email address which can be found in table2.

Note: both tables have many more columns

Note2: when a row is inserted into table 1, it ONLY receives an ObjectID, then an update is done where the user adds the Name.

After the user adds this name is where I want the database to run off and look at table2, find a matching name, and return to table1 and write the data within.

I have written (unsuccessfully) the below code, I have tried using update() and a heap of other things, but I feel I am way off the mark:

CREATE TRIGGER "acc_insert_email" 
ON TABLE1
AFTER UPDATE
AS
BEGIN
    INSERT INTO TABLE1(email)
        SELECT i.email 
        FROM TABLE1 t 
        INNER JOIN TABLE2 i ON t.email = i.email
END

Can anyone point me back in the correct direction?

Thanks in advance.

---

So I have tested the answer provided by @Ezlo but have not had any luck... I slightly modified it to instead insert the word TEST in the column, to see if it will even do that, but this also has no effect. This is the code I have currently, note I have used actual table and column names:

CREATE TRIGGER "gisadmin"."acc_UpdateOfficerDetails" ON NOXIOUS_WEED_INSPECTION_POINT AFTER UPDATE
AS
BEGIN

    IF UPDATE(authorised_officer_email)
    BEGIN

        UPDATE T SET
            authorised_officer_email = 'TEST'
        FROM
            inserted AS I
            INNER JOIN NOXIOUS_WEED_INSPECTION_POINT AS T ON I.authorised_officer = T.authorised_officer
            LEFT JOIN AUTH_DB.authlive.dbo.acc_weave_emp_details AS N ON I.authorised_officer = N.cus_idd

    END
END

authorised_officer and cus_idd is the staff username (I have successfully done a simple join on these two fields, it works fine, so I have ruled out two different data types being the issue)

So I am perplexed as to why this trigger is not even inserting simple 'TEST' text for me.

Any ideas?

---

Below is the final code which answers my original question, works fantastic! Thanks @EzLo

CREATE TRIGGER "gisadmin"."acc_UpdateOfficerDetails" ON NOXIOUS_WEED_INSPECTION_POINT AFTER
UPDATE AS BEGIN IF
UPDATE
    (authorised_officer) BEGIN
UPDATE
    T
SET
    authorised_officer_email = A.mto_lst
FROM
    inserted AS I
INNER JOIN
    NOXIOUS_WEED_INSPECTION_POINT AS T
ON
    I.authorised_officer = T.authorised_officer
LEFT JOIN
    AUTH_DB.authlive.dbo.acc_weave_emp_details AS A
ON
    I.authorised_officer = A.cus_idd
END
END

Answer 1

You can use the following.

CREATE TRIGGER tr_UpdateTable1Email ON TABLE1 AFTER UPDATE
AS
BEGIN

    IF UPDATE(Name)
    BEGIN

        UPDATE T SET
            Email = N.Email
        FROM
            inserted AS I
            INNER JOIN TABLE1 AS T ON I.Name = T.Name
            LEFT JOIN TABLE2 AS N ON I.Name = N.Name

    END
END

Trigger will execute on TABLE1 updates and the inner update will only be done if the original update statement includes the name column. inserted is a special trigger table that holds the rows of the tracking table (TABLE1 in this case) that were updated. Join inserted with TABLE1 to be able to update the correct emails, then go search the emails to update to TABLE2.

Let me mention that you have denormalized data, and this causes maintainance issues (what happens if someone updates name or email from TABLE2? records at TABLE1 will be out of sync). You should keep the names and emails (and other related data) in just 1 place, so only one update is needed.

---

EDIT: Update on more than 1 column (no parenthesis needed).

ALTER TRIGGER "gisadmin"."acc_UpdateOfficerDetails" ON NOXIOUS_WEED_INSPECTION_POINT AFTER UPDATE 
AS 
BEGIN 
    IF UPDATE (authorised_officer) 
    BEGIN

        UPDATE
            T
        SET
            authorised_officer_email = A.mto_lst,
            authorised_officer_phone = A.bus_mob
        FROM
            inserted AS I
        INNER JOIN
            NOXIOUS_WEED_INSPECTION_POINT AS T
        ON
            I.authorised_officer = T.authorised_officer
        LEFT JOIN
            AUTH_DB.authlive.dbo.acc_weave_emp_details AS A
        ON
            I.authorised_officer = A.cus_idd

    END
END