Error in Running Sql Trigger

Question

I am trying to execute this. It is giving me error for mysql syntax. Not able to figure out why.

CREATE TRIGGER trig1
AFTER INSERT OR UPDATE ON table1 
FOR EACH ROW 
INSERT INTO table2 (id,entity_type,`data`,processed,created_at,last_updated_at ) 
VALUES ( DEFAULT , 'campaigns', 
'{"campaign_id":"' CONCAT NEW.campaign_id CONCAT '","name":"' CONCAT NEW.name CONCAT '","advertiser_id":"' CONCAT NEW.advertiser_id CONCAT '","start_date":"' CONCAT NEW.start_date 
CONCAT '","end_date":"' CONCAT NEW.end_date CONCAT '","status":"' CONCAT NEW.status CONCAT '","pacing":"' CONCAT NEW.pacing CONCAT '","budget_value":"' CONCAT NEW.budget_value 
CONCAT '","gross_budget":"' CONCAT NEW.gross_budget CONCAT '"}' ,
0, CURRENT_TIMESTAMP , CURRENT_TIMESTAMP) ;

Answer 1

You have wrongly used CONCAT in the statement.

It should be as shown in the statement below:

VALUES ( DEFAULT , 'campaigns',   
CONCAT( '{"campaign_id":"', NEW.campaign_id,
        '","name":"', NEW.name,
        '","advertiser_id":"', NEW.advertiser_id,
        '","start_date":"', NEW.start_date,
        '","end_date":"', NEW.end_date,
        '","status":"', NEW.status,
        '","pacing":"', NEW.pacing,
        '","budget_value":"', NEW.budget_value,
        '","gross_budget":"', NEW.gross_budget,
        '"}' ),  
0, CURRENT_TIMESTAMP , CURRENT_TIMESTAMP ) ;

Reference to Documentation:

• CONCAT(str1,str2,...)
  • Returns the string that results from concatenating the arguments. May have one or more arguments.

Answer 2

There are few problems on your trigger, I would start to fix it with something like this:

CREATE TRIGGER trig1
AFTER INSERT ON table1 
FOR EACH ROW
INSERT INTO table2 (entity_type,`data`,processed,created_at,last_updated_at) 
VALUES (
  'campaigns',
  CONCAT(
    '{',
    CONCAT_WS(
      ',',
      CONCAT('"campaign_id":"', NEW.campaign_id, '"'),
      CONCAT('"name":"', NEW.name, '"'),
      CONCAT('"status":"', NEW.status, '"'),
    ),
    '}'
  ),
  0,
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
);

CONCAT will concat the opening { with the values and the closing }.

All values will be calculated using CONCAT_WS that will create a comma separated string of values, and it will ignore null values.

This will make the trigger more readable.