CODEWITHSUNDEEP
mysqltriggersconcatenation
João Pedro
João Pedro

Reputation: 546

Can't create a trigger to insert concat value

i am trying to create a trigger to concatenate my table columns into a single column but i can't find the error.

code:

create trigger molecule_trigger After insert on molecule
For each row 
begin

Update molecule
Set molecule_text= CONCAT(mid,',',ULCHEm_ID,',',IUPAC_name,',',Inchi,',',inchi_key,',',smiles,',',can_smiles,',',Molecular_formula,',',Molecular_weight,',',vendor,',',CAS,',',links,',',image); 
end;

ERROR: #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 '' at line 6

Upvotes: 1

Views: 1475

Answers (1)

fancyPants
fancyPants

Reputation: 51918

You get this error, because you started a "multiple statements" block with begin, but the ; after your update statement terminates the create trigger statement before the end; statement.
You have to either change the delimiter

DELIMITER $$
create trigger molecule_trigger After insert on molecule
For each row 
begin

Update molecule
Set molecule_text= CONCAT(mid,',',ULCHEm_ID,',',IUPAC_name,',',Inchi,',',inchi_key,',',smiles,',',can_smiles,',',Molecular_formula,',',Molecular_weight,',',vendor,',',CAS,',',links,',',image); 
end $$
DELIMITER ;

Or you remove the begin and end.

create trigger molecule_trigger After insert on molecule
For each row 
Update molecule
Set molecule_text= CONCAT(mid,',',ULCHEm_ID,',',IUPAC_name,',',Inchi,',',inchi_key,',',smiles,',',can_smiles,',',Molecular_formula,',',Molecular_weight,',',vendor,',',CAS,',',links,',',image);

Now, you have another problem. You're trying to do an action in the trigger on the same table as your trigger works on. This is not allowed. Change your trigger to this:

create trigger molecule_trigger BEFORE insert on molecule
For each row 
SET NEW.molecule_text= CONCAT_WS(',', NEW.mid, NEW.ULCHEm_ID, NEW.IUPAC_name, NEW.Inchi, NEW.inchi_key, NEW.smiles, NEW.can_smiles, NEW.Molecular_formula, NEW.Molecular_weight, NEW.vendor, NEW.CAS, NEW.links, NEW.image);

Note though, that this sets the molecule_text only for the columns inserted. Your trigger updated the whole table each time a row is inserted. And if you insert 3 rows in one statement, your table gets updated 3 times. This is not what you want to do anyway :)

Upvotes: 1

Related Questions