触发器小问题？想了很久很久

[thesecretblue]

说明： 学生表： 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的更新时却不知该怎么写，怎么写都错？（触发器不能建立），但只能建立一个触发器完成三个表的跟新来保持数据库的一致性。
请教高手？？？

[zhangwonderful]

参考sql:
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;

[crazylaa]

楼上的似乎有争议，专业表的总学分数应该是不会被修改的。不能因为加了个学生，就把该专业需要的总学分数加一个值，而应该只加该专业的学生人数就好了吧？

个人意见，仅供参考。
相关只修改学生人数的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; 
/

[thesecretblue]

insert into major_stats(‘Computer Science’,0,2); ......开始输入的

当我按照2为楼主的帮助下，
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呢？？？

[thesecretblue]

感谢，解决了