Lets say we have the following:
create view view_1 as
(
select key, data from some_table_or_view;
);
create table table_1
(
key
more_data
);
create view view_2 as
(
select v1.key, v1.data, t1.more_data
from view_1 v1, table_1 t1
where v1.key = t1.key
);
create table table_2 as
(
key
data
more_data
);
create view view_3 as
(
select key, data, more_data from view_2
union
select key, data, more_data from table_2
);
So basically, we have joined two data feeds, one that comes from a join, one that is raw data.
I want to be able to do the following.
update view_3 set more_data = 'BLAH_MORE_DATA' where key = 'BLAH_KEY';
Where this somehow updates either table_1
or table_2
depending whether "BLAH_KEY" comes from table_1
or table_2
.
Currently the only way I can think of is:
create view view_3 as
(
select 'TAB1' as source, key, data, more_data from view_2
union
select 'TAB2' as source, key, data, more_data from table_2
);
and then doing updates using a PL/SQL function which checks the source column and updates the appropriate table, but this means I have to kept the view and the PL/SQL function in sync.
I'm looking for a nicer way to do this.
You can use an instead of trigger on the view_3
CREATE OR REPLACE TRIGGER view3_trg
INSTEAD OF UPDATE
ON view_3
FOR EACH ROW
BEGIN
IF :OLD.source = 'TAB1' THEN
UPDATE table_1 t1
set t1.data = :NEW.data
WHERE
t1.key = :OLD.key
;
ELSIF OLD.source = 'TAB2' THEN
UPDATE table_2 t2
set t2.data = :NEW.data
WHERE
t2.key = :OLD.key
;
END IF;
END;
/
Adapting schurik's answer and taking into account your comment about "else-if-heimer's disease":
CREATE OR REPLACE TRIGGER view3_trg
INSTEAD OF UPDATE
ON view_3
FOR EACH ROW
BEGIN
UPDATE table_1 t1
set t1.data = :NEW.data
WHERE t1.key = :OLD.key;
UPDATE table_2 t2
set t2.data = :NEW.data
WHERE t2.key = :OLD.key;
END;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With