Thanks for the question, Emmanuel.

Asked: August 23, 2021 - 10:25 am UTC

Last updated: August 25, 2021 - 2:55 pm UTC

Version: 11G R2

Viewed 100+ times

You Asked

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';

and we said...

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.

Rating

(2 ratings)

Comments

Emmanuel, August 24, 2021 - 3:35 pm UTC

Hi Tom,

Your answer half-solves my problem.

I need to remove a colum from the UPDATE statement (either INSERT and DELETE).
But the query is not unique.

For example, if I don't want the column "car_id" to be modify :

update all_cars
set car_name='BMW i3',car_id=21
where car_id=1;

should be change to

update cars
set car_name='BMW i3'
where car_id=1;

and

update all_cars
set brand_id=1,car_id=21
where car_id=1;

should be change to

update cars
set brand_id=1
where car_id=1;

and

update all_cars
set brand_id=1,car_name='BMW i3',car_id=21
where car_id=1;

should be change to

update cars
set brand_id=1,car_name='BMW i3'
where car_id=1;

I can't write the 3 statements in the INSTEAD OF trigger ?

In my case, the table has 135 columns, and I have a lot different "update queries".

Do you think I could build a dynamic update/delete/insert statement by using OLD and NEW object ? or someting else ?

Best regards

August 24, 2021 - 3:59 pm UTC

An instead of trigger intercepts ALL insert/update/delete statements and runs the code in the trigger instead.

I can't write the 3 statements in the INSTEAD OF trigger ?

You can write as many statements as you want!

If you only change some of the columns some of the time, you could change the update to something like this:

  update cars
  set    car_name = nvl ( :new.car_name, car_name ),
         brand_id = nvl ( :new.brand_id, brand_id ),
         another_col = nvl ( :new.another_col, another_col ),
         etc.
  where  car_id = :new.car_id

The big drawback of this is you can no longer set the columns to null (which may or may not be an issue).

You can check which columns have been changed with the UPDATING function and construct dynamic SQL.

Here's an outline to get you started:

create or replace trigger trig
instead of update on all_cars
for each row
declare
  update_stmt clob;
begin
  update_stmt := 'update cars set ';
  if updating ( 'CAR_NAME' ) then
    update_stmt := update_stmt || 'car_name = :car_name, ';
  end if;
  if updating ( 'BRAND_ID' ) then
    update_stmt := update_stmt || 'brand_id = :brand_id, ';
  end if;
  
  update_stmt := rtrim ( update_stmt, ', ' ) || ' where car_id = :car_id';
  
  dbms_output.put_line ( update_stmt );
end;
/

update all_cars
set    car_name = 'Audi Q7'
where  car_id = 1;
/*
update cars set car_name = :car_name,  where car_id = :car_id
*/
update all_cars
set    brand_id = 2
where  car_id = 1;
/*
update cars set brand_id = :brand_id where car_id = :car_id
*/

Very

Emmanuel, August 25, 2021 - 12:50 pm UTC

Hi Tom,

Your answer is very useful. Thans a lot !

Do you think I can build a dynamic quey for DELETE and INSERT statements ?

Does a dynamic query slow the execution ?

Best regards

August 25, 2021 - 2:55 pm UTC

Do you think I can build a dynamic quey for DELETE and INSERT statements ?

Yes

Does a dynamic query slow the execution ?

It depends!

Using dynamic SQL allows you to write more targeted statements (e.g. to only update the changed columns).

Often these will perform better than generalized statements (e.g. that update every column every time, regardless of whether they've actually changed).

But in some cases constructing the dynamic SQL statement can add a reasonable overhead.

Depending on what you're doing dynamic SQL can be harder to write, and you need to take care to avoid SQL injection too. Static SQL also has the advantage of compile-time checks and other optimizations built into PL/SQL.

Breadcrumb

Question and Answer

You Asked

and we said...

Rating

Comments

Very

More to Explore

PL/SQL demos

PL/SQL docs

Remove a column from update on a view

Breadcrumb

Question and Answer

You Asked

and we said...

Rating

Comments

Very

More to Explore

PL/SQL demos

PL/SQL docs