HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Trigger firing when it shouldn't

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
firingshouldntriggerwhen

Problem

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?

Solution

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

Code Snippets

--- 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

Context

StackExchange Database Administrators Q#82436, answer score: 5

Revisions (0)

No revisions yet.