trigger.sql

来自「该管理系统主要实现了对公寓楼、宿舍、住宿学生相关信息的查询」· SQL 代码 · 共 44 行

drop trigger stu_hotel_delete

create trigger stu_hotel_insert1 on stu_hotel
for insert
as
update hotel_information set hotel_actual_stu=hotel_actual_stu+1
from hotel_information,inserted
where hotel_information.hotel_no=inserted.hotel_no and 
hotel_information.hotel_belong_building=inserted.hotel_belong_building


create trigger stu_hotel_insert2 on stu_hotel
for insert
as
update building_information set building_actual_capacity=building_actual_capacity+1
from building_information,inserted
where building_information.building_no=inserted.hotel_belong_building;

create trigger stu_hotel_delete1 on stu_hotel
for delete 
as
update hotel_information set hotel_actual_stu=hotel_actual_stu-1
from hotel_information,deleted
where hotel_information.hotel_no=deleted.hotel_no and 
hotel_information.hotel_belong_building=deleted.hotel_belong_building;

create trigger stu_hotel_delete2 on stu_hotel
for delete 
as
update building_information set building_actual_capacity=building_actual_capacity-1
from building_information,deleted
where building_information.building_no=deleted.hotel_belong_building;


create trigger stu_information_delete on stu_information
for delete 
as
delete stu_hotel from stu_hotel,deleted
where stu_hotel.stu_no =deleted.stu_no;

insert into stu_hotel values('030330520','12','202','1','')

delete from stu_information
where stu_no ='030310221'