patternMinor
Trigger firing when it shouldn't
Viewed 0 times
firingshouldntriggerwhen
Problem
I have the following trigger:
And this trigger is firing when I try to:
What am I doing wrong here?
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.05What 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 EmployeesCode 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 EmployeesContext
StackExchange Database Administrators Q#82436, answer score: 5
Revisions (0)
No revisions yet.