SQL after update trigger between two tables

Question

I have two tables, Engineering and Electrical. Work is done in the Engineering table, then the Electrical team starts work after that. They share some of the same columns. Those columns are

Tag
Service Description
Horsepower
RPM
Project Number

I want to create an after update trigger so that when the Tag column gets filled in the Electrical table and that data matches the data in one of the Tag columns in the Engineering table, the other four same columns in the Engineering table automatically are sent to the corresponding columns in the Electrical table.

Below is what I tried which obviously doesn't work:

CREATE TRIGGER [dbo].[tr_Electrial_Update] 
ON [dbo].[ENGINEERING] 
AFTER UPDATE
AS
BEGIN
    INSERT INTO ELECTRICAL ([ICM_SERVICE_DESCRIPTION_],[PROJECT_NUMBER_], [ICM_POWER_HP_], [ICM_POWER_KW_], [ICM_RPM_])
        SELECT 
            i.[ICM_SERVICE_DESCRIPTION_], i.[PROJECT_NUMBER_],
            i.[ICM_POWER_HP_], i.[ICM_POWER_KW_], i.[ICM_RPM_]
        FROM 
            ENGINEERING m
        JOIN 
            inserted i ON i.[TAG_] = m.[TAG_]    
END

I'm someone trying to teach myself SQL on the fly so be kind. As always I'm very appreciative of any help.

Answer 1

From your post, I'm assuming you already have an entry in the Electrical table, and it's column Tag gets updated from NULL to some other value. This syntax is for SQL Server - you didn't explicitly specify what RDBMS you're using, but it looks like SQL Server to me. If it's not - adapt as needed.

Assuming you have only a single row in Engineering that matches that Tag value, you can do something like this - it has to be an UPDATE statement since you already have a row in Electrical - you want to update some columns, not insert a completely new row:

CREATE TRIGGER [dbo].[tr_Electrical_Update] 
ON [dbo].Electrical 
AFTER UPDATE
AS
BEGIN
    IF UPDATE(Tag)
        UPDATE dbo.Electrical 
        SET [ICM_SERVICE_DESCRIPTION_] = eng.[ICM_SERVICE_DESCRIPTION_],
            [PROJECT_NUMBER_] = eng.[PROJECT_NUMBER_],
            [ICM_POWER_HP_] = eng.[ICM_POWER_HP_], 
            [ICM_POWER_KW_] = eng.[ICM_POWER_KW_], 
            [ICM_RPM_] = eng.[ICM_RPM_]
        FROM Inserted i
        INNER JOIN dbo.Engineering eng ON i.Tag = eng.Tag 
        WHERE Electrical.Tag = i.Tag;
END