CODEWITHSUNDEEP
sql-servert-sqlrecursiontriggers
foxbuur
foxbuur

Reputation: 169

SQL Server triggers, recursive update

I have the table employees, which looks like this:

CREATE TABLE employees
(
     id int primary key,
     name varchar(20) not null,
     boss int references employees(id),
     salary money,
);

Now I have to create a trigger, which after

UPDATE employees
SET money = 5000 
WHERE id = 1

change recursively managers' salary (difference between old and new salary amount to +500zł, employee from the UPDATE has 3 manages, so I want to update his salary and salaries of his managers, but every manager is a boss for his managers, so I want to update their salaries too.)

So ok, I've written the trigger

CREATE TRIGGER salary_recursive ON employees
AFTER UPDATE
AS
  IF(SELECT salary FROM inserted)!=(SELECT salary FROM deleted)
  BEGIN
    declare @diff money = (SELECT salary FROM inserted) - (SELECT salary FROM deleted)
    update employees
    set salary = salary + @diff
    where boss = (SELECT id FROM inserted)
  END

It works correctly, but it updates only one level. So I've tried 

ALTER DATABASE myDatabase
SET RECURSIVE_TRIGGERS ON

but it raises

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How can I fix it?

Upvotes: 2

Views: 565

Answers (1)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56717

The problem is in this line and other lines that are similar to

IF (SELECT salary FROM inserted) != (SELECT salary FROM deleted)

This will not work if the SELECT returns more than one value. You could try to rewrite this as follows:

IF EXISTS (SELECT 1 FROM inserted i INNER JOIN deleted d ON d.id = i.id WHERE i.salary <> d.salary) BEGIN
    ...
END

The inner part of your block also has the same problem. You can actually solve this using a single SQL statement, but I can not test it right now. It might work along the lines of this:

UPDATE employees
SET
    salary = salary + (SELECT i.salary - d.salary FROM inserted i INNER JOIN deleted d ON d.id = i.id WHERE i.ID = employees.boss)
WHERE
    boss in (SELECT i.id FROM inserted i INNER JOIN deleted d ON d.id = i.id WHERE d.salary <> i.salary)

The idea is to update all the employees where the boss-id is one of the ids where the salary has changed. As there can be more than one record in inserted, you need to use in here.

The update adds to the salary field the difference between the new and old values where the salary id is the current record's boss-id.

Upvotes: 2

Related Questions