Log Every Price Change
HardDescription
First create the log table:
CodeCREATE TABLE price_log ( log_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, product_id INT, old_price NUMERIC, new_price NUMERIC );
Then create a trigger function f_log_price_change and a trigger trg_log_price_change on
products. Whenever an UPDATE changes a product's price, the trigger adds one row to
price_log with the product_id, the old_price and the new_price. An update that leaves
the price as it was adds nothing.
Then finish with exactly these statements:
CodeUPDATE products SET price = price * 0.9 WHERE category_id = 8; UPDATE products SET stock = stock + 5 WHERE category_id = 8; UPDATE products SET price = 350 WHERE product_id = 5; UPDATE products SET price = 999 WHERE product_id = 26; SELECT product_id, old_price, new_price FROM price_log ORDER BY product_id, new_price DESC;
Tables
| products |
|---|
| product_id (PK) |
| product_name |
| category_id (FK) |
| price |
| stock |