1：增加外键

drop table TeacherUser

create table TeacherUser

(

    Id int identity(1,1) primary key not null,

Name nvarchar not null,

studentID int not null

foreign key (studentID) references testDB.[dbo].[StudentUser](id)

)

alter table testDB.[dbo].TeacherUser

add constraint keyword foreign  key(studentID) references testDB.[dbo].[StudentUser](id)

--更新门票触发器

create trigger tgr_update_admissionstatus

on [dbo].[AdmissionTicketProduct] for update

as

  if(update(Status))

  begin

   declare @status int;

   declare @productId int;

   select @status=inserted.status,@productId=inserted.ID from inserted

   if(@status=2)

     update [dbo].[SpecialProduct] set status=1 where ProductId=@productId and ProductType=16

else

update [dbo].[SpecialProduct] set status=2 where ProductId=@productId and ProductType=16

  end

 go

--创建线路触发器

create trigger tgr_update_linestatus

on [dbo].TourProduct for update

as

  if(update(Status))

  begin

   declare @status int;

   declare @productId int;

   select @status=inserted.status,@productId=inserted.ID from inserted

   if(@status=64)

     update [dbo].[SpecialProduct] set status=1 where ProductId=@productId and (ProductType=1 or ProductType=2)

else

update [dbo].[SpecialProduct] set status=2 where ProductId=@productId and (ProductType=1 or ProductType=2)

  end

 go

 --创建酒店触发器

 create trigger tgr_update_Hotelstatus

on [dbo].HotelProduct for update

as

  if(update(Status))

  begin

   declare @status int;

   declare @productId int;

   select @status=inserted.status,@productId=inserted.ID from inserted

   if(@status=2)

     update [dbo].[SpecialProduct] set status=1 where ProductId=@productId and ProductType=4

else

update [dbo].[SpecialProduct] set status=2 where ProductId=@productId and ProductType=4

  end

 go

 --创建机票触发器

create trigger tgr_update_flightstatus

on [dbo].AirTicket for update

as

  if(update(Status))

  begin

   declare @status int;

   declare @productId int;

   select @status=inserted.status,@productId=inserted.ID from inserted

   if(@status=2)

     update [dbo].[SpecialProduct] set status=1 where ProductId=@productId and ProductType=8

else

update [dbo].[SpecialProduct] set status=2 where ProductId=@productId and ProductType=8

  end

 go