CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Zhamshidbek Abdulkhamidov
Zhamshidbek Abdulkhamidov

Reputation: 45

Failed to fill new columns in table from existing ones

I have cars table. It has a structure like this:

CREATE TABLE cars ( 
  ID NUMBER(38) PRIMARY KEY,
  Manufacturer VARCHAR2(26),
  Model VARCHAR2(26),
  Year NUMBER(38),
  Category VARCHAR2(26),
  Mileage NUMBER(38),
  FuelType VARCHAR2(26),
  EngineVolume VARCHAR2(26),
  DriveWheels VARCHAR2(26),
  GearBox VARCHAR2(26),
  Doors VARCHAR2(26),
  Wheel VARCHAR2(26),
  Color VARCHAR2(26),
  InteriorColor VARCHAR2(26),
  VIN VARCHAR2(26),
  LeatherInterior VARCHAR2(26),
  Price NUMBER(38),
  Clearance VARCHAR2(26),
  Age NUMBER(38),
  PriceInTg NUMBER(38)
 )

I want to fill age and 'PriceInTg' columns that are filled from other columns. So I have created filler functions for it:

CREATE OR REPLACE FUNCTION age_converter(Year NUMBER) 
RETURN NUMBER
IS
v_age Number;
BEGIN
    v_age := EXTRACT(YEAR FROM SYSDATE) - Year;
    RETURN v_age;
END;


CREATE OR REPLACE FUNCTION price_converter(Price NUMBER, currency_in_tg NUMBER)
RETURN NUMBER
IS
price_in_tg NUMBER;
BEGIN 
    price_in_tg := Price  * currency_in_tg;
    RETURN price_in_tg;
END;

To fill these columns automatically I have created triggers:

CREATE OR REPLACE TRIGGER age_filler_trigger
AFTER INSERT ON cars FOR EACH ROW
BEGIN
    INSERT INTO cars (age)
    VALUES (age_converter(:OLD.year));
END;

CREATE OR REPLACE TRIGGER priceTg_filler 
AFTER INSERT ON cars FOR EACH ROW
BEGIN
    INSERT INTO cars (age)
    VALUES (price_converter(:OLD.price, 430));
END;

But when I am inserting value to the table like this:

 INSERT INTO CARS (ID, MANUFACTURER, MODEL, YEAR, CATEGORY, MILEAGE, FUELTYPE, ENGINEVOLUME, DRIVEWHEELS, GEARBOX, DOORS, WHEEL, COLOR, INTERIORCOLOR, VIN, LEATHERINTERIOR, PRICE, CLEARANCE)
 VALUES (0,'BMW','M6',2014,'Coupe',33500,'Petrol','4.4','Rear','Tiptronic','4/5','Left wheel','White','Black','','true',39000,'false');

I got this kind of error:

Error starting at line : 6 in command -
INSERT INTO CARS (ID, MANUFACTURER, MODEL, YEAR, CATEGORY, MILEAGE, FUELTYPE, ENGINEVOLUME, DRIVEWHEELS, GEARBOX, DOORS, WHEEL, COLOR, INTERIORCOLOR, VIN, LEATHERINTERIOR, PRICE, CLEARANCE) VALUES (0,'BMW','M6',2014,'Coupe',33500,'Petrol','4.4','Rear','Tiptronic','4/5','Left wheel','White','Black','','true',39000,'false')
Error report -
ORA-04091: table PROJECT.CARS is mutating, trigger/function may not see it
ORA-06512: at "PROJECT.PRICETG_FILLER", line 2
ORA-04088: error during execution of trigger 'PROJECT.PRICETG_FILLER'

How I can handle the problem?

Upvotes: 0

Views: 36

Answers (1)

William Robertson
William Robertson

Reputation: 16001

Your insert triggers shouldn't be trying to insert new rows. Instead, they should set the :new values, e.g.

:NEW.year := age_converter(:OLD.year);

I was going to suggest making both AGE and PRICE virtual columns, but these can't use sysdate so calculating age on the fly is a problem. It's a problem anyway though, because any value you set will only reflect the age as of the last time the row was updated, which could be any time in the past and so will never be reliable. It might be better to create a view over the cars table, as then you could include an AGE column that would reflect the time of the query.

The "price in tg" column could be virtual:

priceintg number(38) generated always as (price * 430)

Upvotes: 2

Related Questions