CODEWITHSUNDEEP
sqlmysqltriggersmysql-workbenchdeclare
D H
D H

Reputation: 1

DECLARE is not valid at this position expecting END (TRIGGER)

I am trying to create a trigger to capitalise the first letter of the first name and surname in the customer name column. However receiving an error on the declare section: DECLARE IS NOT VALID AT THIS POSITION EXPECTING END

This is the code at the moment:

USE TRADING;

DELIMITER //
CREATE TRIGGER capitalise_surname
BEFORE INSERT ON Customer
FOR EACH ROW
BEGIN
    
    SET NEW.Customer_name = CONCAT(UPPER(SUBSTRING(NEW.Customer_Name, 1, 1)),
                                    LOWER(SUBSTRING(NEW.Customer_Name FROM 2)));
  
    DECLARE space_index INT;
    SET space_index = LOCATE(' ', NEW.Customer_name);
    
    IF space_index > 0 THEN
        SET NEW.Customer_name = CONCAT(LEFT(NEW.Customer_name, space_index),
                                        UPPER(SUBSTRING(NEW.Customer_name, space_index + 1, 1)),
                                        LOWER(SUBSTRING(NEW.Customer_name FROM space_index + 2)));
    END IF;
END //
DELIMITER ;

I have tried removing the declare and space index part.

Upvotes: 0

Views: 43

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562791

https://dev.mysql.com/doc/refman/8.0/en/declare.html

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

(emphasis mine)

Upvotes: 1

Related Questions