3.5Views and embedded SQL

Views, cursors, stored procedures, and triggers all bear on a single design question: where should a given piece of application logic live? A database specialist needs to understand the trade-off between the consistency and reusability gained by pushing logic into the DBMS and the portability and testability gained by keeping it in the application, and to choose appropriately for the situation. This section covers view updatability, cursors and dynamic SQL, and stored procedures and triggers.

3.5.1Updatable views and WITH CHECK OPTION

• A view is essentially a saved, named query; under the hood it re-executes a query against the base table(s) each time. An updatable view is one composed only of a simple projection/selection from a single base table—one without aggregate functions, GROUP BY, DISTINCT, or joins across multiple tables—and it supports UPDATE/INSERT/DELETE.
• Through a view's defining WHERE condition (e.g., a view filtered to status = 'active'), it is possible to UPDATE a row to a value that violates that condition, or INSERT a row that does not satisfy it—the row then becomes invisible through the view afterward (a disappearing row). Adding WITH CHECK OPTION to the view definition enforces that updates continue to satisfy the view's defining condition, preventing this inconsistency.

3.5.2Cursors and dynamic SQL

• In embedded SQL (writing SQL statements inside a host programming language), a SELECT can produce a multi-row result set, while host-language variables typically hold only one row's worth of values at a time. A cursor bridges this gap by retrieving the result set one row at a time (DECLARE CURSOR -> OPEN -> repeated FETCH -> CLOSE).
• Dynamic SQL builds and executes an SQL statement as a string at runtime (as opposed to static SQL, which uses a fixed statement determined in advance). It is needed for things like a search screen whose conditions vary at runtime (e.g., the number of search fields), but concatenating user input directly into the string creates an SQL injection vulnerability, so parameterizing with placeholders (bind variables) is mandatory.

3.5.3Stored procedures and triggers

• A stored procedure predefines a procedure (multiple SQL statements plus control structures) inside the DBMS, which the application can invoke with a single call. Because it reduces the number of application-to-database round trips, it is often faster than issuing multiple separate SQL statements. On the other hand, it has a maintainability drawback: the logic is written in a DBMS-specific language, and version control/testing become split off from the application side.
• A trigger is processing the DBMS automatically executes in response to an INSERT/UPDATE/DELETE on a specific table. It is used for things like automatically recording audit logs or maintaining consistency in related tables (e.g., automatically updating an inventory table in step with orders). Triggers tend to become "hidden" side effects invisible from the calling application, and overusing them creates a maintainability problem where behavior becomes hard to trace.

Suppose an application developer creates an active_customers view for sales reps, showing "only the customers I own who are under an active contract" (logic roughly equivalent to WHERE status = 'active' AND owner_id = <the current rep's ID>). Some time after go-live, a report comes in: "when a rep updates a customer's status to 'inactive' through this view, that customer suddenly vanishes from their screen." This is a classic instance of a "disappearing row": the update is allowed to a value (status != 'active') that violates the view's defining condition, and the row then falls outside the view's scope. In many cases this is the intended behavior (a customer whose contract has ended is fine to drop off the owned-customer list), but if there is a business rule stating "status changes should go through a separate approval workflow, and sales reps must not be able to change status directly through this view," then the appropriate design is to add WITH CHECK OPTION to the view definition, so that an update which would no longer satisfy the view's condition (status = 'active') is rejected outright. In addition, this developer also has a requirement for a customer search screen: "build a query based on whatever combination of search criteria (name, region, contract type, etc.) the sales rep selects." Because the number of search conditions varies, dynamic SQL is needed, but if the name or region values a user types directly into the search fields are concatenated as raw strings into the SQL statement, malicious input can enable SQL injection. Therefore, the design must be limited to dynamically assembling only the SQL statement's structure (how many and which condition clauses go into WHERE), while the user-input values themselves must always be passed via placeholders (bind variables)—a design principle that must be enforced without exception.

3.5.4Section summary

• An updatable view is limited to a simple projection/selection from a single base table; WITH CHECK OPTION can enforce that updates keep satisfying the defining condition
• A cursor bridges a multi-row result set by fetching one row at a time; dynamic SQL risks SQL injection unless values are passed via placeholders
• A stored procedure trades maintainability for fewer round trips; a trigger tends to become a hidden, automatically firing side effect