MariaDB Triggers error for getting length of name-CodePudding

I am trying to see if the length of a name in my library table is the allowed length. This is my trigger but it's giving me an error. I have a table called membership and it has a column called name, for the name of each person with a membership.

Any help would be greatly appreciated!

This is my trigger:

BEGIN
DECLARE msg varchar(100);
FOR EACH ROW BEGIN
IF len(membership.name) NOT between 4 and 20 THEN
set msg ='Name should be between 5 and 20 letters'; 
SET MESSAGE_TEXT = msg;
END IF;
END

This is the error I'm getting:

The following query has failed: "CREATE TRIGGER Student_Name BEFORE INSERT ON membership FOR EACH ROW BEGIN DECLARE msg varchar(100); FOR EACH ROW BEGIN IF len(membership.name) NOT between 4 and 20 THEN set msg ='Name should be between 5 and 20 letters'; SET MESSAGE_TEXT = msg; END IF; END "

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EACH ROW BEGIN IF len(membership.name) NOT between 4 and 20 THEN set msg ='...' at line 3

CodePudding user response：

Review the syntax for CREATE TRIGGER: https://mariadb.com/kb/en/create-trigger/

You must use FOR EACH ROW only once, before the body of the trigger.

You have also used FOR EACH ROW a second time, after your DECLARE line. Take that out.

CREATE TRIGGER Student_Name BEFORE INSERT ON membership
FOR EACH ROW
BEGIN
 DECLARE msg varchar(100);
 FOR EACH ROW BEGIN <---- remove this line
 IF LENGTH(NEW.name) NOT between 4 and 20 THEN
  SET msg ='Name should be between 5 and 20 letters'; 
  SIGNAL SQLSTATE '45000' 
  SET MESSAGE_TEXT = msg;
 END IF;
END

There are other problems too.

This row:

 IF len(membership.name) NOT between 4 and 20 THEN

There is no LEN() function. See LENGTH().

If you want to refer to the value of the name column in the new row you are inserting, use NEW.name.

If you want to refer to the column name of the new row inserted, use NEW.name, not membership.name.

You probably also want to use the MESSAGE_TEXT somehow to abort the insert in case of a conflict with your rule. You probably intended to use SIGNAL.