隨手筆記

1) Create the 'product' table and 'product_price_history' table

CREATE TABLE product_price_history 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) ); 

CREATE TABLE product 
(product_id number(5), 
product_name varchar2(32), 
supplier_name varchar2(32), 
unit_price number(7,2) ); 
2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger 
BEFORE UPDATE OF unit_price 
ON product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES 
(:old.product_id, 
 :old.product_name, 
 :old.supplier_name, 
 :old.unit_price); 
END; 
/ 
3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100