比较两个不同表的值

b0zn9rqh 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(300)

我目前正努力在mysql中实现触发器。我的设想如下：
有两个数据库（emailregistration&answers），其中一组不同的id作为主键。因此，我想使用email address作为标识符来标识需要更新的正确行（这在两个表中是相同的），我想在更新数据库“registration”时启动触发器。然后，此触发器更新“users”中的值(表示为已确认/未订阅）
据我所知，问题是没有检查正确的行。我下面的代码直接来自phpmyadmin。在其当前形式中，语句用空行填充answers数据库，并根据需要更改confirmed/unsubscribed列，但它没有标识正确的行。

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;

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

mysql phpmyadmin triggers

来源：https://stackoverflow.com/questions/53413489/mysql-trigger-compare-values-of-two-different-tables

1条答案

按热度按时间

oprakyz71#

也许是这样的

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)

如果这不是你的模型，那么把它和预期的结果一起添加到问题中。

赞(0）回复(0）举报 2021-06-19

我来回答

比较两个不同表的值

1条答案

相关问题

热门标签

最新问答