Re: use triggers to enforce interigity

It appears that you first need to enter the cs123 course into the Course table.

Jingyan <jingyan.xu_at_tst2000.com> wrote in message news:Q1K04.1770$LK3.67900_at_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 Wed Dec 01 1999 - 10:47:00 CST