--写触发器:当向借用记录插入一条信息时,如果插入的学生编号在学生信息表中不存在,则也一并插入到学生信息表中
--以下是我写的
create or replace trigger tri_insert
after insert on STUDENT
for each row
begin
if (select stuNum from BOOKUSE where stuNum=:new.stuNum)!=:new.stuNum then
insert into STUDENT(stuNum,stuName) values(:new.stuNum,:new.stuName);
end if;
end;
表:
--学生基本信息表STUDENT
--该表记录了学生信息
--即学号,学生姓名,性别,年龄,家庭地址,系别,入学时间
drop table STUDENT;
create table STUDENT
(
stuNum char(6) primary key,
stuName varchar2(40),
sex char(2) default '男',check (sex in('男','女')),
age int,
address varchar(40),
dept varchar2(40),
enterDate varchar2(10)
);--图书借用记录表BOOKUSE
--该表记录了图书馆中的借用记录
--即书号,学号,学生姓名,借用日期,归还日期
drop table BOOKUSE;
create table BOOKUSE
(
bookUseID varchar2(10),
stuNum char(6),
bookNum varchar2(10) ,
stuName varchar2(40),
borrowDate varchar2(10),
backDate varchar2(10),
constraint pk_bookUseID primary key(bookUseID),
constraint fk_bookNum foreign key(bookNum) references BOOK(bookNum),
constraint fk_stuNum foreign key(stuNum) references STUDENT(stuNum)
);
--要对BOOKUSE创建trigger吧:
create or replace trigger tri_insert
before insert on BOOKUSE
for each row
declare
v_stunum STUDENT.stuNum%type;
begin
begin
select stuNum into v_stunum from STUDENT where stuNum=:new.stuNum and rownum=1;
exception when others then
insert into STUDENT(stuNum,stuName) values(:new.stuNum,:new.stuName);
end;
end;
before insert on BOOKUSE for each row
declare
v_count number;
begin
SELECT count(stuNum) into v_count FROM STUDENT WHERE stuNum=:new.stuNum AND rownum=1;
if v_count=0 then
insert into STUDENT(stuNum,stuName) values(:new.stuNum,:new.stuName);
end if;
end;