说明: 学生表: Id 学号 First_name 名 Last_name 姓 Major 专业Current_credits 当前已得学分CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3) 当前已得学分
)
INSERT INTO STUDENTS (id, first_name, last_name, major, current_credits) VALUES (10000, 'Scott', 'Smith', 'Computer Science', 0);
............................................(其他插入数据省略)
专业表: major 专业 Total_credits 学分数 Total_students 人数CREATE TABLE major_stats (
major VARCHAR2(30),
total_credits NUMBER,
total_students NUMBER);insert into major_stats(‘Computer Science’,0,2);
.......................................课程表
classes开课 Department 开课系别 Course 开课课程
Description 课程名称 max_students 课程容量 current_students 现有学生数量
num_credits 学分 CREATE TABLE classes ( department CHAR(3),
course NUMBER(3),
description VARCHAR2(2000),
max_students NUMBER(3),
current_students NUMBER(3),
num_credits NUMBER(1),
room_id NUMBER(5),
CONSTRAINT classes_department_course
PRIMARY KEY (department, course),
CONSTRAINT classes_room_id
FOREIGN KEY (room_id) REFERENCES rooms (room_id)
);INSERT INTO CLASSES(department, course, description, max_students,
current_students, num_credits, room_id)
VALUES ('HIS', 101, 'History 101', 30, 0, 4, 99999);
...............................................学生选修表 Student_id 学生号
Department 开课系别 Course 开课课程 Grade 分数CREATE TABLE registered_students (
student_id NUMBER(5) NOT NULL,
department CHAR(3) NOT NULL,
course NUMBER(3) NOT NULL,
grade CHAR(1),
CONSTRAINT rs_grade
CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),
CONSTRAINT registeredStudentPK
PRIMARY KEY (student_id,department, course),
CONSTRAINT rs_student_id
FOREIGN KEY (student_id) REFERENCES students (id),
CONSTRAINT rs_department_course
FOREIGN KEY (department, course)
REFERENCES classes (department, course)
);
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10000, 'CS', 102, 'A');
.....................................................
创建触发器:
create trigger updateRegisterStudent3
after insert on registered_students
for each row
declare
v_NumCredits classes.num_credits%type;
begin
select num_credits
into v_NumCredits
from classes
where department=:new.Department
and course=:new.course;
update students
set current_credits=current_credits+v_NumCredits where id=:new.Student_id;
update classes
set current_students=current_students+1 where department=:new.Department and course=:new.Course;update major_stats ???????????????????????不知该怎么跟新专业表中的内容end updateRegisterStudent3;题目要求:
在运行创建表的语句后,数据库中有数据不一致的情况存在,比如在往register_student表中插入数据的时候,相应的学生表的current_credit字段需要更新,classes表中的current_student也应该增加1,major_stats表也需要更新。针对这种数据库不一致的情况,请你写一个程序,将数据库从不一致的状态调整到一直的状态。我自己写的触发器中classes表 和 students表都能自动更新,保持一致性,但是往里面添加major_stats的更新时却不知该怎么写,怎么写都错?(触发器不能建立),但只能建立一个触发器完成三个表的跟新来保持数据库的一致性。
请教高手???
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3) 当前已得学分
)
INSERT INTO STUDENTS (id, first_name, last_name, major, current_credits) VALUES (10000, 'Scott', 'Smith', 'Computer Science', 0);
............................................(其他插入数据省略)
专业表: major 专业 Total_credits 学分数 Total_students 人数CREATE TABLE major_stats (
major VARCHAR2(30),
total_credits NUMBER,
total_students NUMBER);insert into major_stats(‘Computer Science’,0,2);
.......................................课程表
classes开课 Department 开课系别 Course 开课课程
Description 课程名称 max_students 课程容量 current_students 现有学生数量
num_credits 学分 CREATE TABLE classes ( department CHAR(3),
course NUMBER(3),
description VARCHAR2(2000),
max_students NUMBER(3),
current_students NUMBER(3),
num_credits NUMBER(1),
room_id NUMBER(5),
CONSTRAINT classes_department_course
PRIMARY KEY (department, course),
CONSTRAINT classes_room_id
FOREIGN KEY (room_id) REFERENCES rooms (room_id)
);INSERT INTO CLASSES(department, course, description, max_students,
current_students, num_credits, room_id)
VALUES ('HIS', 101, 'History 101', 30, 0, 4, 99999);
...............................................学生选修表 Student_id 学生号
Department 开课系别 Course 开课课程 Grade 分数CREATE TABLE registered_students (
student_id NUMBER(5) NOT NULL,
department CHAR(3) NOT NULL,
course NUMBER(3) NOT NULL,
grade CHAR(1),
CONSTRAINT rs_grade
CHECK (grade IN ('A', 'B', 'C', 'D', 'E')),
CONSTRAINT registeredStudentPK
PRIMARY KEY (student_id,department, course),
CONSTRAINT rs_student_id
FOREIGN KEY (student_id) REFERENCES students (id),
CONSTRAINT rs_department_course
FOREIGN KEY (department, course)
REFERENCES classes (department, course)
);
INSERT INTO registered_students (student_id, department, course, grade)
VALUES (10000, 'CS', 102, 'A');
.....................................................
创建触发器:
create trigger updateRegisterStudent3
after insert on registered_students
for each row
declare
v_NumCredits classes.num_credits%type;
begin
select num_credits
into v_NumCredits
from classes
where department=:new.Department
and course=:new.course;
update students
set current_credits=current_credits+v_NumCredits where id=:new.Student_id;
update classes
set current_students=current_students+1 where department=:new.Department and course=:new.Course;update major_stats ???????????????????????不知该怎么跟新专业表中的内容end updateRegisterStudent3;题目要求:
在运行创建表的语句后,数据库中有数据不一致的情况存在,比如在往register_student表中插入数据的时候,相应的学生表的current_credit字段需要更新,classes表中的current_student也应该增加1,major_stats表也需要更新。针对这种数据库不一致的情况,请你写一个程序,将数据库从不一致的状态调整到一直的状态。我自己写的触发器中classes表 和 students表都能自动更新,保持一致性,但是往里面添加major_stats的更新时却不知该怎么写,怎么写都错?(触发器不能建立),但只能建立一个触发器完成三个表的跟新来保持数据库的一致性。
请教高手???
create trigger updateRegisterStudent3
after insert on registered_students
for each row
declare
v_NumCredits classes.num_credits%type;
v_major students.major%type;
begin
select num_credits
into v_NumCredits
from classes
where department=:new.Department
and course=:new.course;
update students
set current_credits=current_credits+v_NumCredits where id=:new.Student_id;
update classes
set current_students=current_students+1 where department=:new.Department and course=:new.Course;select major into v_major from students where id=:new.Student_id;update major_stats set total_credits = nvl(total_credits,0) + v_NumCredits
total_students=nvl(total_students,0)+1
where major =v_major;end updateRegisterStudent3;
相关只修改学生人数的trigger:create or replace trigger updateRegisterStudent3
after insert on registered_students
for each row
declare
v_NumCredits classes.num_credits%type;
v_major students.major%type;
begin
select num_credits
into v_NumCredits
from classes
where department=:new.Department
and course=:new.course;
update students s set s.current_credits = s.current_credits + v_NumCredits where s.id = :new.Student_id;
update classes c set c.current_students = c.current_students+1 where c.department=:new.Department and c.course=:new.Course;
select major into v_major from students s where s.id = :new.student_id;
update major_stats a set a.total_students = a.total_students + 1 where a.major = v_major;
end updateRegisterStudent3;
/
select total_students from major_stats where major='Computer Science';结果:
SQL> select total_students from major_stats where major='Computer Science';TOTAL_STUDENTS
--------------
2为什么人数没加1呢???