代码之家 › 专栏 › 技术社区 › Finn Kimbrel

如何在SQL触发器中泛化“WHERE”子句

where-clause triggers stored-procedures sqlite sql

Finn Kimbrel · 技术社区 · 1 年前

我是SQL的新手,现在正在练习创建触发器函数。目前,我正在尝试创建一个存储过程,以便在销售或库存更新时更新产品记录。现在,它看起来是这样的:

CREATE TRIGGER IF NOT EXISTS after_sale_insert
    AFTER insert ON sales
BEGIN
    INSERT INTO log_messages (message) VALUES 
    ('new sale record created and product quantity updated');
    UPDATE products
    SET quantity = (
        SELECT products.quantity - sales.quantity_sold FROM products
        JOIN sales ON products.product_id = sales.product_id)
    WHERE products.product_id = sales.product_id;
END;

触发器一直工作到“WHERE”元素,我试图用它来指定Products表中的产品ID应该与触发命令输入的产品ID匹配(Products.product_ID=[插入“Sales”中的任何product_ID])。我正在使用以下命令进行测试:

INSERT INTO sales (product_id, quantity_sold) VALUES (1, 20);

不过,我现在的做法只是让我的程序(SQLite)返回一个错误(“no such column:sales.product_id”)。总之:我很难找到如何创建一个“WHERE”语句,该语句可以在SQLite中普遍应用并仍然有效。有什么帮助吗?

我的表格的完整代码如下:


 CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    price REAL NOT NULL
);

 CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    quantity_sold INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE stock_updates (
    update_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    new_quantity INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE log_messages (
    id INTEGER PRIMARY KEY,
    message TEXT
);

INSERT INTO products (product_name, quantity, price) VALUES
    ('Product A', 100, 50.00),
    ('Product B', 50, 70.00),
    ('Product C', 200, 30.00);

我试着摆弄JOIN元素,并在语句的各个部分添加“old.”前缀,但这并没有起到任何作用。如果我去掉“WHERE”语句,那么触发器在技术上是有效的(因为它不会返回错误),但它会将所有产品的数量更改为20。这是我的测试命令插入Sales表中的数量,但绝对不是它应该做的。

在完全消除了“WHERE”语句并将“SET”语句更改为“SET quantity=(products.quantity-sales.quantity_seld)FROM products JOIN sales ON products.product_id=sales.product_id”后,我得到了错误“不明确的列名:products.quantify”。这很令人兴奋,但也不是我想要的解决方案。

1 回复 | 直到 1 年前

Joel Coehoorn 1 年前

看起来Sqlite需要您使用 the special New table :

WHEN子句和触发器操作都可以使用“NEW.Columname”和“OLD.column-name”形式的引用来访问正在插入、删除或更新的行的元素

请注意,有些数据库不能保证每次单独的更改都会触发触发器,但为了提高性能,可能会将多个数据库批处理在一起。因此,要小心假设触发器中只有一行。

此外,在大多数平台上,触发器不能保证与“main”语句是原子的,因此您必须小心不要设置竞争条件。通常情况下,最好将两个独立的连续语句封装在一个事务中来做这种事情。