how to trace mysql trigger or help with this one.

Question

Hello everyone,

Is there a way to trace MySql triggers? I am stuck with the following one.

I want to calculate a column before insert concatenating two other columns, but there is problem calculated value is always null, i also need to check if one column has comma separated values, in that case i only want the first one, here is the code

BEGIN 
    declare pos int;
    pos = locate(',',new.Admin1Code, 1);
    if (pos = -1) then
        set new.StateCode = Concat(New.CountryCode,'.',New.Admin1Code);
    else
        set new.StateCode = Concat(New.CountryCode,'.',substring(New.Admin1Code,1,pos));
    end if;   
END;

What is the problem there?

Thanks.

Answer 1

LOCATE will never return -1 - if you try:

SELECT LOCATE(',', 'abbc')

...it returns 0. Use:

if (pos > 0) then
  set new.StateCode = Concat(New.CountryCode,'.',substring(New.Admin1Code, 1, pos));        
else
  set new.StateCode = Concat(New.CountryCode,'.',New.Admin1Code);
end if;

The documentation says as much:

Returns 0 if substr is not in str.