来自触发器的结果集MySQL
问题描述:
这是我第一次使用触发器,我遇到了一些麻烦。 我正在创建一个通知系统,当新通知被创建时,它将向notify
表添加一行。添加的内容取决于它的通知类型。来自触发器的结果集MySQL
到目前为止,我已经试过:
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 ;
我搜索周围,然后将其改为:
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 ;
我也尝试了混合以上2和所有我得到要么不能返回结果集,要么我的select查询出现语法错误。
(另外,我假设NEW
关键字已经预先设定的触发?)
答
你的第一次尝试实际上是在正确的轨道上,但你没有你的SELECT
的结果写入到一个变量。从触发器返回结果集是不可能的。
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 ;
感谢您的帮助,@fancyPants,它的工作就像一个魅力! :) – 2014-09-02 10:36:48