数据库系统原理(英文)-8-09.pptVIP免费

PART2RELATIONALDATABASESChapter8ApplicationDesignandDevelopmentApril2009DatabaseSystemConcepts-Chapter8ApplicationDesignandDevelopment-3CasestudyusedinthischapterreturnFig.8.0SchemaofbankingenterpriseApril2009DatabaseSystemConcepts-Chapter8ApplicationDesignandDevelopment-4§8.6Trigger(触发器)TriggerastatementthatisexecutedautomaticallybyDBMSasasideeffectofamodificationtothedatabaseAsanintegritycontrolmechanism,TriggerisintroducedtoSQL1999standard,butsupportedevenearlierusingnon-standardsyntaxbymostdatabasesApril2009DatabaseSystemConcepts-Chapter8ApplicationDesignandDevelopment-58.6Trigger(cont.)Triggerisanevent-condition-actionmodelbasedintegritydefinition,checking,remedymechanismspecifywhateventscausethetriggertobeexecuted(e.g.insert,delete,update),andunderwhichconditionsthetriggerexecutionwillproceedintegrityconstraintscheckingspecifytheactionstobetakenwhenthetriggerexecutesifconstraintsisviolated,remedyactionsaretakenApril2009DatabaseSystemConcepts-Chapter8ApplicationDesignandDevelopment-6Supposethat,insteadofallowingnegativeaccountbalances,thebankdealswithoverdrafts(透支)byfollowingactionssettingtheaccountbalancetozerocreatingaloanintheamountoftheoverdraftgivingthisloanaloannumberidenticaltotheaccountnumberoftheoverdrawnaccountTriggeroverdraft-triggerinFig.8.8thetriggeringeventisupdateonbalancetheconditionforexecutingthetriggerisanmodificationtotheaccountrelationthatresultsinanegativebalancevalueExampleOneApril2009DatabaseSystemConcepts-Chapter8ApplicationDesignandDevelopment-7triggeractionsareinsertonborrower,loanrelations,andupdateonaccountrelationExampleOne(cont.)createtriggeroverdraft-triggerafterupdateonaccount---eventreferencingnewrowasnrowforeachrowwhennrow.balance<0-------conditionbeginatomic-------actioninsertintoborrower(selectcustomer-name,account-numberfromdepositorwherenrow.account-number=depositor.account-number);insertintoloanvalues(nrow.account-number,nrow.branch-name,–nrow.balance);updateaccountsetbalance=0w...