create table t1( id int primary key ); create table t2( id int primary key ); create sequence seq_id; declare v_id int; begin select seq_id.nextval into v_id from dual; --产生一个序列值作为t1,t2表的主键 insert into t1 values (v_id); insert into t2 values (v_id); commit; end; /SQL> select * from t1;
ID --------------------------------------- 1 SQL> select * from t2;
ID --------------------------------------- 1
哎不给你代码是不行了,在触发器jiance中实现插入zujiejilu表的主码值怎么修改 --客户表 create table kehu (memberNo char(18) primary key, memberName char(8) not null, memberPassword char(8) not null, membersex char(3) not null, membertel char(13) null, memberaddr nvarchar2(30) null, yizushuliang int not null check((yizushuliang<7) and (yizushuliang>=0)) );--品种表 create table pinzhong (pinzhongNo char(8) primary key ,pinzhongName char(8) not null ,dailyRental Decimal(4,2) not null );--影片表 create table video (videoNo char(8) primary key ,videoName char(20) not null ,price number(4,2) not null ,publishtime char(4) not null ,director nvarchar2(15) not null ,mainactor nvarchar2(15) not null ,leibie char(10) not null ,publishaddr char(10) not null ,amount integer ,pinzhongNo char(8) ,constraint fk foreign key(pinzhongNo) references pinzhong(pinzhongNo) );--租借记录文件 create table zujiejilu (zujieNO char(14) primary key, videoNo char(8), memberNo char(18), zujieamount integer, guihuanamount integer default 0 , dateout date default sysdate, datereturn date default sysdate+7 , yajin decimal(5,2), zhuangtai varchar2(20) default '未付款', shishou number(5,2) default 0.00, foreign key(videoNo) references video(videoNo), foreign key(memberNo) references kehu(memberNo) );--预约记录文件 create table yuyuejilu (orderNo char(14) primary key , videoNo char(8), memberNo char(18), orderamount int, zujieno char(14), foreign key(zujieno) references zujiejilu(zujieno), foreign key(videoNo) references video(videoNo), foreign key(memberNo) references kehu(memberNo) );--租借记录号序列 create sequence seq_zujiejilu increment by 1 start with 1 maxvalue 9999 nocache;--租借记录插入触发器 CREATE OR REPLACE TRIGGER TrigInsertOnZuJie BEFORE INSERT OR UPDATE ON zujiejilu FOR EACH ROW DECLARE vVideoPrice Video.price%TYPE; vDailyRental pinzhong.dailyrental%TYPE; vCurrentTime DATE; n number(10); num1 varchar2(10); BEGIN IF inserting THEN SELECT price INTO vVideoPrice FROM Video WHERE VideoNo = :NEW.VIDEONO; :NEW.YAJIN := vVideoPrice * 2*:new.zujieamount; select seq_zujiejilu.nextval into n FROM dual; num1:='0000'||n; num1:=substr(num1,-4,4); :new.zujieno:='ZJ'||to_char(sysdate,'yyyymmdd')||num1; update video set amount=amount-:new.zujieamount where videoNo = :new.videoNo; update kehu set yizushuliang=yizushuliang+:new.zujieamount where memberNo = :new.memberNo; ELSE CASE :NEW.Zhuangtai WHEN '已付款' THEN :New.shishou := :Old.yajin; WHEN '提前归还' THEN SELECT SYSDATE INTO vCurrentTime FROM dual; SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo); :New.Datereturn := vCurrentTime; :new.guihuanamount:=:new.zujieamount; :NEW.shishou:=(vCurrentTime-:NEW.dateout)*vDailyRental*:new.zujieamount; update video set amount=amount+:new.guihuanamount where videoNo = :new.videoNo; update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo; WHEN '按期归还' THEN SELECT SYSDATE INTO vCurrentTime FROM dual; SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo); :NEW.shishou := (:new.datereturn-:new.dateout)*vDailyRental*:new.zujieamount; :new.guihuanamount:=:new.zujieamount; update video set amount=amount+:new.guihuanamount where videoNo = :new.videoNo; update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo; WHEN '超期归还' THEN SELECT SYSDATE INTO vCurrentTime FROM dual; SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo); :new.shishou:=(7*vDailyRental+(vCurrentTime-:new.dateout-7)*vDailyRental*1.5)*:new.zujieamount; :new.datereturn := vCurrentTime; :new.guihuanamount:=:new.zujieamount; update video set amount=amount+:new.guihuanamount where videoNo = :new.videoNo; update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo; when '损坏丢失' then SELECT SYSDATE INTO vCurrentTime FROM dual; :new.shishou:=:old.yajin; :new.datereturn := vCurrentTime; update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo; END CASE; END IF; END TrigInsertOnZuJie;--当租借影片为不够租借需求时向预约记录文件插入记录的触发器 create or replace trigger jiance before insert on zujiejilu for each row declare n video.amount%TYPE; begin select amount into n from video where videoNo = :new.videoNo; if (:new.zujieamount > n) then insert into yuyuejilu(videoNo, memberno, orderamount) values(:new.videoNo, :new.memberNo, :new.zujieamount - n); :new.zujieamount := n; end if; end jiance;--插入预约记录的触发器 create or replace trigger orderinsert before insert on yuyuejilu for each row declare n number(10); num1 varchar2(10); begin select orderNo_seq.Nextval into n from dual; num1:='0000'||n; num1:=substr(num1,-4,4); :new.orderNo:='YY'||to_char(sysdate,'YYYYMMDD')||num1; update kehu set yizushuliang=yizushuliang+:new.orderamount where memberNo = :new.memberNo; end orderinsert; --orderNo_seq序列的生成 create sequence orderNo_seq increment by 1 start with 1 maxvalue 9999 nocache; --实现yuyuejilu表的记录插入,插入时修改kehu表的yizushuliang create or replace trigger orderinsert before insert on yuyuejilu for each row declare n number(10); num1 varchar2(10); begin select orderNo_seq.Nextval into n from dual; num1:='0000'||n; num1:=substr(num1,-4,4); :new.orderNo:='YY'||to_char(sysdate,'YYYYMMDD')||num1; update kehu set yizushuliang=yizushuliang+:new.orderamount where memberNo = :new.memberNo; end orderinsert; --删除预约记录,并向租借记录文件插入新记录(修改kehu表的yizushuliang,为了防止重复) create or replace trigger delorder after delete on yuyuejilu for each row declare -- local variables here begin update kehu set yizushuliang=yizushuliang-:old.orderamount where memberNo=:old.memberNo; insert into zujiejilu(videoNo,memberNo,zujieamount) values(:old.videono,:old.memberno,:old.orderamount); end delorder;
合过了,在插入数据的时候出错了,错误指向insert into yuyuejilu(videoNo, memberno, orderamount,zujieno) 说什么未找到父项关键字
把jiance触发器改成after insert on zujiejilu 方式行不行?
做了个测试 create table t1( id varchar2(50) primary key, name varchar2(50) );create table t2( id varchar2(50) primary key, t_id varchar2(50), name varchar2(50), foreign key(t_id) references t1(id) );create sequence seq_t1_id increment by 1 start with 1; create sequence seq_t2_id increment by 1 start with 1;create or replace trigger tri_t1 before insert on t1 for each row declare v_id varchar2(50) := '5'; begin select 'id' || seq_t1_id.nextval into v_id from dual; :new.id := v_id; end; /create or replace trigger tri_t11 after insert on t1 for each row begin insert into t2 values (seq_t2_id.nextval, :new.id, '2'); end; / SQL> insert into t1(name) values ('name');
1 row inserted SQL> select * from t1;
ID NAME -------------------------------------------------- id1 name id2 name SQL> select * from t2;
ID T_ID NAME -------------------------------------------------- 1 id1 2 2 id2 2
create table t1(
id int primary key
);
create table t2(
id int primary key
);
create sequence seq_id;
declare
v_id int;
begin
select seq_id.nextval into v_id from dual; --产生一个序列值作为t1,t2表的主键
insert into t1 values (v_id);
insert into t2 values (v_id);
commit;
end;
/SQL> select * from t1;
ID
---------------------------------------
1
SQL> select * from t2;
ID
---------------------------------------
1
--客户表
create table kehu
(memberNo char(18) primary key,
memberName char(8) not null,
memberPassword char(8) not null,
membersex char(3) not null,
membertel char(13) null,
memberaddr nvarchar2(30) null,
yizushuliang int not null check((yizushuliang<7) and (yizushuliang>=0))
);--品种表
create table pinzhong
(pinzhongNo char(8) primary key
,pinzhongName char(8) not null
,dailyRental Decimal(4,2) not null
);--影片表
create table video
(videoNo char(8) primary key
,videoName char(20) not null
,price number(4,2) not null
,publishtime char(4) not null
,director nvarchar2(15) not null
,mainactor nvarchar2(15) not null
,leibie char(10) not null
,publishaddr char(10) not null
,amount integer
,pinzhongNo char(8)
,constraint fk foreign key(pinzhongNo) references pinzhong(pinzhongNo)
);--租借记录文件
create table zujiejilu
(zujieNO char(14) primary key,
videoNo char(8),
memberNo char(18),
zujieamount integer,
guihuanamount integer default 0 ,
dateout date default sysdate,
datereturn date default sysdate+7 ,
yajin decimal(5,2),
zhuangtai varchar2(20) default '未付款',
shishou number(5,2) default 0.00,
foreign key(videoNo) references video(videoNo),
foreign key(memberNo) references kehu(memberNo)
);--预约记录文件
create table yuyuejilu
(orderNo char(14) primary key ,
videoNo char(8),
memberNo char(18),
orderamount int,
zujieno char(14),
foreign key(zujieno) references zujiejilu(zujieno),
foreign key(videoNo) references video(videoNo),
foreign key(memberNo) references kehu(memberNo)
);--租借记录号序列
create sequence seq_zujiejilu increment by 1 start with 1 maxvalue 9999 nocache;--租借记录插入触发器
CREATE OR REPLACE TRIGGER TrigInsertOnZuJie
BEFORE INSERT OR UPDATE ON zujiejilu
FOR EACH ROW
DECLARE
vVideoPrice Video.price%TYPE;
vDailyRental pinzhong.dailyrental%TYPE;
vCurrentTime DATE;
n number(10);
num1 varchar2(10);
BEGIN
IF inserting THEN
SELECT price INTO vVideoPrice FROM Video WHERE VideoNo = :NEW.VIDEONO;
:NEW.YAJIN := vVideoPrice * 2*:new.zujieamount;
select seq_zujiejilu.nextval into n FROM dual;
num1:='0000'||n;
num1:=substr(num1,-4,4);
:new.zujieno:='ZJ'||to_char(sysdate,'yyyymmdd')||num1;
update video set amount=amount-:new.zujieamount where videoNo = :new.videoNo;
update kehu set yizushuliang=yizushuliang+:new.zujieamount where memberNo = :new.memberNo;
ELSE
CASE :NEW.Zhuangtai
WHEN '已付款' THEN
:New.shishou := :Old.yajin;
WHEN '提前归还' THEN
SELECT SYSDATE INTO vCurrentTime FROM dual;
SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo);
:New.Datereturn := vCurrentTime;
:new.guihuanamount:=:new.zujieamount;
:NEW.shishou:=(vCurrentTime-:NEW.dateout)*vDailyRental*:new.zujieamount;
update video set amount=amount+:new.guihuanamount where videoNo = :new.videoNo;
update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo;
WHEN '按期归还' THEN
SELECT SYSDATE INTO vCurrentTime FROM dual;
SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo);
:NEW.shishou := (:new.datereturn-:new.dateout)*vDailyRental*:new.zujieamount;
:new.guihuanamount:=:new.zujieamount;
update video set amount=amount+:new.guihuanamount where videoNo = :new.videoNo;
update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo;
WHEN '超期归还' THEN
SELECT SYSDATE INTO vCurrentTime FROM dual;
SELECT dailyrental INTO vDailyRental FROM pinzhong where pinzhongNo = (select pinzhongNo from video where VideoNo = :NEW.VideoNo);
:new.shishou:=(7*vDailyRental+(vCurrentTime-:new.dateout-7)*vDailyRental*1.5)*:new.zujieamount;
:new.datereturn := vCurrentTime;
:new.guihuanamount:=:new.zujieamount;
update video set amount=amount+:new.guihuanamount where videoNo = :new.videoNo;
update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo;
when '损坏丢失' then
SELECT SYSDATE INTO vCurrentTime FROM dual;
:new.shishou:=:old.yajin;
:new.datereturn := vCurrentTime;
update kehu set yizushuliang=yizushuliang-:new.zujieamount where memberNo = :new.memberNo;
END CASE;
END IF;
END TrigInsertOnZuJie;--当租借影片为不够租借需求时向预约记录文件插入记录的触发器
create or replace trigger jiance
before insert on zujiejilu
for each row
declare
n video.amount%TYPE;
begin
select amount into n from video where videoNo = :new.videoNo;
if (:new.zujieamount > n) then
insert into yuyuejilu(videoNo, memberno, orderamount)
values(:new.videoNo, :new.memberNo, :new.zujieamount - n);
:new.zujieamount := n;
end if;
end jiance;--插入预约记录的触发器
create or replace trigger orderinsert
before insert on yuyuejilu
for each row
declare
n number(10);
num1 varchar2(10);
begin
select orderNo_seq.Nextval into n from dual;
num1:='0000'||n;
num1:=substr(num1,-4,4);
:new.orderNo:='YY'||to_char(sysdate,'YYYYMMDD')||num1;
update kehu set yizushuliang=yizushuliang+:new.orderamount where memberNo = :new.memberNo;
end orderinsert;
--orderNo_seq序列的生成
create sequence orderNo_seq
increment by 1
start with 1
maxvalue 9999
nocache;
--实现yuyuejilu表的记录插入,插入时修改kehu表的yizushuliang
create or replace trigger orderinsert
before insert on yuyuejilu
for each row
declare
n number(10);
num1 varchar2(10);
begin
select orderNo_seq.Nextval into n from dual;
num1:='0000'||n;
num1:=substr(num1,-4,4);
:new.orderNo:='YY'||to_char(sysdate,'YYYYMMDD')||num1;
update kehu set yizushuliang=yizushuliang+:new.orderamount where memberNo = :new.memberNo;
end orderinsert;
--删除预约记录,并向租借记录文件插入新记录(修改kehu表的yizushuliang,为了防止重复)
create or replace trigger delorder
after delete on yuyuejilu
for each row
declare
-- local variables here
begin
update kehu
set yizushuliang=yizushuliang-:old.orderamount where memberNo=:old.memberNo;
insert into zujiejilu(videoNo,memberNo,zujieamount)
values(:old.videono,:old.memberno,:old.orderamount);
end delorder;
create table t1(
id varchar2(50) primary key,
name varchar2(50)
);create table t2(
id varchar2(50) primary key,
t_id varchar2(50),
name varchar2(50),
foreign key(t_id) references t1(id)
);create sequence seq_t1_id increment by 1 start with 1;
create sequence seq_t2_id increment by 1 start with 1;create or replace trigger tri_t1
before insert on t1
for each row
declare
v_id varchar2(50) := '5';
begin
select 'id' || seq_t1_id.nextval into v_id from dual;
:new.id := v_id;
end;
/create or replace trigger tri_t11
after insert on t1
for each row
begin
insert into t2 values (seq_t2_id.nextval, :new.id, '2');
end;
/
SQL> insert into t1(name) values ('name');
1 row inserted
SQL> select * from t1;
ID NAME
--------------------------------------------------
id1 name
id2 name
SQL> select * from t2;
ID T_ID NAME
--------------------------------------------------
1 id1 2
2 id2 2