Trigger MySql while using CONCAT inserting data is Null

Question

This is the query i execute after insertation in some table in my database. Here i am getting rate_like_id and all other fields i also save them but when i use CONCAT and trying to save them as json it inserts NULL value via trigger.

Is this a wrong way to do this. Is ther any other way i can save this type of data via trigger mysql.

INSERT INTO `log_activity`(`visitor_id`,
                       `rating_like_id`,
                       `type`,
                       `response`,
                       `created_by`,
                       `created_date`)
VALUES (new.created_by,
    new.rating_like_id,
    CASE WHEN new.is_like = NULL THEN "Rating" ELSE "Like" END ,
    -- Here is this column Should not be null but it is coming null when INSERTED 
    CONCAT(
   '{"card_id":','"',new.card_id,'"',
   ',"card_type":','"',new.card_type,'"',
   ',"user_id":','"',new.user_id,'"',
   ',"is_like":','"',new.is_like,'"',
   ',"has_rated":','"',new.has_rated,'"',
   ',"rate":','"',new.rate,'"',
   ',"created_by":','"',new.created_by,'"',
   ',"created_date":','"',new.created_date,'"',
   ',"card_type":','"',new.card_type,'"','}'),
    new.created_by,
    new.created_date)

what is wrong with this .. all other fields are saved correctly only CONCAT field is not getting inserted properly.

Yes I am able to insert value like this

CONCAT(" hi "," how "," are "," you ", "  ? ")

Is this problem because i am using "new.fieldName" inside CONCAT ... i see THIS question they are doing the same thing .. ofcource it is less complex compare to mine.

you're building json on-the-fly in the db? that's just... nuts... and remember that sql null is contagious. if ANY of the fields you're concatting are themselves null, the ENTIRE result becomes null. — Marc B
– Marc B, Commented Jun 30, 2015 at 18:10
if ANY of the fields you're concatting are themselves null ... @MarcB let me see ... may be this should be the issue.... — Jaimin MosLake
– Jaimin MosLake, Commented Jun 30, 2015 at 18:19
Yes building json... because can not create all the fields... becuase in this table ... more then 10 tables's data will be saved for logging... @MarcB ... i know its awkward ... but its a requirenment.. — Jaimin MosLake
– Jaimin MosLake, Commented Jun 30, 2015 at 18:20
well, then at least try IFNULL(x, '') for each of those new.whatever so you convert them to empty strings if they ARE null. — Marc B
– Marc B, Commented Jun 30, 2015 at 18:21

Jaimin MosLake · Accepted Answer · 2015-06-30 18:37:56Z

If you see comments and conversation between me and marcB.. you will get the answer...

Here i want to show what changes i made to my query to make it work.

marcB said :- Remember that sql null is contagious. if ANY of the fields you're concatting are themselves null, the ENTIRE result becomes null

so you can see my query in question.. i do not have checked for any value is it null or not. So i just had to add the null check.

INSERT INTO `log_activity`(`visitor_id`,
                       `rating_like_id`,
                       `type`,
                       `response`,
                       `created_by`,
                       `created_date`)
VALUES (new.created_by,
    new.rating_like_id,
    CASE WHEN new.is_like = NULL THEN "Rating" ELSE "Like" END,
    CONCAT('{"card_id":',
           '"',
           IFNULL(new.card_id, ''),
           '"',
           ',"card_type":',
           '"',
           IFNULL(new.card_type, ''),
           '"',
           ',"user_id":',
           '"',
           IFNULL(new.user_id, ''),
           '"',
           ',"is_like":',
           '"',
           IFNULL(new.is_like, ''),
           '"',
           ',"has_rated":',
           '"',
           IFNULL(new.has_rated, ''),
           '"',
           ',"rate":',
           '"',
           IFNULL(new.rate, ''),
           '"',
           ',"created_by":',
           '"',
           IFNULL(new.created_by, ''),
           '"',
           ',"created_date":',
           '"',
           IFNULL(new.created_date, ''),
           '"',
           ',"card_type":',
           '"',
           IFNULL(new.card_type, ''),
           '"',
           '}'),
    new.created_by,
    new.created_date)

It checks for the null value of the field.

Collectives™ on Stack Overflow

Trigger MySql while using CONCAT inserting data is Null

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related