CODEWITHSUNDEEP
mysqlmysql-workbench
default
default

Reputation: 53

mysql syntax error 1064 "NEW" is not valid at this position for this server version, expecting an identifier

I'm getting an error message related to syntax. I've searched the MySql documentation and these forms and can't seem to find anything that works.

I'm trying to call a stored procedure within a trigger. If a patient has no family physician (hence Fam_Phys_SSN being null) then the procedure is called and they are assigned a family physician.

I'm using MySql workbench (MySql 8) and I get the error message:

Error Code: 1064. 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 'CALL RETURN_PHYSICIAN(SSN); END IF; END' at line 5

The error appears on the "NEW" in SET NEW.Fam_Phys_SSN...

My code is:

-- Write your stored procedure after this line
delimiter //
create procedure RETURN_PHYSICIAN(IN paSSN INT(11), OUT phSSN INT(11))
BEGIN

    select person.SSN INTO phSSN
    from person join physician using (SSN)
    where city = (select city from person where SSN = paSSN) 
       AND country = (select country from person where SSN = phSSN);

END//
DELIMITER ;
-- Write your trigger after this line and call the stored procedure in the body of the trigger.
delimiter //
CREATE TRIGGER trig1 BEFORE INSERT ON patient
FOR EACH ROW
BEGIN
    IF Fam_Phys_SSN IS NULL THEN
        SET NEW.Fam_Phys_SSN = CALL RETURN_PHYSICIAN(SSN);
    END IF;
END//
DELIMITER ;
-- Do not modify or delete the following lines. Do not write anything after this line.
insert into patient values('530166255', null, '[email protected]');

Upvotes: 0

Views: 836

Answers (1)

Barmar
Barmar

Reputation: 780889

CALL isn't an expression, it doesn't return a value. The result is an OUT parameter, so you need to put the column in the argument list.

CREATE TRIGGER trig1 BEFORE INSERT ON patient
FOR EACH ROW
BEGIN
    IF NEW.Fam_Phys_SSN IS NULL THEN
        CALL RETURN_PHYSICIAN(NEW.SSN, NEW.Fam_Phys_SSN);
    END IF;
END//

Upvotes: 4

Related Questions