CODEWITHSUNDEEP
mysqlsql
Rwd
Rwd

Reputation: 35200

Resultset from trigger MySQL

This is my first time using trigger and I'm having a bit of trouble. I'm creating a notification system that when a new notification is created it will add a row to the notify table. What is added depends on what notification type it is.

So far I've tried:

DELIMITER $$
DROP TRIGGER IF EXISTS generate_notify $$
CREATE TRIGGER generate_notify
AFTER INSERT 
ON notifications
FOR EACH ROW
BEGIN

SELECT @group := notify_group FROM notification_types WHERE type=NEW.type;
IF (@group <> 1) THEN
INSERT INTO notify (user_id, notification_id) VALUES (NEW.user_reference, NEW.id);
ELSE

INSERT INTO notify (user_id, notification_id) VALUES(
    SELECT ID, NEW.id FROM user_customer WHERE clientID=NEW.user_reference
);
END IF;
END; $$
DELIMITER ;

I searched around and then changed it to:

DELIMITER $$
DROP TRIGGER IF EXISTS generate_notify $$
CREATE TRIGGER generate_notify
AFTER INSERT 
ON notifications
FOR EACH ROW
BEGIN
DECLARE insert_user_id INT(11);
SELECT @group := notify_group FROM notification_types WHERE type=NEW.type;
IF (@group <> 1) THEN
INSERT INTO notify (user_id, notification_id) VALUES (NEW.user_reference, NEW.id);
ELSE
SELECT ID INTO insert_user_id FROM user_customer WHERE clientID=NEW.user_reference;
INSERT INTO notify (user_id, notification_id) VALUES(insert_user_id, NEW.id);
END IF;
END; $$
DELIMITER ;

I've also tried mixing the above 2 up and all I'm getting is either can not return result set or there is a syntax error with my select query.

(Also, I'm assuming that the NEW keyword is predefined for triggers?)

Upvotes: 1

Views: 187

Answers (1)

fancyPants
fancyPants

Reputation: 51928

Your first try was actually on the right track, but you did not write the result of your SELECT into a variable. Returning a result set from a trigger is not possible.

DELIMITER $$
DROP TRIGGER IF EXISTS generate_notify $$
CREATE TRIGGER generate_notify
AFTER INSERT 
ON notifications
FOR EACH ROW
BEGIN

DECLARE v_group INT;

SELECT notify_group INTO v_group FROM notification_types WHERE type=NEW.type;
IF (v_group <> 1) THEN
    INSERT INTO notify (user_id, notification_id) VALUES (NEW.user_reference, NEW.id);
ELSE
    INSERT INTO notify (user_id, notification_id) VALUES(
    SELECT ID, NEW.id FROM user_customer WHERE clientID=NEW.user_reference
);
END IF;
END $$
DELIMITER ;

Upvotes: 1

Related Questions