CREATE TABLE dept
(dept_id number not null CONSTRAINT dept_pk PRIMARY KEY);

CREATE TABLE student
(s_id number not null CONSTRAINT student_pk PRIMARY KEY,
dept_id number not null,
CONSTRAINT dept_fk_student FOREIGN KEY (dept_id) REFERENCES dept(dept_id));

CREATE TABLE teacher
(t_id number not null CONSTRAINT teacher_pk PRIMARY KEY,
dept_id number not null,
CONSTRAINT dept_fk_teacher FOREIGN KEY (dept_id) REFERENCES dept(dept_id));

CREATE TABLE course
(code number not null CONSTRAINT course_pk PRIMARY KEY,
credit number not null,
dept_id number not null,
CONSTRAINT dept_fk_course FOREIGN KEY (dept_id) REFERENCES dept(dept_id));

CREATE TABLE result
(s_id number not null,
t_id number not null,
code number not null,
semester varchar2(10) not null,
ct number,
mid number,
final number,
attend number,
CONSTRAINT student_fk_result FOREIGN KEY (s_id) REFERENCES student(s_id),
CONSTRAINT teacher_fk_result FOREIGN KEY (t_id) REFERENCES teacher(t_id),
CONSTRAINT course_fk_result FOREIGN KEY (code) REFERENCES course(code));

CREATE TABLE grade
(grad varchar2(2),
start_marks number,
end_marks number,
gp float);

INSERT ALL
	INTO grade (grad, start_marks, end_marks, gp) VALUES('A+',80,100,4)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('A',75,79,3.75)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('A-',70,74,3.50)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('B+',65,69,3.25)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('B',60,64,3.00)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('B-',55,59,2.75)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('C+',50,54,2.50)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('C',45,49,2.25)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('D',40,44,2.00)
	INTO grade (grad, start_marks, end_marks, gp) VALUES('F',0,39,0.00)
SELECT * FROM dual;

CREATE TABLE f_result
(s_id number,
code number,
semester varchar2(10),
total_marks number,
grad varchar2(2),
gpa float);


CREATE OR REPLACE TRIGGER restrict_f_result
BEFORE INSERT OR UPDATE OR DELETE ON f_result
BEGIN
	RAISE_APPLICATION_ERROR(-006,'You can not update/insert/delete the f_result table');
end;
/



CREATE OR REPLACE TRIGGER restrict_result_crt1
AFTER INSERT ON result
for each row
DECLARE
	vtotal number;
	vs_id number;
	vcode number;
	vsemester varchar2(10);
	vgrad varchar2(2);
	vgp float;
	vcredit number;
	vgpa float;
	vt_id number;
BEGIN
	vcode:=:new.code;
	vs_id:=:new.s_id;
	vtotal:= :new.ct + :new.mid + :new.final + :new.attend;
	vsemester:= :new.semester;
	SELECT gp,grad
		INTO vgp,vgrad FROM grade
			WHERE vtotal>=start_marks AND vtotal <=end_marks;
	SELECT credit INTO vcredit FROM course WHERE code=vcode;
	vgpa:=vgp*vcredit;
	INSERT INTO f_result(s_id, code, semester, total_marks,grad,gpa)
    	VALUES (vs_id,vcode,vsemester,vtotal,vgrad,vgpa);
end;
/

CREATE OR REPLACE TRIGGER restrict_result_crt2
AFTER UPDATE ON result
for each row
DECLARE
	vtotal number;
	vs_id number;
	vcode number;
	vsemester varchar2(10);
	vgrad varchar2(2);
	vgp float;
	vcredit number;
	vgpa float;
	vt_id number;
BEGIN
	vcode:=:new.code;
	vs_id:=:new.s_id;
	vtotal:=:new.ct + :new.mid + :new.final + :new.attend;
	vsemester:=:new.semester;
	SELECT gp,grad
		INTO vgp,vgrad FROM grade
			WHERE vtotal>=start_marks AND vtotal<=end_marks;
	SELECT credit INTO vcredit FROM course WHERE code=vcode;
	vgpa:=vgp*vcredit;
	UPDATE f_result
		SET code=vcode, semester=vsemester, total_marks=vtotal,grad=vgrad,gpa=vgpa
    	WHERE s_id=vs_id;
end;
/

CREATE OR REPLACE TRIGGER restrict_result_crt3
Before DELETE ON result
for each row
DECLARE
	vs_id number;
BEGIN
	vs_id:=:new.s_id;
	delete from f_result where s_id=vs_id;
end;
/

















insert into dept(dept_id) values (2345);
insert into student(s_id,dept_id) values (201720,2345);
insert into teacher(t_id,dept_id) values (1017,2345);
insert into course(code,credit,dept_id) values (201,3,2345);
insert into result(s_id, t_id, code, semester, ct, mid, final, attend)
	values (201720,1017,201,'summer', 10, 15, 35, 8);

update result set ct=10, mid=15, final=49, attend=8 where s_id=201720;

insert into f_result(s_id) values (201720);
drop trigger restrict_f_result;

select * from f_result;

system.trg is invalid and failed re-validation