Home How can I update some columns table2 from table1 automatically after insert any row in table1 (SQL Server)?

Questions

How can I update some columns table2 from table1 automatically after insert any row in table1 (SQL Server)?

April 21, 2022

I have table1:

id  name    ranking energy   
-------------------------------
222    tom      15      f            
333    sara     11      f    
333    sara     2       a      
111    jhon     4       h       
111    jhon     16      f    
333    sara     13      g    
222    tom      12      j

And table2:

id  name    ranking
-------------------
111 jhon    2
222 tom     1
333 sara    0

I want for any row insert to table1 automatically update the column ranking in table2.

I think I should use the trigger but I do not know how?

I use below code but not work:

CREATE TRIGGER Table1Trigger   
ON Table1 
AFTER INSERT
BEGIN
    UPDATE Table2 
    SET ranking = Table1.ranking 
    WHERE Table1.id = Table2.id;
END;

>Solution :

Presumably you actually want this:

CREATE TRIGGER dbo.Table1Trigger
ON dbo.Table1
AFTER INSERT
AS --AS was missing
BEGIN

    UPDATE T2
    SET ranking = i.ranking
    FROM dbo.Table2 T2 
         JOIN inserted i ON T2.id = i.id;

END;

Table1 in your attempt had no context; there was no object within the query defined as Table1. Also, however, it is highly unlikely you want to use every row in the table Table1, and rather just the rows that were inserted (which are in the pseudo table inserted).