MySQL INSERT Trigger: SQL Syntax Error when referencing inserted row-CodePudding

I am creating an SQL trigger on mySQL

CREATE TRIGGER workson_insert_trigger
AFTER INSERT ON workson
WHEN (NEW.pno IN (SELECT pno FROM proj))
UPDATE proj SET budget = budget   1000;

The code is supposed to increase budget by 1000 in the proj table every time a record is added into the workson table.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN (NEW.pno IN (SELECT pno FROM proj)) UPDATE proj SET budget = budget 1000' at line 3 SQLState: 42000 ErrorCode: 1064

I've tested the UPDATE statement by itself and is executed properly so, I'm assuming that the issue is when I reference the inserted row. the NEW.pno part

Note: I am somewhat new to SQL

If I'm missing any information in my question, let me know

CodePudding user response：

CREATE TRIGGER workson_insert_trigger
AFTER INSERT ON workson
FOR EACH ROW
IF (NEW.pno IN (SELECT pno FROM proj)) THEN
  UPDATE proj SET budget = budget   1000;
END IF;

CodePudding user response：

I suppose it's the missing delimiter statement to blame. I have tried simulating the case in workbench. It works as expected.

create table proj (pno int primary key auto_increment,budget int);
create table workson (pno int);
delimiter //
CREATE TRIGGER workson_insert_trigger
AFTER INSERT ON workson
FOR EACH ROW
IF (NEW.pno IN (SELECT pno FROM proj)) THEN
  UPDATE proj SET budget = budget   1000;
END IF //
delimiter ;
insert proj (budget) values(100),(200),(300);
insert into workson values(1),(2);

select * from proj;
-- result set:
# pno, budget
1, 2100
2, 2200
3, 2300