locked
how to correct this mysql triggers? RRS feed

  • 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$$
    

     

    Saturday, March 31, 2012 11:41 AM

Answers

  • 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$$



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 2, 2012 11:22 PM

All replies

  • 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,

    Saturday, March 31, 2012 11:55 AM
  • 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...

    Saturday, March 31, 2012 12:09 PM
  • User1929035129 posted

    what is NEW

    Saturday, March 31, 2012 12:27 PM
  • User-1579957735 posted

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

    Saturday, March 31, 2012 12:31 PM
  • 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?

    Saturday, March 31, 2012 1:18 PM
  • 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$$



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 2, 2012 11:22 PM