资源描述：
《oracle触发器练习（oracle flip flop exercise）》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、oracle触发器练习（Oracleflipflopexercise）DMLflipflop:ExcitingwhenperformingInsert,Update,andDeleteoperationsonthetableItcanbeusedtoperformverification,setinitialvalues,auditchanges,andevenprohibitcertainDMLoperationsGrammar:CREATEORREPLACETRIGGERtriggernam
2、e{AFTER
3、BEFORE}specifiesthetriggertime{INSERTORDELETEORUPDATE}-specifiestriggereventsONtablename-specifiesthetabletobemonitored{FOREACHROW
4、FOREACHSTATEMENT}-specifiesthenumberoftriggersBEGIN--code;END;Relatedconcepts:AFTER
5、BEFORE:beforeorafteranevent
6、INSERT
7、DELETE
8、UPDATE:whatevent?ONtablename:whattabledoesthetriggerbuildon?Whattabledoesitmonitor?FOREACHROW:rowleveltrigger,example:deletefromT1,deletethe1000line,thenexecute1000times(oneline,onetime)FOREACHSTATEMENT:statementleveltrigger,example:del
9、etefromT1,deletethe1000line,thenexecute1times(onceasentence)New:variable:savetheeventnewrow,onlytheinsertandupdateeventshavenewdataOldrowvariables:savetheolddatawhentheeventoccurs,onlythedeleteeventandtheupdateeventhavetheolddataExample:pointsoutnewd
10、ataandolddataineventsandeventsInsertintoEMP(empno,ename)values(51,'job');Analysis:thereisonlyonelineofnewdata(51,job),correspondingto:newvariable.UpdateEMPsetename='oracle'whereempno=51;Analysis:olddata(51,job),corresponding:oldvariable.Newdata(51,Or
11、acle),corresponding:newvariable.DeletefromEMPwhereempno=51;Analysis:thereisonlyonerowofolddata(51,Oracle),correspondingto:oldvariable.SqlcodeDisplaytriggerSelecttrigger_name,statusfromuser_triggers;ProhibittriggersAltertriggertr_emp_salarydisable;Act
12、ivatetriggersAltertriggertr_emp_salaryenable;--alltriggersthatprohibitthetableAltertableemployeedisablealltriggers;ActivatealltriggersofthetableAltertableemployeeenablealltriggers;Recompiletriggers--altertabletr_emp_salarycompile;DeletetriggerDroptri
13、ggertr_emp_salary;DisplaytriggerSelecttrigger_name,statusfromuser_triggers;ProhibittriggersAltertriggertr_emp_salarydisable;ActivatetriggersAltertriggertr_emp_salaryenable;--alltriggersthatprohibitthetableAltertableemployeedisablealltriggers;Activate
