Oracle: Trigger to INSERT into one table changes from separate table

Question

I have two tables, item and item_price_history. I want to create a trigger that inserts records of the old prices after an item's price is changed in the price table. Here are the two tables:

CREATE TABLE item(
item_id DECIMAL(10) NOT NULL,
description VARCHAR(30),
price DECIMAL(10),
PRIMARY KEY (item_id));


CREATE TABLE item_price_history(
item_id DECIMAL(10) NOT NULL,
old_price DECIMAL(10) NOT NULL,
new_price DECIMAL(10) NOT NULL,
date_of_change DATE NOT NULL,
FOREIGN KEY (item_id) references ITEM);

I have tried numerous ways just to get it to compile but this is what I have as the trigger:

CREATE OR REPLACE TRIGGER price_hist
AFTER UPDATE ON item
FOR EACH ROW
WHEN (new.price <> old.price)

BEGIN
INSERT INTO item_price_history(item_id, old_price, new_price, date_of_change)
VALUES (:NEW.item_id, :OLD.price, :NEW.price, SYSDATE);
END;

I receive the following error after updating the price on item:

Error starting at line : 1 in command -
UPDATE item
SET price = 11
WHERE item_id = 1
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-04098: trigger 'KEVIN.PRICE_UPDATE' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
       found to be invalid.  This also means that compilation/authorization
       failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
       disable the trigger, or drop the trigger.

Please let me know what you suggest, I've searched high and low but am not able to find a solution.

did you try SHOW ERRORS TRIGGER price_hist; ? Also why is your error showing name of the trigger like 'KEVIN.PRICE_UPDATE' and in code it is "price_hist", can it be that there are two triggers? — Hatik
– Hatik, Commented Jul 27, 2018 at 4:01

starko · Accepted Answer · 2018-07-27 06:07:33Z

2

This error only indicates a problem with the trigger. You will need to view the USER_ERROR table

select *
from
   user_errors
where
   type = 'TRIGGER'
and
   name = 'price_hist';

Check also SHOW ERRORS TRIGGER price_hist. You should receive more information from this source. Reasons can be many, largely due to errors on the object.

I checked on my schemat and everything went perfectly, so probably something is wrong on your database schema.

Try creating the same tables on another user and see if the problem repeats itself.

answered Jul 27, 2018 at 6:07

starko

1,14911 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle: Trigger to INSERT into one table changes from separate table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related