1、触发器(oracle).txt根网线尽赚了多少人的青春有时候感动的就是身边微不足道的小事。破碎不是最残酷的 最残酷的是踩着这些碎片却假装不疼痛 固执的寻找将来就算我遇见再怎么完美的人,都有一个缺点,他不是你,_下辈子要做男生，娶一个像我这样的女生。注意：分别把下面的每个触发器拷贝到查询分析器中运行，不要一股脑拷贝运行。1.插入球队触发插入积分表-当向球队表 Team 中插入一行数据时，会自动向积分榜 Score 中插入一条对应的数据create trigger teaminsert on teamfor insertas declare name varchar(20)select name=
2、NAME from insertedinsert into score(PLACE,NAME,TURN,WON,EVEN,BEATEN,GOAL,LOST,NET,POINT) values(0,name,0,0,0,0,0,0,0,0)2.删除球队触发删除射手榜、球员、比赛情况、积分榜-删除球队的时候由于很多其他表格都参照球队表，需要先删除从表，再删除主表球队表，所以采用 instead of 类型的触发器，把球队的删除工作替换为一系列动作create trigger teamdel on teaminstead of deleteasdeclare name varchar(20)/*由于
3、删除的可能不止一支球队，所以采用游标。把删除的记录（放在 deleted 系统临时表中）的球队名称做成一个游标，每次从游标中取出一条，放入到临时变量name 中，然后再根据name 删除 goal、member、course、score、team 中的数据*/declare team_cursor cursorfor select name from deletedopen team_cursor -打开游标fetch next from team_cursor into namewhile FETCH_STATUS=0 -FETCH_STATUS=0 表示从游标中获取数据成功begindel
4、ete from goal where teamname=namedelete from member where teamname=namedelete from course where home=name or away=namedelete from score where name=namedelete from team where name=namefetch next from team_cursor into name -从游标中获取下一条数据endclose team_cursor -关闭游标deallocate team_cursor -释放游标占用的内存资源3.插入比赛
5、进程时调整积分榜CREATE TRIGGER COURSE_INSERT ON Course FOR INSERT ASDECLARE Turn INTEGER,HomeTeam VARCHAR(20),AwayTeam VARCHAR(20),HomeScore INTEGER,AwayScore INTEGERSET Turn=0SET HomeTeam =SET AwayTeam=SET HomeScore=0SET AwayScore=0-从 INSERTED 表中获取插入的数据，以便进行各种情况的判断SELECT Turn=TURN,HomeTeam=HOME,AwayTeam=AW
6、AY,HomeScore=HOMESCORE,AwayScore=AWAYSCOREFROM INSERTED-如果积分榜中没有主队或客队的信息，则添加一条IF NOT EXISTS(SELECT NAME FROM SCORE WHERE NAME=HomeTeam)INSERT INTO SCORE VALUES(0,HomeTeam,0,0,0,0,0,0,0,0)IF NOT EXISTS(SELECT NAME FROM SCORE WHERE NAME=AwayTeam)INSERT INTO SCORE VALUES(0,AwayTeam,0,0,0,0,0,0,0,0)-主队获
7、胜IF(HomeScoreAwayScore)BEGINUPDATE SCORESET TURN=Turn,WON=WON+1,GOAL=GOAL+HomeScore,LOST=LOST+AwayScore,NET=NET+(HomeScore-AwayScore),POINT=POINT+3WHERE NAME=HomeTeamUPDATE SCORESET TURN=Turn,BEATEN=BEATEN+1,GOAL=GOAL+AwayScore,LOST=LOST+HomeScore,NET=NET+(AwayScore-HomeScore),POINT=POINT+0WHERE NAM
8、E=AwayTeamEND-两队打平ELSE IF(HomeScore=AwayScore)BEGINUPDATE SCORESET TURN=Turn,EVEN=EVEN+1,GOAL=GOAL+HomeScore,LOST=LOST+AwayScore,POINT=POINT+1WHERE NAME=HomeTeamUPDATE SCORESET TURN=Turn,EVEN=EVEN+1,GOAL=GOAL+AwayScore,LOST=LOST+HomeScore,POINT=POINT+1WHERE NAME=AwayTeamEND-主队失利ELSEBEGINUPDATE SCORE
9、SET TURN=Turn,BEATEN=BEATEN+1,GOAL=GOAL+HomeScore,LOST=LOST+AwayScore,NET=NET+(HomeScore-AwayScore),POINT=POINT+0WHERE NAME=HomeTeamUPDATE SCORESET TURN=Turn,WON=WON+1,GOAL=GOAL+AwayScore,LOST=LOST+HomeScore,NET=NET+(AwayScore-HomeScore),POINT=POINT+3WHERE NAME=AwayTeamEND-利用游标对名次进行处理DECLARE name VA
10、RCHAR(20), place INTEGERSET place=0 -按照积分、净胜球、进球数的降序进行排名DECLARE score_cursor CURSORFORSELECT NAME FROM SCORE ORDER BY POINT DESC,NET DESC,GOAL DESCOPEN score_cursor FETCH NEXT FROM score_cursor INTO nameWHILE FETCH_STATUS=0 BEGINSET place=place+1UPDATE SCORE SET PLACE=place WHERE NAME=nameFETCH NEXT
11、 FROM score_cursor INTO nameEND CLOSE score_cursor DEALLOCATE score_cursor 4.插入进球时调整射手榜CREATE TRIGGER goal_insertON Goal FOR INSERTASDECLARE name varchar(20),teamname varchar(20),number integerselect teamname=TEAMNAME,number=NUMBER from insertedselect name=NAME from member where teamname=teamname an
12、d number=number-如果射手榜中没有该球员的信息，则插入一条对应记录if not exists(select name from goalscore where teamname=teamname and number=number)insert into goalscore values(0,name,teamname,number,0)-进球数加一update goalscore set goals=goals+1 where teamname=teamname and number=number-重新对射手榜进行排名DECLARE place INTEGERSET place
13、=0 DECLARE goalscore_cursor CURSORFORSELECT TEAMNAME,NUMBER FROM GOALSCORE ORDER BY GOALS DESC,NAMEOPEN goalscore_cursor FETCH NEXT FROM goalscore_cursor INTO teamname,numberWHILE FETCH_STATUS=0 BEGINSET place=place+1UPDATE GOALSCORE SET PLACE=place WHERE teamname=teamname and number=numberFETCH NEX
14、T FROM goalscore_cursor INTO teamname,numberEND CLOSE goalscore_cursor DEALLOCATE goalscore_cursor 5.修改球队名称时自动调整相关表格中的球队名称create trigger TeamUpdateon Teaminstead of updateasdeclare oldName varchar(20)declare oldHome varchar(30)declare oldCoach varchar(20)declare newName varchar(20)declare newHome va
15、rchar(30)declare newCoach varchar(20)select oldName=name, oldHome=home, oldCoach=coach from deletedselect newName=name, newHome=home, newCoach=coach from insertedif (oldNamenewName)beginbegin transactioninsert into team(name,home,coach) values(newName,newhome,newCoach)delete from score where name=ne
16、wNameupdate goal set teamname=newName where teamname=oldNameupdate member set teamname=newName where teamname=oldNameupdate course set home=newName where home=oldNameupdate course set away=newName where away=oldNameupdate score set name=newName where name=oldNamedelete from team where name=oldNamecommitendelseupdate team set home=newhome, coach=newCoach where name=oldName
