【正文】 房主所有的房源信息。create trigger del_homeowner on homeOwner instead of deleteasbegindeclare @id intselect @id=ownerId from deleted delete from houses where ownerId=@iddelete from homeOwner where ownerId=@idend 添加房源的同時(shí)，根據(jù)房源的出租價(jià)格確定房源的類型,800元以下為廉價(jià)型，8002500元為經(jīng)濟(jì)型，2500元以上為奢侈型。create trigger insert_houses on houses for insertasdeclare @curcal cursor set @curcal= cursor for select houseId,zent from insertedopen @curcaldeclare @price money,@houseId varchar(20)Fetch next from @curcal into @houseId,@priceWhile (@@fetch_status=0)begindeclare @style varchar(10)if @price=800 set @style=39。廉價(jià)型39。else if @price=2500 and @price 800 set @style=39。經(jīng)濟(jì)型39。else set @style=39。奢侈型39。update houses set style=@style where houseId=@houseIdFetch next from @curcal into @houseId,@priceendclose @curcaldeallocate @curcal use fwzlxtgo 添加委托合同插入觸發(fā)器，若最低價(jià)格大于房源出租價(jià)格，則操作失敗create trigger insert_auth on authorize instead of insertasdeclare @contractId varchar(20),@houseId varchar(20),@lowestMoney money, @bedate datetime,@dueDate datetime,@ment varchar(100), @bid varchar(10),@rent moneybegin transelect @contractId=contractId,@houseId=houseId,@lowestMoney=lowestMoney,@bedate=bedate, @dueDate=dueDate,@ment=ment,@bid=bid,@rent=rent from insertedinsert into authorize values (@contractId,@houseId,@lowestMoney,@bedate, @dueDate,@ment,39。合同信息39。,@bid,@rent)if @lowestMoney@rent rollback tranelse mit tran存儲(chǔ)過程 修改業(yè)務(wù)員工資的存儲(chǔ)過程，修改后的工資必須滿足一下條件：初級(jí)業(yè)務(wù)員的工資在15002500元，中級(jí)業(yè)務(wù)員的工資在25003500元,高級(jí)業(yè)務(wù)員的工資在3500元以上元。返回值1為不滿足工資條件，2該業(yè)務(wù)員不存在，1修改成功。create proc modifyBusinessman@bno varchar(10),@bgrade varchar(10),@bsalary moneyas begindeclare @salary money,@grade varchar(10)select @salary=bsalary,@grade=bgrade from businessman where bno=@bno。if exists(select * from businessman where bid=@bno)beginif not((@bgrade=39。初級(jí)業(yè)務(wù)員39。 and @bsalary=1500 and @bsalary=2500)or(@bgrade=39。中級(jí)業(yè)務(wù)員39。 and @bsalary=2500 and @bsalary=3500)or(@bgrade=39。高級(jí)業(yè)務(wù)員39。 and @bsalary=3500 and @bsalary=5000)) return 1update businessman set bgrade=@bgrade,bsalary=@bsalary where bid=@bnoend else return 2endgo declare @info varchar(20)exec @info = modifyBusinessman 39。BS0000000139。,39。中級(jí)業(yè)務(wù)員39。,print @info go存儲(chǔ)過程+游標(biāo) 根據(jù)業(yè)務(wù)員一年的業(yè)績(jī)（成功撮合成交所盈利的金額數(shù)）修改業(yè)務(wù)員等級(jí)和工資情況，金額數(shù)大于150000元的工資提高15%，金額數(shù)大于100000元的工資提高10%，金額數(shù)大于50000元的工資提高5%，同時(shí)根據(jù)提升后工資水平修改業(yè)務(wù)員的等級(jí)。if OBJECT_ID(39。addSalary39。,39。P39。)is not null drop proc addSalarygocreate proc addSalary as begindeclare modifySalaryCursor Cursorfor select SUM(Cmoney), from [contract] c join businessman b on = group by ,open modifySalaryCursor。declare @tmp_money money,@tmp_bid varchar(10),@tmp_salary money。fetch next from modifySalaryCursor into @tmp_money,@tmp_bid,@tmp_salary。while @@FETCH_STATUS =0begin print @tmp_money。 print @tmp_bid。 declare @grade varchar(10),@new_salary money。 if @tmp_money=150000 set @new_salary=@tmp_salary* else if @tmp_money150000 and @tmp_money=100000 set @new_salary=@tmp_salary* else if @tmp_money100000 and @tmp_money=50000 set @new_salary=@tmp_salary* else set @new_salary=@tmp_salary if @new_salary=2500 set @grade =39。初級(jí)業(yè)務(wù)員39。 else if @new_salary=3500 set @grade=39。中級(jí)業(yè)務(wù)員39。 else if @new_salary3500 set @grade=39。高級(jí)業(yè)務(wù)員39。 if(@tmp_money!=@new_salary) update businessman set bgrade=@grade,bsalary=@new_salary where bid = @tmp_bid fetch next from modifySalaryCursor into @tmp_money,@tmp_bid,@tmp_salary。endclose modifySalaryCursor。deallocate modifySalaryCursor。endgo測(cè)試exec addSalary檢查并注銷房源信息的存儲(chǔ)過程，檢查房源的委托合同是否到期，如果到期就注銷該條房源。Create proc checkHouseas begin declare checkAuthorize cursorfor select houseId,dueDate from authorize open checkAuthorizedeclare @houseId varchar(20), @dueDate datetimeFetch next from checkAuthorize into @houseId,@dueDatewhile @@FETCH_STATUS=0 begin print @houseId if(@dueDategetdate()) update houses set status=1 where houseId=@houseId Fetch next from checkAuthorize into @houseId,@dueDate endclose checkAuthorizedeallocate checkAuthorizeend根據(jù)房源一段時(shí)間的看房信息情況修改房源的狀態(tài)，是否為熱門房源。一個(gè)月內(nèi)看房數(shù)量達(dá)到5次以上為熱門，一年內(nèi)看房數(shù)量達(dá)到30次以上為熱門房源。Create proc checkIsHOTas begin按月declare checkReports cursor for select 39。次數(shù)39。=count(houseId),houseId from arrangeInfo group by month(adate),houseId 遍歷這張表open checkReportsdeclare @num int,@houseId varchar(20)fetch next from checkReports into @num,@houseIdwhile @@FETCH_STATUS=0 begin if(@num=5) update houses set isHot=1 where houseId=@houseId fetch next from checkReports into @num,@houseIdendclose checkReportsdeallocate checkReports按年declare checkReports cursor for select 39。次數(shù)39。=count(houseId),houseId from arrangeInfo group by year(adate),houseId open checkReportsdeclare @num1 int,@houseId1 varchar(20)fetch next from checkReports into @num1,@houseId1while @@FETCH_STATUS=0 begin if(@num1=30) update houses set isHot=1 where houseId=@houseId1 fetch next from checkReports into @num1,@houseId1endclose checkReportsdeallocate checkReportsendexec checkIsHOT32 / 32