用一个触发器,同时更新子表create trigger name_tri before update on tablea for each row begin update tableb set table_col:=:new.table_col where id=:new.id; end; /
declare strsql varchar2(1000); sec_balc number; begin for sec_balc in 500..1000 loop strsql := 'update table_name set table_col = table_col + 40 where table_col = :sec_balc'; execute immediate strsql using sec_balc; end loop; commit; end;
update dept
*
ERROR 位于第 1 行:
ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录日志
(select id + 40 from tab1);
commit ;
commit;
deptno有10,20,30,40四行
现在要用一句话将四行中的10,20,30,40改为40,50,60,70,怎么办?
before update on tablea
for each row
begin
update tableb set table_col:=:new.table_col where id=:new.id;
end;
/
strsql varchar2(1000);
sec_balc number;
begin
for sec_balc in 500..1000 loop
strsql := 'update table_name set table_col = table_col + 40 where table_col = :sec_balc';
execute immediate strsql using sec_balc;
end loop; commit;
end;
这是每个部门号加40吗,不是加30吗?而且你更改前后的部门号40这里有重复,需要先将部门号为40的先更改,然后才能用他们的SQL,这样才不会出现主键冲突。
DEPTNO
DEPTNAME
LOCATION假设有主键PK_DEPTNO(DEPTNO),且表DEPT无子表
那么在UPDATE之前先使PK_DEPTNO(DEPTNO)失效ALTER TABLE DEPT DISABLE CONSTRAINT PK_DEPTNO;UPDATE DEPT SET DEPTNO = DEPTNO + 40;ALTER TABLE DEPT ENABLE CONSTRAINT PK_DEPTNO;