Update Trigger.. Not Working as intended?

Question

I am learning how to construct Triggers in MYSQL, I have constructed a Trigger that will recalculate the Department Salary if one of the Department Employees is updated (either their salary is updated or they are added to/removed from the department). If I update a user's salary, it is not updating the departments salary as well.

Employee Table:

SSN
Salary
Name
Dno
Supervisor_ssn

Department Table:

DName
Dno
Total_sal
Manager_SSN

Trigger looks as follows:

DELIMITER $$
CREATE TRIGGER `Employee_AUPD` AFTER UPDATE ON `Employee` FOR EACH ROW
BEGIN
 IF old.salary != new.salary THEN
    UPDATE department SET total_sal = total_sal - old.salary
        WHERE new.dno = department.dno;
    UPDATE department SET total_sal = total_sal + new.salary
        WHERE new.dno = department.dno;
 END IF;
 IF old.dno != new.dno THEN
    UPDATE department SET total_sal = total_sal - old.salary
        WHERE old.dno = department.dno;
    UPDATE department SET total_sal = total_sal + new.salary
        WHERE new.dno = department.dno;
 END IF;
END;
DELIMITER;

The record I was updating had NULL values for Salary and Dno, I am wondering if that had something to do with it. Like you said, it works great for records that do not have NULL values. — Talen Kylon, Apr 19 '14 at 01:21
read this: http://stackoverflow.com/questions/6999295/comparing-a-null-to-another-value-in-mysql-trigger (you should have included the problem was with null values in the question). — guido, Apr 19 '14 at 01:28

score 0 · Answer 1 · answered Apr 19 '14 at 02:38

You have a logic error. If the salary and the department changes, the first IF block substracts the old salary from the new department.

But you don't need to test anything. Simply code these two update statements:

UPDATE department SET
total_sal = total_sal - old.salary
WHERE department.dno = old.dno;

UPDATE department SET
total_sal = total_sal + new.salary
WHERE department.dno = new.dno;

This logic is steaightforward and can't fail.

Update Trigger.. Not Working as intended?

1 Answers1