sql - 更新触发器中的多个表，其中一个表用于触发器激活

Question

假设我有两个名为widgetCustomerand的表widgetSale。在插入时，widgetSale我想widgetSale在行中添加时间戳并将销售添加到 id表中。last_order_idwidgetCustomer

我知道使用AFTER INSERT ON会导致尝试更新NEW行时出错，因此我们需要使用BEFORE INSERT ON子句。这提出了一个AUTO_INCREMENT尚未生成待售 id 的新问题，因此 last_order_id 将全部为零。在MySQL/MariaDB TRIGGER有一种方法可以做到这一点，但它似乎在我的系统上失败了（即最后一个订单 ID 仍然为零）。

作为一种解决方法，我使用了两个不同的触发器，一个在插入之前，一个在插入之后。虽然它确实有效，但我很想知道上述方法是否存在可能的缺陷，以及是否有更好的方法来做到这一点（在性能和数据完整性方面）。

我的代码如下：

DROP TABLE IF EXISTS widgetSale;
DROP TABLE IF EXISTS widgetCustomer;
DROP TABLE IF EXISTS widgetLog;

CREATE TABLE widgetCustomer ( id integer primary key AUTO_INCREMENT, name TEXT, last_order_id INT, stamp TEXT );
CREATE TABLE widgetSale ( id integer primary key AUTO_INCREMENT, item_id INT, customer_id INTEGER, quan INT, price INT, stamp TEXT );
CREATE TABLE widgetLog ( id integer primary key AUTO_INCREMENT, stamp TEXT, event TEXT, username TEXT, tablename TEXT, table_id INT);

INSERT INTO widgetCustomer (name) VALUES ('Bob');
INSERT INTO widgetCustomer (name) VALUES ('Sally');
INSERT INTO widgetCustomer (name) VALUES ('Fred');
SELECT * FROM widgetCustomer;

CREATE TRIGGER stampSale BEFORE INSERT ON widgetSale
    FOR EACH ROW BEGIN
        SET NEW.stamp = CURRENT_TIMESTAMP();
    END
    
CREATE TRIGGER stampOnRest AFTER INSERT ON widgetSale
    FOR EACH ROW BEGIN 
        UPDATE widgetCustomer SET last_order_id = NEW.id, stamp = CURRENT_TIMESTAMP()
            WHERE widgetCustomer.id = NEW.customer_id;
        INSERT INTO widgetLog (stamp, event, username, tablename, table_id)
            VALUES (CURRENT_TIMESTAMP(), 'INSERT', 'TRIGGER', 'widgetSale', NEW.id);
    END



INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (1, 3, 5, 1995);
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (2, 2, 3, 1495);
INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (3, 1, 1, 2995);

SELECT * FROM widgetSale;
SELECT * FROM widgetCustomer;
SELECT * FROM widgetLog;

我在 Archlinux 上使用 mariadb 10.6.*。

标签： sqlmariadb