I have a table created called "item_price_history" with the following fields:
PKlog_id DECIMAL(10);
FKitem_id DECIMAL(10);
date_changed DATE;
old_price DECIMAL(12);
new_price DECIMAL(12);
I want to pull values into this table from another table created call "item" with the following fields:
PKitem_id DECIMAL(10);
price DECIMAL(12);
I'm looking to create a trigger event that inserts values after or before an update is made to the ITEM table. This would insert values into the "item_price_history" table that take the value of price before the update and insert it into old_price field and take the new price and insert it into the new_price field. My current trigger looks like this:
CREATE OR REPLACE TRIGGER price_change_trg
AFTER UPDATE ON item
FOR EACH ROW
DECLARE
Log_ID_ARG DECIMAL(10);
Item_ID_ARG DECIMAL(10);
Date_Changed_ARG DATE;
Price_ARG DECIMAL(12);
New_Price_ARG DECIMAL(12);
Set Log_ID_ARG := 1;
Set Item_ID_ARG := (Select item_id from Item);
Set Date_Changed_ARG := (Select SYSDATE from Dual);
Set Price_ARG := (Select price from item);
Set New_Price_ARG := select :NEW.price from Item:;
BEGIN
INSERT INTO item_price_history(log_id,item_id,date_changed, Price
VALUES (log_id_ARG, Item_ID_Arg, Date_Changed_ARG, price_arg, new_price_arg);
END;
Can someone please help me create this trigger and understand what I'm doing wrong? I've included an ERD and created the necessary constraints.