Re: use triggers to enforce interigity

From: Jingyan <jingyan.xu_at_tst2000.com>
Date: Mon, 29 Nov 1999 22:24:37 -0800
Message-ID: <Q1K04.1770$LK3.67900@nuq-read.news.verio.net>

Hi,
Do all of you think that I don't do my homework? Below I attach it, which I am almost done. Only one thing is when

update course
set course#='cs123'
where course#='cs457';

I got errors because tiggers to check parent tables. So I think I got in a wrong direction and come here, but...

Jingyan Xu
I posted several times the attach file but it failed. so I copy it below:

Assignment6

/*3.create tables/
Create TABLE STUDENTS (
Student# number(4) not null,
FirstName varchar2(20)not null,
LastName varchar2(20)not null,
Major varchar2(10) ,
Age number(2) ,
street varchar2(30) ,
city varchar2(20) ,
state char(2) ,
zip number(5)
);

create Table COURSE (
Course# varchar(5) not null,
CourseTitle varchar(20)
);

create TABLE INSTRUCTOR (
Instructor# number(4) not null,
FirstName varchar2(20) not null,
LastName varchar2(20) not null
);

create TABLE STUDENTCOURSE (
Student# number(4) not null,
Course# varchar(5) not null,
section char(1) not null,
Semester varchar(10) not null
);

create TABLE instructor_ta_course (
Course# varchar(5) not null,
section char(1) not null,
Semester varchar(10) not null,
Instructor# number(4) ,
Room varchar(4),
TA_Student# number(4)
);

/********* below is data for testing **********/ insert into students values(1000,'a','b',  null , null, null, null, null, null);
insert into students values(1001,'c','d',  null , null, null, null, null, null);
insert into course values('cs457','database design'); insert into course values('cs569','advance design'); insert into instructor values(0001,'John','Smith'); insert into instructor values(0002,'Don','Ma');

insert into instructor_ta_course
values('cs569','B','98fall',0001,'a101',1000); insert into instructor_ta_course

values('cs457','C','98fall',0002,'a101',1001);
insert into studentcourse values(1000,'cs457','B','98fall');
insert into studentcourse values(1001,'cs457','C','98fall');

update course
set course#='cs123'
where course#='cs457';

/**************************************************/

/*4. Indentify all primary keys and foreign keys, and create constraints to enforce referential integrity./

ALTER TABLE STUDENTS
add (constraint S_PK PRIMARY KEY (Student#));

ALTER TABLE COURSE
add (constraint C_PK PRIMARY KEY(Course#));

ALTER TABLE INSTRUCTOR
add (constraint I_PK PRIMARY KEY(instructor#));

ALTER TABLE instructor_ta_course
add (constraint SEMC_PK PRIMARY KEY (Course#,section,Semester), constraint SEMC_FK1 FOREIGN KEY (Course#) REFERENCES COURSE(Course#),
constraint SEMC_FK2 FOREIGN KEY (TA_Student#) REFERENCES STUDENTS(Student#),
constraint SEMC_FK3 FOREIGN KEY (Instructor#) REFERENCES Instructor(Instructor#)
);

ALTER TABLE STUDENTCOURSE
add (constraint SC_PK PRIMARY KEY(Student#,Course#,section,Semester), constraint SC_FK1 FOREIGN KEY (Student#) REFERENCES STUDENTS(Student#),
constraint SC_FK2 FOREIGN KEY (Course#,section,Semester) REFERENCES instructor_ta_course(Course#,section,Semester)
);

/*5.Write statements to drop the constraints created in 4/

ALTER TABLE STUDENTCOURSE
drop constraints SC_PK;
ALTER TABLE STUDENTCOURSE
drop constraints SC_FK1;
ALTER TABLE STUDENTCOURSE
drop constraints SC_FK2;

ALTER TABLE instructor_ta_course
drop constraints SEMC_PK;
ALTER TABLE instructor_ta_course
drop constraints SEMC_FK1;
ALTER TABLE instructor_ta_course
drop constraints SEMC_FK2;
ALTER TABLE instructor_ta_course
drop constraints SEMC_FK3;

ALTER TABLE STUDENTS
drop constraints S_PK;

ALTER TABLE COURSE
drop constraints C_PK;

ALTER TABLE INSTRUCTOR
drop constraints I_PK;

/*6. Create Trigger statements to enforce referential integrity/ Create or replace trigger STUDENT_DU_ROW after delete or update of student# on STUDENTS for each row
begin
 IF DELETING THEN
  delete from STUDENTCOURSE
  where student# = :old.student#;
  update instructor_ta_course
  set TA_student#=null
  where TA_student# = :old.student#;
 END IF;
 IF UPDATING THEN
  update STUDENTCOURSE
  set student#= :new.student#
  where student#= :old.student#;
  update instructor_ta_course
  set TA_student#= :new.student#
  where TA_student#= :old.student#;

 END IF;
end;
/

Create or replace trigger COURSE_DU_ROW after delete or update of course# on COURSE for each row
begin
 IF DELETING THEN
  delete from instructor_ta_course
  where course#= :old.course#;
 END IF;
 IF UPDATING THEN
  update instructor_ta_course
  set course#= :new.course#
  where course#= :old.course#;
 END IF;
end;
/
Create or replace trigger instructor_DU_ROW after delete or update of instructor# on INSTRUCTOR for each row
begin
 IF DELETING THEN
  delete from instructor_ta_course
  where instructor#= :old.instructor#;
 END IF;
 IF UPDATING THEN
  update instructor_ta_course
  set instructor# = :new.instructor#
  where instructor#= :old.instructor#;
 END IF;
end;
/

/* trigger for SemesterCourse table */
Create or replace trigger instructor_ta_course_DIU_ROW before delete or insert or update
of course#,section,semester on instructor_ta_course for each row
declare

 trigger_error_course EXCEPTION;
 trigger_error_student EXCEPTION;
 trigger_error_instructor EXCEPTION;

 test_exist number;
begin
 if inserting then
  select count(*) into test_exist from course    where course#= :new.course#;
   IF( test_exist < 1 ) THEN
     RAISE trigger_error_course;
   END IF;
 select count(*) into test_exist from students    where student#= :new.ta_student#;
   IF( test_exist < 1 ) THEN
     RAISE trigger_error_student;
   END IF;
 select count(*) into test_exist from instructor    where instructor#= :new.instructor#;    IF( test_exist < 1 ) THEN
     RAISE trigger_error_instructor;
   END IF;
 end if;

 if updating then
/********** need to check if :new.course# exist in parents table ???****/  update studentcourse
 set course#=:new.course#,section=:new.section,   semester=:new.semester
 where course#=:old.course# and section=:old.section   and semester=:old.semester;
 end if;
 if deleting then
 delete from studentcourse
 where course#=:old.course# and
  section=:old.section and semester=:old.semester;  end if;
EXCEPTION
 WHEN trigger_error_course THEN
 RAISE_APPLICATION_ERROR(-20001,
  'No such course# in course table');
 WHEN trigger_error_student THEN
 RAISE_APPLICATION_ERROR(-20002,
  'No such student# in students table');  WHEN trigger_error_instructor THEN
 RAISE_APPLICATION_ERROR(-20003,
  'No such instructor# in instructor table'); end;
/

/* trigger for sudentcourse table */

Create or replace trigger sudentcourse_IU_ROW before insert or update of student#,course#,  section,semester on studentcourse
for each row
declare
 trigger_error_ins_ta_course EXCEPTION;  trigger_error_student EXCEPTION;
 test_exist number;
begin
/******* to check if :new.course#,section,  semestor exist in parents table ****/

  select count(*) into test_exist from instructor_ta_course    where course#= :new.course# and
 section=:new.section and semester=:new.semester;    IF( test_exist < 1 ) THEN
     RAISE trigger_error_ins_ta_course;    END IF;
 select count(*) into test_exist from students    where student#= :new.student#;
   IF( test_exist < 1 ) THEN
     RAISE trigger_error_student;
   END IF; EXCEPTION
 WHEN trigger_error_ins_ta_course THEN
 RAISE_APPLICATION_ERROR(-20001,
  'No match course#,section,and semestor in ins_ta_course table');  WHEN trigger_error_student THEN
 RAISE_APPLICATION_ERROR(-20002,
  'No such student# in students table'); end;
/

drop table studentcourse;
drop table instructor_ta_course;
Drop table students;
drop table course;
drop table instructor; Received on Tue Nov 30 1999 - 00:24:37 CST