Chapter 12 Quiz: Views, Triggers, and Stored Procedures

Test your understanding of database logic encapsulation in DB2. Try to answer each question before revealing the answer.

Question 1

What is a view in DB2? Does it store data?

Show Answer A view is a named SELECT statement stored in the database catalog. It does **not** store data. Every time you query a view, DB2 executes the underlying SELECT and returns the result as if it were a table. The view definition (the SQL text) is stored in the catalog, but no data rows are materialized unless you create a Materialized Query Table (MQT).

Question 2

List three practical benefits of using views.

Show Answer 1. **Security**: Views can restrict which rows and columns a user can see, without granting direct access to base tables. 2. **Abstraction**: Views encapsulate complex joins and computations, presenting a simpler interface to application developers. 3. **Stability**: If the underlying table structure changes, the view can be redefined to hide the change from applications. Other valid answers include: convenience (simplifying repetitive queries), consistency (ensuring all users see data through the same transformations), and logical data independence.

Question 3

What four conditions must be true for a view to be updateable (support INSERT, UPDATE, DELETE)?

Show Answer 1. The FROM clause references exactly **one base table** (or one updateable view). 2. The SELECT list does not contain aggregate functions, DISTINCT, GROUP BY, HAVING, UNION, EXCEPT, or INTERSECT. 3. Every non-nullable column without a DEFAULT in the base table must be included (for INSERT). 4. Each column in the view maps directly to a base table column -- no derived or computed columns.

Question 4

What does WITH CHECK OPTION do on a view? What happens without it?

Show Answer WITH CHECK OPTION ensures that any INSERT or UPDATE performed through the view produces a row that still satisfies the view's WHERE clause. Without it, a row can be modified through the view in a way that causes it to "disappear" from the view (because it no longer matches the WHERE condition). This is called **row migration** and is almost always unintended. WITH CHECK OPTION prevents it by rejecting such operations with SQLSTATE 44000.

Question 5

What is the difference between WITH CASCADED CHECK OPTION and WITH LOCAL CHECK OPTION?

Show Answer - **CASCADED** (the default): The check applies to the current view's WHERE clause AND the WHERE clauses of all underlying views it is based on. - **LOCAL**: The check applies only to the current view's own WHERE clause. Underlying views' WHERE clauses are not checked during the insert/update. CASCADED is safer and is the default for good reason. LOCAL is rarely used in practice.

Question 6

What is an INSTEAD OF trigger? When would you use one?

Show Answer An INSTEAD OF trigger intercepts an INSERT, UPDATE, or DELETE on a **view** and executes custom logic instead of DB2's default behavior (which would be to reject the operation on a non-updateable view). You use INSTEAD OF triggers to make complex views (those with joins, aggregation, or other features that prevent direct updates) appear updateable. The trigger body contains the actual base-table modifications needed to implement the requested change.

Question 7

What is a Materialized Query Table (MQT)? How does it differ from a regular view?

Show Answer An MQT physically stores the result of a query, unlike a regular view which stores only the query definition. When you query a regular view, DB2 executes the underlying SELECT every time. An MQT's data is pre-computed and stored in a table, so queries against it read the materialized data directly. The trade-off: MQTs consume storage and may contain stale data (unless REFRESH IMMEDIATE is used), but they can dramatically improve query performance for complex aggregations.

Question 8

Explain the difference between REFRESH DEFERRED and REFRESH IMMEDIATE for an MQT.

Show Answer - **REFRESH DEFERRED**: The MQT is only updated when you explicitly run `REFRESH TABLE`. Between refreshes, the data may be stale relative to the base tables. This is suitable for reporting workloads where slight staleness is acceptable. - **REFRESH IMMEDIATE**: The MQT is automatically updated whenever the base tables change (INSERT, UPDATE, DELETE). The data is always current, but every DML operation on the base tables incurs additional overhead to maintain the MQT.

Question 9

What is the difference between a BEFORE trigger and an AFTER trigger?

Show Answer - **BEFORE trigger**: Fires before the row is modified in the table. It can inspect and modify the NEW values before they are written. It is used for validation, derivation, and data normalization. BEFORE triggers typically cannot modify other tables. - **AFTER trigger**: Fires after the row has been modified (but before COMMIT). It can read both OLD and NEW values and can modify other tables. It is used for audit logging, cascading updates, and cross-table operations.

Question 10

In a trigger, what are the OLD and NEW transition variables?

Show Answer - **OLD**: A pseudo-row containing the column values as they were before the modification. Available for UPDATE and DELETE triggers. - **NEW**: A pseudo-row containing the column values as they will be after the modification. Available for INSERT and UPDATE triggers. In BEFORE triggers, NEW values can be changed (e.g., `SET n.email = LOWER(n.email)`). You define aliases for OLD and NEW using the REFERENCING clause: `REFERENCING OLD AS o NEW AS n`.

Question 11

What is the difference between FOR EACH ROW and FOR EACH STATEMENT in a trigger?

Show Answer - **FOR EACH ROW**: The trigger fires once for every row affected by the DML statement. If an UPDATE modifies 500 rows, the trigger fires 500 times. This is required to access OLD/NEW transition variables for individual rows. - **FOR EACH STATEMENT**: The trigger fires once per SQL statement, regardless of how many rows are affected. It uses transition tables (OLD TABLE, NEW TABLE) instead of transition variables. This is appropriate for aggregate-level actions like logging that a batch operation occurred.

Question 12

What does the SIGNAL SQLSTATE statement do in a trigger body?

Show Answer SIGNAL raises a custom error condition, causing the triggering SQL statement to fail. It is used in BEFORE triggers to reject invalid data. For example: 
SIGNAL SQLSTATE '75001'
    SET MESSAGE_TEXT = 'Insufficient funds';
This aborts the INSERT or UPDATE, and the calling application receives the SQLSTATE code and message text as an error. The operation is rolled back.

Question 13

Name three risks or pitfalls of using triggers.

Show Answer 1. **Trigger chains**: Triggers that modify other tables may fire additional triggers, creating complex cascading chains that are hard to predict, debug, and maintain. Recursive chains are terminated by DB2 after a platform-specific depth limit. 2. **Hidden logic**: Business rules embedded in triggers are invisible to application developers unless they explicitly check for triggers. This makes debugging difficult and increases the learning curve for new team members. 3. **Performance overhead**: Every trigger adds execution time, lock acquisition, and log generation to the triggering DML statement. FOR EACH ROW triggers on bulk operations can be especially expensive (e.g., 100,000 trigger executions for a 100,000-row INSERT). Other valid answers: trigger ordering complexity, impact on bulk load operations, difficulty in testing.

Question 14

What are the three parameter modes for stored procedure parameters? Explain each.

Show Answer 1. **IN**: Input only. The caller passes a value to the procedure. The procedure can read it but changes to it are not visible to the caller. 2. **OUT**: Output only. The procedure sets a value that the caller reads after the CALL completes. The initial value passed by the caller is not meaningful. 3. **INOUT**: Both input and output. The caller passes an initial value, and the procedure can modify it. The modified value is visible to the caller after the CALL.

Question 15

How does a stored procedure return a result set to the calling application?

Show Answer The procedure declares a cursor WITH RETURN, opens it, and **does not close it**. The open cursor is returned to the calling application as a result set. The procedure must include `DYNAMIC RESULT SETS n` in its declaration, where n is the number of result sets it will return. 
CREATE PROCEDURE my_proc()
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE c1 CURSOR WITH RETURN FOR SELECT ...;
    OPEN c1;
    -- do NOT close c1
END;

Question 16

What are the three types of condition handlers in SQL PL?

Show Answer 1. **CONTINUE handler**: Executes the handler body, then continues with the next statement after the one that raised the condition. 2. **EXIT handler**: Executes the handler body, then exits the current BEGIN...END compound statement. 3. **UNDO handler** (LUW only): Like EXIT, but also rolls back any changes made within the current compound statement before exiting.

Question 17

Give two arguments in favor of putting business logic in stored procedures and two arguments in favor of putting it in application code.

Show Answer **In favor of stored procedures:** 1. Reduced network latency -- multiple SQL statements execute in a single round trip. 2. Centralized enforcement -- all applications go through the same logic, ensuring consistency. **In favor of application code:** 1. Easier testability -- application code can be unit-tested with mocking frameworks and CI/CD pipelines. 2. Better debugging tools -- application debuggers (breakpoints, step-through, stack traces) are more mature than SQL PL debugging tools. Other valid answers for stored procedures: security boundary, performance for set-based operations. For application code: horizontal scalability, developer familiarity, database portability.

Question 18

What is the difference between a stored procedure and a user-defined function (UDF)?

Show Answer - A **stored procedure** is called with the CALL statement, can have IN/OUT/INOUT parameters, can return multiple result sets, and can modify data (INSERT, UPDATE, DELETE). It cannot be used inside a SQL expression. - A **user-defined function** (scalar or table) returns a value and can be used inside SQL statements (SELECT, WHERE, etc.). Scalar UDFs return a single value; table UDFs return a table. UDFs typically should not modify data (side-effect-free).

Question 19

Why is view merging important for performance? What happens if the optimizer cannot merge a view?

Show Answer View merging is the process where the optimizer combines the view definition with the outer query into a single optimized access plan. This means using a view has zero or near-zero performance cost compared to writing the equivalent query directly. If the optimizer cannot merge the view (e.g., for views with certain aggregations or nested constructs), it may **materialize** the view -- executing the view's SELECT first, storing the intermediate result, and then applying the outer query to that intermediate result. Materialization can be significantly slower because it eliminates optimization opportunities like predicate pushdown and join reordering.

Question 20

In the Meridian Bank transfer_funds procedure (Section 12.11), why does the procedure validate that both accounts are ACTIVE before performing the transfer? What would happen if this check were omitted?

Show Answer The procedure validates account status because transferring funds to or from a frozen, closed, or dormant account violates banking business rules. If the check were omitted: - Money could be transferred out of a frozen account (bypassing a regulatory hold). - Money could be deposited into a closed account (creating orphaned funds). - The bank would be out of compliance with its own policies and potentially with regulatory requirements. The database could also have CHECK constraints enforcing status rules, but the procedure provides a more descriptive error message and prevents the partial execution of the multi-step operation.

Question 21

What catalog views would you query on z/OS and LUW to find all triggers defined on a specific table?

Show Answer - **z/OS**: `SELECT * FROM SYSIBM.SYSTRIGGERS WHERE TBNAME = 'table_name' AND TBOWNER = 'schema';` - **LUW**: `SELECT * FROM SYSCAT.TRIGGERS WHERE TABNAME = 'table_name' AND TABSCHEMA = 'schema';` Both catalog tables include the trigger name, event (INSERT/UPDATE/DELETE), activation time (BEFORE/AFTER), granularity (ROW/STATEMENT), and the SQL text of the trigger body.

Question 22

A developer reports that an INSERT into the ACCOUNT table is taking 50ms when it should take 2ms. You suspect trigger overhead. How would you investigate?

Show Answer 1. **Check for triggers**: Query the catalog (SYSCAT.TRIGGERS or SYSIBM.SYSTRIGGERS) to list all triggers on the ACCOUNT table. 2. **Review trigger bodies**: Read each trigger's SQL text to understand what it does. Look for triggers that modify other tables, perform subqueries, or have complex logic. 3. **Check for trigger chains**: If a trigger modifies another table, check for triggers on that table too. Trace the full chain. 4. **Measure**: On z/OS, use accounting trace class 3 to see trigger execution time. On LUW, use `db2pd -tcbstats`, event monitors, or the Activity Monitor. 5. **Test**: Temporarily disable triggers (ALTER TRIGGER ... DISABLE on LUW) and re-run the INSERT to measure the difference.

Question 23

What is the maximum trigger nesting depth on DB2, and what happens when it is exceeded?

Show Answer The default maximum trigger nesting depth is **16 levels** on both z/OS and LUW. When this depth is exceeded, DB2 raises SQLSTATE 54038 and rolls back the triggering statement. This limit prevents infinite recursion (e.g., trigger on Table A modifies Table B, trigger on Table B modifies Table A, and so on). The depth can be configured on some platforms but increasing it is rarely advisable.

Question 24

You have a view with WITH CHECK OPTION that filters WHERE status = 'ACTIVE'. A user tries to INSERT a row with status = 'PENDING' through the view. What happens?

Show Answer DB2 rejects the INSERT with **SQLSTATE 44000** (check option violation). WITH CHECK OPTION requires that every row inserted or updated through the view must satisfy the view's WHERE clause. Since `status = 'PENDING'` does not satisfy `WHERE status = 'ACTIVE'`, the insert is not allowed. The row is not created in the base table.

Question 25

Compare the following two approaches for implementing the "mask SSN" requirement. Which is better and why?

Approach A: Create a view that shows 'XXX-XX-' || RIGHT(ssn, 4) as the SSN column. Grant SELECT on the view to call center agents. Do not grant SELECT on the base table.

Approach B: Create a scalar UDF mask_ssn(ssn) that returns 'XXX-XX-' || RIGHT(ssn, 4). Train developers to always use this function when displaying SSN.

Show Answer **Approach A is significantly better.** It enforces the masking at the database level. Call center agents literally cannot see the full SSN because they do not have access to the base table. The security is architectural, not behavioral. **Approach B** relies on every developer remembering to call the function. If any developer forgets, or if a new application is built without using the function, full SSNs are exposed. The UDF is a tool; the view is a barrier. For security-critical requirements, barriers are always preferable to tools. The UDF still has value -- it can be used within the view definition or in other contexts -- but it should not be the sole mechanism for protecting sensitive data.

Return to Chapter 12 | Continue to Case Study 1