I have two tables, Accounts and Person:

CREATE TABLE Person(
id INT NOT NULL PRIMARY KEY,
Person_Name VARCHAR(17) NOT NULL,
P_Location INT NOT NULL
);

INSERT INTO Person VALUES (1,"Adam",300),(2,"Betty",10),(3,"Louis",60);

CREATE TABLE Accounts(
Person_id INT PRIMARY KEY,
Balance INT DEFAULT 200);

INSERT INTO Accounts VALUES (1,2000),(2,1350),(3,800);

And one trigger, Bonuses:

CREATE TRIGGER Bonuses
AFTER UPDATE ON Person
FOR EACH ROW
UPDATE Accounts
SET Balance = CASE WHEN (SELECT P_Location FROM Person WHERE id = Person_id) = 3 THEN Balance - 150
                   WHEN (SELECT P_Location FROM Person WHERE id = Person_id) = 7 THEN Balance   100
                   WHEN (SELECT P_Location FROM Person WHERE id = Person_id) = 15 THEN Balance - 30
                   WHEN (SELECT P_Location FROM Person WHERE id = Person_id) = 1 THEN Balance   200
                   END;

And I want to make the trigger update the Accounts table according to certain instructions whenever the P_Location on the Person table changes to one of a select few values (3,7,15 and 1). However, as things are they result is incorrect. Assume I run the above code, the tables I get are:

Person

Accounts

Now if I run UPDATE Person SET P_Location = 3 WHERE id = 1; then the Accounts table should yield:

However, what I get is

Any idea of what I'm doing wrong?

CodePudding user response：

Well, that code did exactly what you said, though it wasn't what you meant!

That's the thing about UPDATE queries, EVERY row will get an update unless a WHERE clause is used to filter what actually gets modified. Nothing is found from the CASE with most records, so any of those will get assigned to NULL. To see this behavior, check this

Then run: UPDATE Person SET P_Location = 3 WHERE id = 1;

Gives:

Example fiddle with your tables, the simplified trigger case handling, and the output examples from the update query.