Trigger to insert one record to new rows from another table-CodePudding

I have a small app that inserts data into the database.

I have a something like this

dbo.system.table1(col1, col2, col3) and table2(col1) (table2.col1 is just one single row).

What I want to do is insert the table2.col1 into table1.col3 when a new order is made.

Also the table2.col1 updates twice a day, so every time a new order is made with table1 I need to keep the old table1.col3 changes.

I've tried

CREATE TRIGGER dbo.TR_CHANGES
ON dbo.SYSTEM 
AFTER INSERT
AS
    UPDATE table1
    SET table1.col3 = (SELECT col1 
                       FROM table2
                       WHERE table1.col3 = table2.col1)

But it ends updating col3 for all rows.

CodePudding user response：

You need to include the Inserted pseudo table into your statement, to find the rows that were actually updated - and I would recommend using proper JOIN syntax instead of those nested subqueries - seems a lot easier to read and understand for me.

So try this:

CREATE TRIGGER dbo.TR_CHANGES
ON dbo.SYSTEM 
AFTER INSERT
AS
    UPDATE t1
    SET col3 = t2.col1
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.col3 = t2.col1
    INNER JOIN inserted i ON t1.primarykeycol = i.primarykeycol

You need to replace the .primarykeycol for the Inserted and t1 tables with the actual primary key column for your table - this is needed to link the Inserted rows with the actual data table