how to correct this mysql triggers?

Question

User-1579957735 posted

Anyone can help me in this? I get an error "ERROR 1054: Unknown column 'post_id' in 'NEW'"

how can i fix this.....?

CREATE
DEFINER=`root`@`localhost`
TRIGGER `meemor`.`addnotification`
AFTER UPDATE ON `meemor`.`posts`
FOR EACH ROW
BEGIN
  DECLARE _user_id BIGINT;
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE notifition_group CURSOR FOR select user_id FROM posts WHERE post_id = NEW.post_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
  
  OPEN notifition_group;
  
  the_loop: LOOP

    FETCH  notifition_group
    INTO   _user_id;
    
    IF no_more_rows THEN CLOSE notifition_group; LEAVE the_loop; END IF;

    
    SET loop_cntr = loop_cntr + 1;

    INSERT INTO notifications (user_id)
        VALUES(_user_id);

  END LOOP the_loop;
  END$$

 

Answer 1

User-691245060 posted

post_id = NEW.post_id;

That is the problem...NEW is not an Alias, also it should be either a variable...change that to a valid variable...

Thanks,

Answer 2

User-1579957735 posted

hi, how can i fix that then? i would like get create a trigger so that if there an update of that post, it will insert a notification to all the users that involved in that post.

that is why  i do this -> DECLARE notifition_group CURSOR FOR select user_id FROM posts WHERE post_id = NEW.post_id;

where NEW.post_id is the post_id of that post that is being modified...

Answer 3

User1929035129 posted

what is NEW

Answer 4

User-1579957735 posted

the NEW.post_id should be the post's id that is being Update.. what should i replace it with NEW?

Answer 5

User-1579957735 posted

DECLARE notifition_group CURSOR FOR select user_id FROM posts WHERE post_id = NEW.post_id;

how can i get the modified post's post_id?

Answer 6

User-638331144 posted

try,

CREATE
DEFINER=`root`@`localhost`
TRIGGER `meemor`.`addnotification`
AFTER UPDATE ON `meemor`.`posts`
FOR EACH ROW
BEGIN
  DECLARE _user_id BIGINT;
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE new_post INT;
  set new_post = NEW.post_id;
  DECLARE notifition_group CURSOR FOR select user_id FROM posts WHERE post_id = new_post;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;
  
  OPEN notifition_group;
  
  the_loop: LOOP

    FETCH notifition_group
    INTO _user_id;
    
    IF no_more_rows THEN CLOSE notifition_group; LEAVE the_loop; END IF;

    
    SET loop_cntr = loop_cntr + 1;

    INSERT INTO notifications (user_id)
        VALUES(_user_id);

  END LOOP the_loop;
  END$$