SQL Trigger Recursive update

Question

Hello

I have an organizational database where in it each employee have a foreign key to it's boss(FID).

Table declaration:

Create Table Emp(
    ID integer,
    FID integer,
    SALARY integer,
    Primary key (ID), 
    foreign key (FID) references EMP
);

The following sql trigger should update the employees under a boss. And then update their children recursively. but it only updates one level.

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

I want to know how to solve this problem. Thank you.

Answer 1

Assuming this is SQL Server, you need to enable recursive triggers using sp_dboption. See MSDN for more details:

http://msdn.microsoft.com/en-us/library/ms189799.aspx