create table scott.drv_car
(
Cphm varchar(20) not null,
Cllx varchar(20) not null,
primary key (Cphm)
)create table scott.drv_assess
(
Kgbh varchar(20) primary key,
Kgxm varchar(20)
)
create table scott.drv_car_assess
(
carassessId number(5) not null,
Cphm varchar(20) not null,
Kgbh varchar(20) not null,
time date not null,
primary key(carassessId),
foreign key(Cphm) references scott.drv_car(Cphm) on delete cascade,
foreign key(Kgbh) references scott.drv_assess(Kgbh) on delete cascade
)无论我先更新外键表还是先更新主键表都报错,那应该怎么手动实现级联更新了?谢谢高手帮忙!小弟第一次用oracle被这个搞的郁闷死了!
(
Cphm varchar(20) not null,
Cllx varchar(20) not null,
primary key (Cphm)
)create table scott.drv_assess
(
Kgbh varchar(20) primary key,
Kgxm varchar(20)
)
create table scott.drv_car_assess
(
carassessId number(5) not null,
Cphm varchar(20) not null,
Kgbh varchar(20) not null,
time date not null,
primary key(carassessId),
foreign key(Cphm) references scott.drv_car(Cphm) on delete cascade,
foreign key(Kgbh) references scott.drv_assess(Kgbh) on delete cascade
)无论我先更新外键表还是先更新主键表都报错,那应该怎么手动实现级联更新了?谢谢高手帮忙!小弟第一次用oracle被这个搞的郁闷死了!
2 (
3 Cphm varchar(20)not null,
4 Cllx varchar(20)not null,
5 primary key (Cphm)
6 );Table createdExecuted in 0.187 seconds
SQL> create table drv_assess
2 (
3 Kgbh varchar(20) primary key,
4 Kgxm varchar(20)
5 );Table createdExecuted in 0.015 seconds
SQL> create table drv_car_assess
2 (
3 carassessId number(5) not null,
4 Cphm varchar(20) not null,
5 Kgbh varchar(20) not null,
6 time date not null,
7 primary key(carassessId),
8 foreign key(Cphm) references drv_car(Cphm) on delete cascade,
9 foreign key(Kgbh) references drv_assess(Kgbh) on delete cascade
10 );Table createdExecuted in 0.047 secondsSQL> insert into drv_car select 'a','b' from dual;1 row insertedExecuted in 0 secondsSQL> insert into drv_assess select 'a','b' from dual;1 row insertedExecuted in 0 secondsSQL> insert into drv_car_assess select 1,'a','a',sysdate from dual;1 row insertedExecuted in 0 secondsSQL> 没发现问题呀,只是第三张表有外建需要注意,如果
SQL> insert into drv_car_assess select 1,'c','a',sysdate from dual;insert into drv_car_assess select 1,'c','a',sysdate from dualORA-00001: unique constraint (GUANSHIZHONG.SYS_C008410) violatedSQL因为c不在drv_car里面呀
查了一下好象是使用after each row trigger来实现单条记录的级联更新,但是我才用的oracle那个高手帮忙写个啊