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$$
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...
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$$
Mark as Answer the posts that helped you.
Marked as answer by badzeugene on Apr 04, 2012 05:50 PM
badzeugene
Member
118 Points
154 Posts
how to correct this mysql triggers?
Mar 31, 2012 03:41 PM|LINK
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$$ramiramilu
All-Star
97713 Points
14461 Posts
Re: how to correct this mysql triggers?
Mar 31, 2012 03:55 PM|LINK
That is the problem...NEW is not an Alias, also it should be either a variable...change that to a valid variable...
Thanks,
JumpStart
badzeugene
Member
118 Points
154 Posts
Re: how to correct this mysql triggers?
Mar 31, 2012 04:09 PM|LINK
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...
tdmca
Contributor
2396 Points
661 Posts
Re: how to correct this mysql triggers?
Mar 31, 2012 04:27 PM|LINK
what is NEW
badzeugene
Member
118 Points
154 Posts
Re: how to correct this mysql triggers?
Mar 31, 2012 04:31 PM|LINK
the NEW.post_id should be the post's id that is being Update.. what should i replace it with NEW?
badzeugene
Member
118 Points
154 Posts
Re: how to correct this mysql triggers?
Mar 31, 2012 05:18 PM|LINK
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?
bugwee
Member
354 Points
72 Posts
Re: how to correct this mysql triggers?
Apr 03, 2012 03:22 AM|LINK
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$$