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

ymzxtsji  于 12个月前  发布在  SQLite
关注(0)|答案(1)|浏览(177)

我对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应该与触发命令(products.product_id = [whatever product_id is inserted into "Sales"])输入的产品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_sold)FROM products JOIN sales ON products.product_id = sales.product_id”时,我得到错误“模棱两可的列名:products. quantity”。这令人兴奋,但也不是我正在寻找的解决方案。

jq6vz3qz

jq6vz3qz1#

看起来Sqlite需要你使用the special New table
WHEN子句和触发器操作都可以使用“NEW.column-name”和“OLD.column-name”形式的引用访问被插入、删除或更新的行的元素
请注意,有些数据库不能保证每次单独更改时都触发触发器,而是可能将多个事件批处理在一起以提高性能。因此,请小心假设触发器中只有一行。
此外,在大多数平台上,触发器不能保证与“main”语句是原子的,所以你必须小心不要设置争用条件。通常最好用两个单独的连续语句 Package 在一个事务中来做这类事情。

相关问题