Felipe Andrioli
Felipe Andrioli

Reputation: 15

Instead of trigger is not updating my table

I have to check the new status on a table and, depending this status I need to update another table relationated with the first one.

Original table = origin_table

View = table_view

The View was created with a simple select:

create or replace view table_view as
select * from table

My trigger is basically:

create or replace trigger verify_status
    instead of update on table_view
    referencing old as old new as new
    for each row

begin

    if :new.status in ('A', 'P') then
        update origin_table set status = 'CP' where cod = :new.cod;
    end if;
end;

But my origin_table is not updating, some idea?

Obs: The view_table is a copy of a table with a relationship with the origin_table and not a view of a of the origin table.

ObsII: I had to do that to avoid the mutating table error.

Upvotes: 0

Views: 105

Answers (1)

VN'sCorner
VN'sCorner

Reputation: 1552

I don't see any issue with your code, it works perfectly. Could you check dbfiddle link for more details,

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8bad808cb6d120df37537044081e09b0

Upvotes: 1

Related Questions