17,086
社区成员
发帖
与我相关
我的任务
分享
create table t1 (a char(4) primary key, b number(4))
/
create table t2 (a char(4) primary key, b number(4))
/
insert into t1 values('t100',10);
insert into t2 values('t200',20);
insert into t1 values('t101',20);
insert into t2 values('t201',30);
commit;
create view t_v as
select * from (
select a,b from t1
union all
select a,b from t2
)
/
create or replace trigger update_tv_trg
instead of update on t_v
begin
-- 阻止更改 a 列的 "t1" 或 "t2" 部分
if substr(:old.a,1,2)<>substr(:new.a,1,2) then
raise_application_error(-20001,'Deny update.');
end if;
if substr(:old.a,1,2)='t1' then
update t1 set a=:new.a,b=:new.b where a=:old.a;
elsif substr(:old.a,1,2)='t2' then
update t2 set a=:new.a,b=:new.b where a=:old.a;
end if;
end;
/
-- 测试
update t_v set b=30 where a='t100';
update t_v set b=b-10 where a like 't2%';