MySQL触发器-比较两个不同表的值

Question

我目前正努力在MySQL中实现触发器。我的场景如下：

有两个数据库(emailregistration&answers(以不同的id集作为主键。因此，我想使用电子邮件地址作为标识符来识别需要更新的正确行。(两个表中相同(我想在数据库"注册"更新时启动一个触发器。此触发器随后更新"users"中的值。(表示为已确认/未订阅(

根据我的理解，问题是没有检查正确的行。我下面的代码直接取自phpmyadmin。在其当前形式中，该语句用空行填充答案数据库，并根据需要更改已确认/未订阅的列，但未标识正确的行。

AFTER
UPDATE
------
INSERT INTO answers (confirmed, unsubscribed)
SELECT emailregistration.confirmed, emailregistration.unsubscribed
FROM emailregistration
WHERE emailregistration.email = email
-----
root@localhost
*This should translate into the following trigger code:*
CREATE TRIGGER DOI 
AFTER UPDATE
ON emailregistration
FOR EACH ROW
BEGIN
INSERT INTO answers (confirmed, unsubscribed)
SELECT emailregistration.confirmed, emailregistration.unsubscribed
FROM emailregistration
WHERE emailregistration.email = email
END;

我希望我准确地描述了我的问题。如果我没有，请随意提问。非常感谢你的帮助。

Answer 1

也许有类似的东西

drop table if exists r,u;
create table r(email varchar(3), subscribed varchar(1), confirmed varchar(1));
create table u(email varchar(3), subscribed varchar(1), confirmed varchar(1));
insert into r (email) values('aaa'),('bbb');
insert into u (email,subscribed,confirmed) values('aaa','x','x'),('bbb','y','y');
drop trigger if exists t;
delimiter $$
CREATE TRIGGER t 
AFTER UPDATE
ON r
FOR EACH ROW
BEGIN
update u 
set  subscribed = new.subscribed,
confirmed = new.confirmed
where email = new.email;
END $$
delimiter ;
update r set subscribed = 'n' where email = 'aaa';
select * from r;
+-------+------------+-----------+
| email | subscribed | confirmed |
+-------+------------+-----------+
| aaa   | n          | NULL      |
| bbb   | NULL       | NULL      |
+-------+------------+-----------+
2 rows in set (0.00 sec)
select * from u;
+-------+------------+-----------+
| email | subscribed | confirmed |
+-------+------------+-----------+
| aaa   | n          | NULL      |
| bbb   | y          | y         |
+-------+------------+-----------+
2 rows in set (0.00 sec)

如果这不是你所拥有的模型，那么将其与预期结果一起添加到问题中。