CODEWITHSUNDEEP
sqltriggers
Shayan
Shayan

Reputation: 2828

SQL Trigger Recursive update

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.

Upvotes: 0

Views: 2398

Answers (1)

David M
David M

Reputation: 72890

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

Upvotes: 2

Related Questions