CODEWITHSUNDEEP
mysqlsql
nullException
nullException

Reputation: 1122

sql creating a trigger on update

I have three tables.

  1. members and it has a nullable column called blocked,ID and primaryPhone.
  2. blockedinfo it has 2 columns info and type.
  3. phoneNumbers it has ID, prefix, phoneNumber, memberID

I'm trying to create a trigger on the members table to insert the user primary phone number to the blockedinfo table when I set the blocked field in the members table. And remove it from the blockedinfo when I set the blocked field to NULL

CREATE TRIGGER `members.before_update` BEFORE UPDATE ON  `members` FOR EACH ROW BEGIN 
IF( NEW.`blocked` IS NOT NULL ) THEN 
INSERT INTO  `blockedinfo` ( phone,  'userPhoneNumberValue') 
SELECT CONCAT(  `prefix` ,  `phoneNumber` ) AS  'phone' 
FROM  `phonenumbers` 
WHERE  `ID` = NEW.`primaryPhone` 
AND  `memberID` = NEW.`ID` 
LIMIT 1 ;
ELSE 
DELETE FROM  `blockedinfo` WHERE  `info` IN (
SELECT CONCAT(  `prefix` ,  `phoneNumber` ) 
FROM  `phonenumbers` 
WHERE  `ID` = NEW.`primaryPhone` 
AND  `memberID` = NEW.`ID`);
END IF ;

END ;

I get this error when trying to build the trigger

The following query has failed: "CREATE DEFINER=root@127.0.0.1 TRIGGER members.before_update BEFORE UPDATE ON members FOR EACH ROW BEGIN MySQL said: #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 ''phoneNumberValue') SELECT CONCAT(prefix,phoneNumber` ) AS 'phone' at line 4

Upvotes: 0

Views: 154

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

To start with, in this part

INSERT INTO  `blockedinfo` ( phone,  'userPhoneNumberValue') 
SELECT CONCAT(  `prefix` ,  `phoneNumber` ) AS  'phone' 
FROM  `phonenumbers`

you provide a string literal, "userPhoneNumberValue", where you should provide the column name in "blockedinfo". Name the columns you're targeting here.

INSERT INTO `blockedinfo` (info, type)

In this part

SELECT CONCAT(  `prefix` ,  `phoneNumber` ) AS  'phone'

you're providing one value. You need to provide two. I'm guessing that you need something more like this.

SELECT CONCAT(`prefix`, `phoneNumber`) AS phone, 'userPhoneNumberValue'

Upvotes: 1

Nagasaki
Nagasaki

Reputation: 58

INSERT INTO  `blockedinfo` ( phone,  `phoneNumber`) 
SELECT CONCAT(  `prefix` ,  `phoneNumber` ) AS  `phone`

Here you try to insert 2 values but your select return just 1

try:

INSERT INTO  `blockedinfo` ( phone,  `phoneNumber`) 
SELECT CONCAT(  `prefix` ,  `phoneNumber` ) AS  `phone`, `phoneNumber`
FROM  `phonenumbers` 
WHERE  `ID` = NEW.`primaryPhone` 
AND  `memberID` = NEW.`ID` 
LIMIT 1 ;

Upvotes: 0

Related Questions