3

I have the following trigger:

CREATE TRIGGER SalaryCannotDecrease ON Employees
AFTER UPDATE
AS
IF EXISTS (
            SELECT *
            FROM inserted, deleted
            WHERE inserted.Salary < deleted.Salary
            )
BEGIN
    RAISERROR 30002 'You cannot decrease salary';
    ROLLBACK TRANSACTION;
    RETURN
END;

And this trigger is firing when I try to:

UPDATE Employees
SET Salary = Salary * 1.05

What am I doing wrong here?

  • 3
    Just looking at your query this might show what is partially wrong. You need to join the inserted and deleted within your trigger to properly pull valid data. Once you do that I think it should work. – Shawn Melton Nov 12 '14 at 0:41
3

First, just a little terminology thing: you do want your trigger to fire, since you are updating Employees; you just don't want the IF statement's condition to be satisfied.


Now on to the problem. When you list tables in a FROM clause like this:

FROM inserted, deleted

you are actually creating a CROSS JOIN between the tables. So this SELECT subquery will be looking at every combination of inserted and deleted rows, and looking for any instance where some inserted.Salary is less than some other deleted.Salary.

So if your salaries were

100
200
300

to start with, your UPDATE would try and change them to

105
210
315

and the combinations considered in the subquery would be:

Is 105 < 100? No
Is 210 < 100? No
Is 315 < 100? No
Is 105 < 200? Yes
Is 210 < 200? No
Is 315 < 200? No
Is 105 < 300? Yes
Is 210 < 300? Yes
Is 315 < 300? No

Hence the IF condition is met, and the ROLLBACK takes place.

To fix it, be sure to only compare inserted.Salary to deleted.Salary for the same employee, by specifying the Employees primary key (here I asssume Id) in an explicit JOIN:

FROM inserted INNER JOIN deleted ON inserted.Id = deleted.Id

The two general points are:

  • Don't write FROM table1, table2 in queries - always be explicit about the kind of join you want
  • When writing triggers, always bear in mind that inserted / deleted can have more than one record in them
5

Join Employees table's unique ID with inserted in trigger.

--- Employees table 
CREATE TABLE Employees
( EmpID INT,  --- unique id for employee
  Salary INT
)

--- Dummy data insertion
INSERT INTO Employees
SELECT 11001,5000
UNION ALL 
SELECT 10003,6500
UNION ALL 
SELECT 10004,6900
UNION
SELECT 10006,300


-- Salary Trigger 
CREATE TRIGGER SalaryCannotDecrease ON Employees
AFTER UPDATE
AS
IF EXISTS (
            SELECT *
            FROM inserted INNER JOIN deleted ON inserted.empid=deleted.empid
            WHERE inserted.Salary < deleted.Salary
            )
BEGIN
    RAISERROR 30002 'You cannot decrease salary';
    ROLLBACK TRANSACTION;
    RETURN
END;


-- Update Statement
UPDATE Employees
SET Salary = Salary * 1.05
WHERE empid=10006


--- Verification 
SELECT * FROM Employees
  • 1
    Joining with the inserted table on identical ID will usually be faster than joining with the employees table, wouldn't it? – Twinkles Nov 12 '14 at 12:23
  • @Twinkles,I cross checked yes by joining 'inserted' with 'deleted' Duration, reads and CPU cost is always better. I modified answer – AA.SC Nov 12 '14 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.