数据库实验作业7.docxVIP

1.

usesct

delimiter//

createtriggertr_teacher_insert

beforeinsertonteacher

foreachrow

begin

ifnew.salaryisnotnullnew.salary0

thensignalsqlstateHY000

setmessage_text=工资不能小于0，插入失败!;

endif;

end;

//

delimiter;

2.

setforeign_key_checks=0;

usesct;

delimiter//

createtriggertr_teacher_deleteafterdeleteonteacher

foreachrow

begin

deletefromtcwheretc.tno=old.tno;

end;

//

delimiter;

3.

altertablestudent

add

sumcreditchar(6);

usesct;

delimiter//

createtriggertr_student_insertafterupdateonsc

foreachrow

begin

updatestudentsetsumcredit=sumcredit+(selectcreditfromcoursewherecno=new.cno)

wherestudent.sno=new.snoandscore=60;

end;

//

delimiter;

4

usesct;

delimiter//

createtriggertr_sc_updateafterupdateonstudent

foreachrow

begin

updatescsetsc.sno=new.snowheresc.sno=old.sno;

end;

//

delimiter;

1.

insertintoteacher

values(001,克洛林德,女,28,教授,01,-100);

2.

delete

fromteacher

wheretno=002;

select*

fromtc

wheretno=001;

3.

4.

updatestudent

setsnowheresno

3.

1.索引

创建；createindexidx_snoonsc(snodesc);

删除:dropindexidx_snoonsc;

2.视图：

创建：createviewteacher_inf_view

as

select*fromteacherwheredno=01;

删除：dropviewteacher_inf_view;