How to generalize a "WHERE" clause in a SQL trigger

Question

I'm very new to SQL and practicing creating trigger functions right now. Currently I'm trying to create a stored procedure to update product records when a sale or stock update occurs. Right now, it looks like this:

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;

The trigger works fine up to the "WHERE" element, which I'm trying to use to specify that the product ID in the Products table should match the product ID being inputted by the triggering command (products.product_id = [whatever product_id is inserted into "Sales"]).

I'm testing it with the following command:

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

The way I have it now, though, just makes my program (SQLite) return an error ("no such column: sales.product_id"). In sum: I'm struggling to figure out how to create a WHERE clause that can be generally applicable and still function in SQLite. Any help?

The full code for my tables are as follows:

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);

I've tried messing around with the JOIN element and adding "old." prefixes to various parts of the statement, which hasn't helped. If I eliminate the "WHERE" statement, the trigger technically works (in that it doesn't return an error), but it changes the quantity for all products to 20. Which is the quantity that was inserted into the Sales table by my test command, but definitely not what it's supposed to do.

Upon eliminating the "WHERE" statement altogether and changing the "SET" statement to "SET quantity = (products.quantity - sales.quantity_sold) FROM products JOIN sales ON products.product_id = sales.product_id" I get the error "ambiguous column name: products.quantity." Which is exciting but not the solution I'm looking for, either.

Answer 1

It looks like Sqlite needs you to use the special New table:

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name"

Note some databases won't guarantee to fire the trigger with each separate change, and may instead batch several events together to improve performance. Therefore, be careful about assuming you only have one row in the trigger.

Also, triggers are not guaranteed to be atomic with the "main" statement on most platforms, so you have to be careful not to setup race conditions. It's often better to do this kind of thing with two separate consecutive statements wrapped in a transaction.