删除数据库记录问题!(所剩分全撒......)

Question

    我建了四张表:Teacher\ Student\ Grade \ SubmitTest，这四张表关系如下Teacher表与Student表主外键关系（字段名：TeacherID），Student表与Grade 表和 SubmitTest表也是主外键关系(字段名：StudentID)
    现我要实现删除Teacher表中一行，问题出现在必须先删除外键引用，也就是从Grade 表和 SubmitTest表开始把关联的StudentID记录删除，再删除Student表中与TeacherID关联的记录，请问如何实现这功能？？？（我想用存储过程实现，请高手过招...）
    

Answer 1

楼上两位兄弟,我之前是这样做!但几条记录删除执行要1分钟,逛慢!能否用动态SQL语句删除?不知怎样下手...晕!

Answer 2

create proc Delete_Teacher(@TeacherID varchar(5))
as
SET XACT_ABORT On
  begin tran
    delete Grade from Grade G,Student S,Teacher T
                 where T.TeacherID=@TeacherID and T.TeacherID=S.TeacherID and GStudentID=S.StudentID
    delete SubmitTest from SubmitTest G,Student S,Teacher T
                 where T.TeacherID=@TeacherID and T.TeacherID=S.TeacherID and GStudentID=S.StudentID
    delete Student from Student S,Teacher T
                 where T.TeacherID=@TeacherID and T.TeacherID=S.TeacherID 
    delete Teacher from Teacher 
                 where TeacherID=@TeacherID
  commit tran

Answer 3

Create proc DelTeacher
@TeacherID varchar(50)
as
     declare @strSql varchar(1000)
    set @strSql= 'delete from Grade where StudentID in (select StudentID from Student where Student.TeacherID='+@TeacherID+')'
    exec(@strSql)
   set @strSql= 'delete from SubmitTest where StudentID in (select StudentID from Student where Student.TeacherID='+@TeacherID+')'
    exec(@strSql)
   set @strSql= 'delete from Student where Student.@TeacherID='+@TeacherID
    exec(@strSql)
   set @strSql= 'delete from Teacher where Teacher.@TeacherID='+@TeacherID
    exec(@strSql)