How can I update some columns table2 from table1 automatically after insert any row in table1 (SQL S-CodePudding

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;

CodePudding user response：

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).

CodePudding user response：

Instead of the trigger, we can use an OUTPUT CLAUSE

DECLARE @TmpTable TABLE (ID INT, ranking VARCHAR(100))

INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID ,Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')

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