如何在触发器中放置多个更新? [英] How to put multiple updates in a trigger?

问题描述

希望您能在这里为我提供帮助. 我正在使用MySQL + PhpMyAdmin，并且在此问题中有2个表.

I hope you can help me here. I am using MySQL + PhpMyAdmin and I have 2 tables in this problem.

表1:帐户-ID，帐户名，网站等...
表2:域-id，domain_name，account_name

Table 1: Accounts - id, account_name, website, etc. etc...
Table 2: Domains - id, domain_name, account_name

然后我将这些查询插入到2个触发器中.

and I Inserted these queries into 2 triggers.

更新前
更新域，帐户
设置domains.account_name = NULL
其中account.website！= domains.domain_name

Before Update
update domains, accounts
set domains.account_name = NULL
where accounts.website != domains.domain_name

更新后
更新域，帐户
设置domains.account_name = accounts.account_name
其中domains.domain_name = main_accounts.website

After Update
update domains, accounts
set domains.account_name = accounts.account_name
where domains.domain_name = main_accounts.website

有了这些，当我更新accounts表中的内容时，它将自动从domains表中删除account_name并在更新某个帐户时放置新的account_name.

With these, when I update something in the accounts table, it will automatically remove the account_name from domains table and put new account_name if a certain account is updated.

下面的图像将显示示例.
表格尚未更新:
----------
帐户表

Images below will show example.
Tables not yet updated:
----------
Accounts Table

域表
----------
----------
更新后的视图.
----------
----------
帐户表

Domains Table
----------
----------
Updated View.
----------
----------
Accounts Table

域表

因此，在第二个帐户表图像上，我更改了域，并且域表已自动更新.我只想使1个触发器包含这2个更新查询.我不知道是否可能，因为完成此操作后，我可能还需要知道如何从1个触发器更新多个不同的表.我问的原因是因为我需要为正在使用的每个子表分配帐户名.例如，主表是会计科目表，而需要更新的子表是:
domains.account_name
ip_address.account_name
phone.account_name
payments.account_name

So, on the second account table image, I have changed the domain and automatically, the domains table has been updated. I would want to make only 1 trigger that will contain those 2 update queries. I don't know if it's possible because after I complete this, I may also need to know how to update multiple different tables from 1 trigger. The reason why I ask is because I need to assign the account name for each sub-tables that are in use. Example, the main table is the accounts table and the sub tables that needs to be updated are:
domains.account_name
ip_address.account_name
phones.account_name
payments.account_name

因此，我不知道在更新主表"accounts"时是否甚至可以更新"account_name"列下的那些子表.

So, I don't know if it's even possible to update those sub-tables under column "account_name" when the main table "accounts" is updated.

谢谢.希望我的问题清楚. :D:D

Thank you. I hope my question is clear. :D :D

推荐答案

您可以使用BEGIN和END将多个语句分组为一个触发器.

You can group multiple statements into a trigger with BEGIN and END.

示例:

DELIMITER $$

CREATE TRIGGER my_trigger BEFORE INSERT ON accounts
FOR EACH ROW
BEGIN
    -- Statement one
    UPDATE domains
    SET domains.account_name = accounts.account_name
    WHERE domains.domain_name = main_accounts.website;
    -- Statement two
    UPDATE another_table
    SET another_table.column_name = accounts.account_name
    WHERE another_table.domain_name = accounts.some_column;
    -- More UPDATE statements
END$$

这篇关于如何在触发器中放置多个更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！