mysql trigger for update not working

Question

i want to convert a field when a insertion takes place in one of my table.

check & ged's => check and geds.

DELIMITER $$
CREATE TRIGGER `perma_limk_insertion` AFTER INSERT
ON `tbl_magazine_subscription`
FOR EACH ROW BEGIN

    DECLARE magazine_name VARCHAR(100); 
    DECLARE magazine_name_from_table VARCHAR(100); 
    DECLARE magazine_name_new VARCHAR(100); 
    SET @magazine_name_from_table := (SELECT MAGAZINE_NAME FROM tbl_magazine WHERE MAGAZINE_ID = NEW.magazine_id LIMIT 1);
    SET @magazine_name            := REPLACE(@magazine_name_from_table,'&','and');
    SET @magazine_name_new        := REPLACE(@magazine_name,"'",'');
    UPDATE tbl_magazine SET perma_link = @magazine_name_new WHERE MAGAZINE_ID = NEW.magazine_id;
END$$
 DELIMITER ;

but using above query they didn't update. if any thing wrong in my query

if any one know about this please help me.

score 0 · Answer 1 · edited May 23 '17 at 11:57

Try this, removed "@" from everywhere. and using INTO when you want to SELECT a value from a table into a variable.

    DELIMITER $$ 
        CREATE TRIGGER perma_limk_insertion AFTER INSERT ON tbl_magazine_subscription FOR EACH ROW BEGIN

            DECLARE magazine_name VARCHAR(100); 
            DECLARE magazine_name_from_table VARCHAR(100); 
            DECLARE magazine_name_new VARCHAR(100); 

  //changed from SET to INTO here
  SELECT MAGAZINE_NAME INTO magazine_name_from_table FROM tbl_magazine
  WHERE MAGAZINE_ID = NEW.magazine_id LIMIT 1;

            SET magazine_name            := REPLACE(magazine_name_from_table,'&','and');
            SET magazine_name_new        := REPLACE(magazine_name,"'",'');
            UPDATE tbl_magazine SET perma_link = magazine_name_new WHERE MAGAZINE_ID = NEW.magazine_id;
        END$$

     DELIMITER ;

To know more aboue session variables and local variable, follow here,

MySQL: @variable vs. variable. Whats the difference?

mysql trigger for update not working

1 Answers1