title: SQL Trigger Recursive update
body: <p>I have an organizational database where in it each employee have a foreign key to it's boss(FID).</p>
  
  <p>Table declaration:</p>
  
  <pre><code>Create Table Emp(
      ID  integer,
      FID integer,
      SALARY integer,
      Primary key (ID), 
      foreign key (FID) references EMP
  );
  </code></pre>
  
  <p>The following sql trigger should update the employees under a boss. And then update their children recursively. but it only updates one level.</p>
  
  <pre><code>CREATE TRIGGER SAL_TRIG ON EMP After UPDATE
  as
  declare @SALARY int  
  declare @OLDSAL int 
  declare @ID int      
  
  --use the 'inserted' keyword to access the values inserted into the invoice table
  select @OLDSAL = Salary from deleted
  select @SALARY = Salary from inserted 
  select @ID = ID from inserted
  
  
  BEGIN
      UPDATE EMP 
      SET SALARY = salary + @SALARY - @OLDSAL
      WHERE FID = @ID
  END
  </code></pre>
  
  <p>I want to know how to solve this problem.
  Thank you.</p>