Hi,
With an Oracle Tutorial I created 2 tables (brands / cars) and a join view (all_cars) based on this 2 tables (
https://www.oracletutorial.com/oracle-view/oracle-updatable-view/ ).
The view "all_cars" is an updatable join view, but only 3 columns are updatable (car_id, car_name, brand_id).
If I try to update the 4th column "brand_name" I got an error ORA-01779 cannot modify a column which maps to a non key-preserved table.
update all_cars
set brand_name = 'BMW' where Car_Id = 1;
Because a legacy code, that I can't remove, try to update this "non-updatable" column. I'd like to create a trigger to remove the column "brand_name" from any update command.
I can't succed to create a trigger on this view.
Does someone has an idea ?
Thanks
------------------------------------------
DDL instruction to create the table BRANDS :
CREATE TABLE brands(
brand_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
brand_name VARCHAR2(50) NOT NULL,
PRIMARY KEY(brand_id)
);
INSERT INTO brands(brand_name)
VALUES('Audi');
DDL instruction to create the table CARS :
CREATE TABLE cars (
car_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
car_name VARCHAR2(255) NOT NULL,
brand_id NUMBER NOT NULL,
PRIMARY KEY(car_id),
FOREIGN KEY(brand_id)
REFERENCES brands(brand_id) ON DELETE CASCADE
);
INSERT INTO cars (car_name,brand_id)
VALUES('Audi R8 Coupe',
1);
INSERT INTO cars (car_name,brand_id)
VALUES('Audi Q2',
1);
INSERT INTO cars (car_name,brand_id)
VALUES('Audi S1',
1);
DDL instruction to create the VIEW :
CREATE VIEW all_cars AS
SELECT
car_id,
car_name,
c.brand_id,
brand_name
FROM
cars c
INNER JOIN brands b ON
b.brand_id = c.brand_id;
Check the updatable columns :
SELECT
*
FROM
USER_UPDATABLE_COLUMNS
WHERE
TABLE_NAME = 'ALL_CARS';
You can intercept DML against views with INSTEAD OF triggers.
These replace an insert/update/delete against the view with the statements you've coded into the trigger.
This enables you to omit the non-key preserved rows the change. For example:
update all_cars
set brand_name = 'BMW',
car_name = 'Audi Q7'
where car_id = 1;
/*
ORA-01779: cannot modify a column which maps to a non key-preserved table
*/
create or replace trigger trig
instead of update on all_cars
for each row
begin
update cars
set car_name = :new.car_name,
brand_id = :new.brand_id
where car_id = :new.car_id;
end;
/
select * from cars;
/*
CAR_ID CAR_NAME BRAND_ID
---------- -------------------- ----------
1 Audi R8 Coupe 1
2 Audi Q2 1
3 Audi S1 1
*/
update all_cars
set brand_name = 'BMW',
car_name = 'Audi Q7'
where car_id = 1;
select * from cars;
/*
CAR_ID CAR_NAME BRAND_ID
---------- -------------------- ----------
1 Audi Q7 1
2 Audi Q2 1
3 Audi S1 1
*/
You could also write the trigger to update the non-key preserved table, for example:
create or replace trigger trig
instead of update on all_cars
for each row
declare
brand brands.brand_name%type;
begin
update cars
set car_name = :new.car_name,
brand_id = :new.brand_id
where car_id = :new.car_id
returning brand_id into brand;
update brands
set brand_name = :new.brand_name
where brand_id = brand;
end;
/
select * from brands;
/*
BRAND_ID BRAND_NAME
---------- --------------------------------------------------
1 BMW
*/
update all_cars
set brand_name = 'VW',
car_name = 'Audi Q7'
where car_id = 1;
select * from brands;
/*
BRAND_ID BRAND_NAME
---------- --------------------------------------------------
1 VW
*/
But you need to take care doing this. If the update changes lots of rows, you could update the same row in the brands table
many
times. This is lots of wasted effort.