CODEWITHSUNDEEP
sqloracle-databaseplsql
MD. Rejaul Hasan
MD. Rejaul Hasan

Reputation: 176

Is it possible to update 2 columns of 2 tables using triggering in oracle?

I create 3 table whose name are customers,payments and orders.They have a same column name customerNumber.customerNumber is a primary key of customers table and a foreign of payments and orders table.Now I want to make a trigger whose works is to update customerNumber in payments and orders table before updating customerNumber in customers table.

my code is here:

create or replace trigger customers_update 
before update of customerNumber on customers 
for each row
begin
update payments,orders 
set 
payments.customerNumber = :new.customerNumber
orders.customerNumber = :new.customerNumber
where (payments.customerNumber = :old.customerNumber)
and 
(orders.customerNumber = :old.customerNumber);
end;
/

but it shows some problem like this

Error at line 2: PL/SQL: SQL Statement ignored

  1. create or replace trigger customers_update
  2. before update of customerNumber on customers
  3. for each row
  4. begin

So my question is how I fix the problem ?

Upvotes: 1

Views: 970

Answers (2)

Matheus Segalotto
Matheus Segalotto

Reputation: 49

"Oracle doesn't allow updates to two tables in the same statement."

That's true, but if you really like, you can create a view and update the view.

Using a trigger with type INSTEAD OF, you can update both, but still two updates internally, but one update a functional level.

Let me know if you have any questions.

Thanks.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Just use two update statements:

update payments
    set customerNumber = :new.customerNumber
    where payments.customerNumber = :old.customerNumber;

update orders 
    set customerNumber = :new.customerNumber
    where orders.customerNumber = :old.customerNumber;

Oracle doesn't allow updates to two tables in the same statement.

Upvotes: 2

Related Questions