A city permits counter app must calculate fees and update application status in one server-side call. Which database object should the team implement?
Select an answer to reveal the explanation.
Short Explanation
Think of the stored procedure as the counter’s one-button checkout: you calculate the fee and change the application status together, without making the app juggle half-finished steps. A scalar function returns values; it is not meant to quietly rewrite rows.
Full Explanation
A city permits counter that must calculate fees and change application status in one round-trip needs a multi-step write path behind a single callable contract. A stored procedure is the right object: it can compute the fee from rate tables and parcel attributes, update the application row’s status, and optionally return the fee or new status to the counter app—all in one server-side call. Keeping the fee calculation and status change together makes the workflow atomic, so a network glitch cannot leave an application charged but still Pending, or marked Issued with no fee recorded. A scalar function is the wrong fit because it is meant to return values for use in queries; updating application rows as a side effect of fee calculation breaks that expectation and is disallowed or discouraged in many SQL environments. A temporal history table versions row changes for AS OF queries; it does not recalculate fees or orchestrate status transitions when history is read. An INSTEAD OF DELETE trigger that only archives rejected applications handles a different lifecycle event and does not perform the fee calculation and status update the counter needs. Exam caveat: keep scalar and table-valued functions side-effect free, and use a stored procedure for this multi-step write. Practical check: call the procedure once with a known application ID in a transaction, confirm that both the fee and status change, then roll back in a lab and verify that no orphan fee row or half-updated status remains.