0

I did a trigger validating two columns,when only one of them is wrong it show the message normally,when both are wrong I get the error

Error Code: 1292. Truncated incorrect INTEGER value: ''

I believe it's on the Concat part am I missing something on the function?

 if(RESULTADO = false) then
    set msg = "Cpf invalido";
 end if;

    if(new.idadeproprietario <18) then

        set msg2 = "Idade invalida";
    end if;

    if (msg is not null) or (msg2 is not null) then 

            select Concat(msg, msg2) into msg3;

        Signal sqlstate '40000' set message_text = msg3;
    end if;
ragerory
  • 1,360
  • 1
  • 8
  • 27
henrique richter
  • 1
  • 1
  • 2
  • concat will return Null when either value is null, you may need to coalesce msg and msg2 into empty sets like `select Concat(coalesce(msg,'',coalesce(msg2,''')) into msg3; another stack article on topic... http://stackoverflow.com/questions/15741314/mysql-concat-returns-null-if-any-field-contain-null – xQbert Jun 05 '15 at 20:46
  • @xQbert it worked just had to change a few things,for future reference select Concat(coalesce(msg,''),coalesce(msg2,'')) into msg3; – henrique richter Jun 05 '15 at 21:53

2 Answers2

0

Change

select Concat(msg,msg2) into msg3;

to

select Concat(msg,msg2) as msg3;

and that should do the trick.

ragerory
  • 1,360
  • 1
  • 8
  • 27
0
Warning (Code 1292): Truncated incorrect INTEGER value: '<Some Value>'

I was getting this warning as an error while creating a table using CTAS also while inserting data into an already created table.

This was happening due to using count() on the column values. Use Sum() method instead of count()

Usually, Select statement gives output without error but it gives Warning(Which can be checked by enabling warning \W). This gives error while INSERT/CTAS.

ketankk
  • 2,578
  • 1
  • 29
  • 27