0

I have created a trigger :

DELIMITER $$
CREATE TRIGGER member_enrollment_update AFTER UPDATE ON memberships
FOR EACH ROW
BEGIN

INSERT INTO query_logs (QUERY,shop_id) VALUES ("update memberships set member_enrollment_status="+NEW.member_enrollment_status+" where card_no ='"+NEW.card_no+"'","LR00");

END $$
DELIMITER ;

where data type for the columns are :

member_enrollment_status int(1) DEFAULT 0 ,
card_no varchar(15) NOT NULL

when I execute update ,the executed SQL is :

update memberships set member_enrollment_status= 1 where card_no=3678000022 and member_enrollment_status = 0

The warnings are :

1292 - Truncated incorrect DOUBLE value: '''
1292 - Truncated incorrect DOUBLE value: ' where card_no =''
1292 - Truncated incorrect DOUBLE value: 'update memberships set member_enrollment_status='
In query_logs table, record is inserted but getting only the card_no (3678000022) as value of QUERY column.

please help to solve the error

sunkuet02
  • 2,376
  • 1
  • 26
  • 33
Shahrin
  • 134
  • 1
  • 2
  • 11

1 Answers1

2

Instead of + as concatenation, I used the CONCAT function inside the INSERT INTO statement to solve the problem.

INSERT INTO query_logs (QUERY,shop_id) 
VALUES 
(CONCAT("update memberships set member_enrollment_status=", NEW.member_enrollment_status,  
        "where card_no ='", NEW.card_no, "'"), "LR00");
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Shahrin
  • 134
  • 1
  • 2
  • 11
  • You should use the `QUOTE()` function to wrap the columns you're referencing when building queries with concatenation. It handles `NULL` correctly (which this answer, as written, does not -- a null argument to `CONCAT()` makes the entire expression null) as well as escaping of quotes, backslashes, etc. – Michael - sqlbot May 26 '16 at 11:59