1.编写一个（teacher_update）的触发器，当修改lesson_info的course_id时，该列在teacher_info表中的对应值也相应改变；
语句如下：
     use student
go
create trigger trigger_name
on lesson_info
after update
as
begin
declare @newcourse_id nvarchar(50)
declare @oldcourse_id nvarchar(50)
select @newcourse_id=course_id from inserted
select @oldcourse_id=course_id from deleted
update  teacher_info set course_id=@newcourse_id
where course_id=@oldcourse_id
end
 
2.编写一个teacher_delete 触发器,当删除lesson_info表中的记录时，也同时删除改记录course_id列值在teacher_info表中的记录
语法如下：
use student<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

go

create trigger teacher_delete

on   lesson_info

for delete

as

begin

  declare @course_id char(20)

   select @course_id=(select course_id from deleted)

if @course_id  in (select teacher_info.course_id from teacher_info,deleted

    where teacher_info.course_id=deleted.course_id)

 

   delete from teacher_info where course_id=@course_id

 

end