UPDATE EzonexamConsolidated
SET District = ‘Mexico’
WHERE RtvID = 45
You work as database administrator at Ezonexam.com. You handle one SQL Server 2000 computer and one database.
When users run the query in the exhibit above the receive the following error message:
Server: Mrh3612, Level 16, Stats 1, Line 1
View or function ‘EzonexamConsolidated’ is not updateable because it
contains aggregates:
How should you enable the query to run?
A．Create a non-normalized EzonexamConsolidated table and populate the table with data from the base tables.
B．The appropriate users should be granted UPDATE permissions on each base table.
C．To enable updates on the composite tables create an INSTEAD OF trigger on the view.
D．On each base table add a cascading update trigger.
参考答案
正确答案：C
解析：Explanation:SQLServer2000usesINSTEADOFtriggersandpartitionedviewstoenhancetheclassofupdateableviews.INSTEADOFtriggerscanbecreatedonaviewinordertomakeaviewupdateable.TheINSTEADOFtriggerisexecutedinsteadofthedatamodificationstatementonwhichthetriggerisdefined.Thistriggerallowstheusertospecifythesetofactionsthatneedtotakeplaceinordertoprocessthedatamodificationstatement.Thus,ifanINSTEADOFtriggerexistsforaviewonagivendatamodificationstatement,thecorrespondingviewisupdateablethroughthatstatement.Iftheviewisapartitionedview,theviewisupdateable,subjecttocertainrestrictions.IncorrectAnswers:A:Creatingatablethatcombinesthecompositetablesthattheviewisbasedonwoulddenyustheabilitytousetheviewtoreturnaggregatedcolumnvalues.B:Theerrormessageisnotrelatedtopermissionsbuttotheupdateableofthetable.D:CascadingreferentialintegrityconstraintsmakesitpossibletodefinetheactionsSQLServer2000takeswhenauserattemptstodeleteorupdateakeytowhichexistingforeignkeyspoint.CascadingreferentialintegrityconstraintsfireAFTERtriggersafterallofthecascadingreferentialactionsdirectlycausedbytheoriginalDELETEorUPDATEareperformed.