Chapter 12 Exercises: Views, Triggers, and Stored Procedures

These exercises progress from foundational recall through applied design. Difficulty ratings: [Beginner], [Intermediate], [Advanced], [Challenge].

Section A: Views (Exercises 1-10)

Exercise 1 -- Simple View Creation [Beginner]

Create a view called meridian.v_active_customers that shows only active customers (where status = 'ACTIVE'). Include the columns: customer_id, first_name, last_name, email, phone, and city. Then write a query that uses the view to find all active customers in the city of 'Boston'.

Exercise 2 -- View with Column Restrictions [Beginner]

Meridian Bank's call center agents need access to customer data but should never see Social Security numbers or internal risk scores. Create a view called meridian.v_customer_callcenter that includes all customer columns except ssn, risk_score, and internal_notes. Explain why this approach is preferable to granting SELECT on the base table and training agents not to look at those columns.

Exercise 3 -- View with Joins [Intermediate]

Create a view called meridian.v_account_detail that joins the following tables: - meridian.account - meridian.customer - meridian.account_type - meridian.branch

The view should show: account_id, customer full name (first + last), account_type description, balance, open_date, status, branch_name, and branch_city.

Then write a query against the view that finds all active checking accounts with a balance over $5,000, sorted by balance descending.

Exercise 4 -- View with Aggregation [Intermediate]

Create a view called meridian.v_customer_portfolio that shows, for each customer: - customer_id - customer_name (first + last) - total_accounts (count of all accounts) - active_accounts (count of active accounts) - total_balance (sum of all active account balances) - avg_balance (average balance of active accounts) - oldest_account_date (earliest open_date)

Write a query against this view to find the top 10 customers by total balance.

Exercise 5 -- Updateable vs. Read-Only [Intermediate]

For each of the following view definitions, determine whether the view is updateable or read-only. Explain your reasoning.

a) A view that selects account_id, balance, and status from meridian.account where status = 'ACTIVE'.

b) A view that joins meridian.account and meridian.customer and selects columns from both.

c) A view that selects branch_id and COUNT(*) from meridian.account, grouped by branch_id.

d) A view that selects DISTINCT account_type_code from meridian.account.

e) A view that selects all columns from meridian.customer where city = 'Chicago' WITH CHECK OPTION.

Exercise 6 -- WITH CHECK OPTION [Intermediate]

Consider this view:

CREATE VIEW meridian.v_premium_accounts AS
    SELECT account_id, customer_id, balance, status, account_type_code
    FROM meridian.account
    WHERE account_type_code = 'PRM'
WITH CHECK OPTION;

For each of the following operations, state whether DB2 will allow it or reject it, and why:

a) INSERT INTO meridian.v_premium_accounts (customer_id, balance, status, account_type_code) VALUES (100, 5000, 'ACTIVE', 'PRM');

b) UPDATE meridian.v_premium_accounts SET balance = 10000 WHERE account_id = 5001;

c) UPDATE meridian.v_premium_accounts SET account_type_code = 'STD' WHERE account_id = 5001;

d) DELETE FROM meridian.v_premium_accounts WHERE account_id = 5001;

Exercise 7 -- INSTEAD OF Trigger [Advanced]

The view meridian.v_account_detail from Exercise 3 is read-only because it contains joins. Write an INSTEAD OF UPDATE trigger that allows users to update the balance and status columns through the view. The trigger should only modify the meridian.account base table. If the user attempts to change any other column (like branch_name), the trigger should ignore those changes.

Exercise 8 -- Materialized Query Table [Advanced]

Create an MQT called meridian.mqt_daily_branch_summary that precomputes: - branch_id - branch_name - summary_date (transaction date) - txn_count (number of transactions) - total_credits - total_debits - net_flow (credits minus debits)

Use REFRESH DEFERRED. Then write the REFRESH TABLE statement, and explain when in the daily processing cycle this refresh should be scheduled.

Exercise 9 -- Views for Security [Advanced]

Design a set of three views for Meridian Bank's three user roles:

  1. Teller: Can see customer name, account type, balance, and status for accounts at their branch. Cannot see SSN, risk score, or accounts at other branches.
  2. Branch Manager: Can see everything a teller sees, plus account open/close dates, last activity date, and the total number of transactions per account. Still limited to their branch.
  3. Regional Manager: Can see all branches in their region, with aggregate statistics (total deposits, account counts) per branch.

Write the CREATE VIEW statements for all three views. Explain how you would use GRANT statements to restrict each role to its appropriate view.

Exercise 10 -- View on View [Intermediate]

Create a view meridian.v_high_value_branches that is based on the meridian.v_branch_dashboard view from Section 12.11. It should show only branches where total deposits exceed $5 million and the number of active accounts exceeds 100. Discuss the performance implications of nesting views, and identify when the optimizer might struggle with deeply nested view definitions.

Section B: Triggers (Exercises 11-20)

Exercise 11 -- Basic BEFORE Trigger [Beginner]

Write a BEFORE INSERT trigger on meridian.customer that: 1. Trims leading and trailing spaces from first_name and last_name. 2. Converts email to lowercase. 3. Sets created_date to CURRENT TIMESTAMP if it is NULL.

Exercise 12 -- AFTER Trigger for Logging [Beginner]

Write an AFTER INSERT trigger on meridian.transaction that inserts a row into meridian.transaction_log with the following columns: log_id (auto-generated), txn_id, account_id, txn_type, amount, logged_by (CURRENT USER), logged_at (CURRENT TIMESTAMP).

Exercise 13 -- Validation Trigger [Intermediate]

Write a BEFORE INSERT trigger on meridian.account that enforces the following business rules: 1. The balance must be non-negative (>= 0). 2. The account_type_code must be one of: 'CHK', 'SAV', 'MMA', 'CD', 'PRM'. 3. The open_date must not be in the future.

If any rule is violated, use SIGNAL SQLSTATE to raise an appropriate error with a descriptive message.

Exercise 14 -- Trigger with Conditional Logic [Intermediate]

Write an AFTER UPDATE trigger on meridian.account that fires when the status column changes. The trigger should: - If the new status is 'FROZEN', insert a row into meridian.account_alerts with alert_type = 'ACCOUNT_FROZEN'. - If the new status is 'CLOSED', insert a row into meridian.account_alerts with alert_type = 'ACCOUNT_CLOSED'. - In all cases, record the old status, new status, account_id, and timestamp.

Exercise 15 -- Multiple Triggers and Ordering [Intermediate]

You need three BEFORE INSERT triggers on meridian.transaction: 1. ORDER 1: Validate that the amount is positive. 2. ORDER 2: Set the txn_timestamp to CURRENT TIMESTAMP. 3. ORDER 3: Set the processed_flag to 'N' and compute a txn_hash from the account_id, amount, and timestamp.

Write all three triggers. Explain why the ORDER clause matters and what would happen if trigger 3 ran before trigger 2.

Exercise 16 -- Statement-Level Trigger [Advanced]

Write a FOR EACH STATEMENT AFTER UPDATE trigger on meridian.account that: 1. Counts how many rows were updated using a transition table. 2. If more than 100 rows were updated in a single statement, inserts an alert into meridian.batch_operation_log with the count and timestamp.

This could be used to detect potentially unauthorized mass updates.

Exercise 17 -- Cross-Table Validation Trigger [Advanced]

Write a BEFORE INSERT trigger on meridian.account that prevents a customer from having more than 10 active accounts. The trigger should: 1. Count the customer's current active accounts. 2. If the count is 10 or more, reject the insert with a descriptive error. 3. Otherwise, allow the insert to proceed.

Exercise 18 -- Trigger Chain Analysis [Advanced]

Given the following triggers (described, not coded), trace the complete chain of events when a row is inserted into meridian.transaction with txn_type = 'DEBIT' and amount = 500:

  1. BEFORE INSERT on meridian.transaction: Sets txn_timestamp and validates amount > 0.
  2. AFTER INSERT on meridian.transaction: Updates meridian.account to decrease balance by the amount.
  3. AFTER UPDATE on meridian.account (balance): Inserts a row into meridian.account_audit.
  4. BEFORE INSERT on meridian.account_audit: Sets created_at to CURRENT TIMESTAMP.

List every operation that occurs, in order. How many tables are modified? How many locks are acquired? What is the risk if any trigger in the chain fails?

Exercise 19 -- Trigger vs. CHECK Constraint [Intermediate]

For each of the following validation rules, determine whether a CHECK constraint, a BEFORE trigger, or either would be appropriate. Explain your reasoning.

a) Account balance must be >= 0. b) Email address must contain an '@' character. c) The combination of account_type_code and branch_id must exist in a meridian.branch_offering table. d) The close_date must be after the open_date. e) The total balance across all of a customer's accounts must not exceed $10,000,000.

Exercise 20 -- Trigger Performance Benchmark [Challenge]

Design an experiment to measure the performance impact of triggers on INSERT operations:

  1. Create a test table with 5 columns.
  2. Create an AFTER INSERT trigger that inserts a corresponding row into an audit table.
  3. Insert 100,000 rows into the test table with the trigger active.
  4. Disable (or drop) the trigger and repeat the 100,000-row insert.
  5. Compare the elapsed times.

Write the DDL for the tables, the trigger, and the test INSERT statements. Describe what metrics you would collect and what results you would expect.

Section C: Stored Procedures (Exercises 21-30)

Exercise 21 -- Basic Stored Procedure [Beginner]

Write a stored procedure called meridian.get_customer_info that: - Takes an IN parameter p_customer_id (INTEGER). - Returns OUT parameters: p_full_name (VARCHAR(100)), p_email (VARCHAR(100)), p_total_accounts (INTEGER), p_total_balance (DECIMAL(15,2)). - Queries the customer and account tables to populate the OUT parameters.

Write the CALL statement to invoke this procedure.

Exercise 22 -- Procedure with Conditional Logic [Beginner]

Write a stored procedure called meridian.categorize_account that: - Takes IN parameter p_account_id (INTEGER). - Returns OUT parameter p_category (VARCHAR(20)). - Categorizes the account based on balance: - Balance >= 100,000: 'PLATINUM' - Balance >= 25,000: 'GOLD' - Balance >= 5,000: 'SILVER' - Balance >= 0: 'STANDARD' - Balance < 0: 'OVERDRAWN'

Exercise 23 -- Procedure with Cursor [Intermediate]

Write a stored procedure called meridian.deactivate_dormant_accounts that: - Takes IN parameter p_days_inactive (INTEGER) -- number of days since last activity. - Uses a cursor to iterate over all ACTIVE accounts where last_activity_date is older than p_days_inactive days. - For each account, updates the status to 'DORMANT' and records the change in meridian.account_audit. - Returns OUT parameters: p_accounts_deactivated (INTEGER) and p_status_message (VARCHAR(500)).

Exercise 24 -- Procedure with Result Set [Intermediate]

Write a stored procedure called meridian.search_customers that: - Takes IN parameters: p_last_name (VARCHAR(50)), p_city (VARCHAR(50)), p_min_balance (DECIMAL(15,2)). - All parameters are optional (NULL means "don't filter on this field"). - Returns a result set of matching customers with their total account balances. - Use DYNAMIC RESULT SETS.

Write the CALL statement and explain how the calling application would process the result set.

Exercise 25 -- Error Handling [Intermediate]

Write a stored procedure called meridian.open_account that: - Takes IN parameters: p_customer_id, p_account_type, p_initial_deposit, p_branch_id. - Returns OUT parameters: p_new_account_id, p_status_code, p_message. - Validates that the customer exists, the account type is valid, the initial deposit is >= the minimum required for that type, and the branch exists. - Includes a DECLARE EXIT HANDLER for SQLEXCEPTION. - On success, inserts the new account and the initial deposit transaction. - On failure, returns an appropriate error code and message.

Exercise 26 -- Procedure with SAVEPOINT [Advanced]

Modify the meridian.transfer_funds procedure from Section 12.11 to use SAVEPOINTs. Instead of rolling back the entire transaction on failure, roll back to a SAVEPOINT set before the transfer operations. This allows the calling application to perform additional operations within the same transaction even if the transfer fails.

Exercise 27 -- Nested Procedure Calls [Advanced]

Write a stored procedure called meridian.monthly_account_maintenance that calls three other procedures in sequence: 1. meridian.calculate_monthly_interest (from Section 12.11) 2. meridian.deactivate_dormant_accounts (from Exercise 23) 3. meridian.generate_monthly_statements (assume this exists; you only need to write the CALL)

The master procedure should: - Collect the results from each sub-procedure. - Continue with the remaining procedures even if one fails (using handlers). - Return a summary of all three operations (how many accounts processed by each, any errors).

Exercise 28 -- Procedure vs. Application Logic Analysis [Intermediate]

For each of the following operations at Meridian Bank, recommend whether it should be implemented as a stored procedure, an application-layer function, or a combination. Justify each recommendation with at least two factors from the trade-off framework in Section 12.9.

a) Calculating a customer's credit score based on transaction history. b) Validating a new customer's address against a postal service API. c) Generating a PDF monthly statement. d) Processing an ATM withdrawal (balance check, debit, transaction record). e) Sending an email notification when an account balance drops below a threshold. f) Migrating 500,000 customer records from a legacy system.

Exercise 29 -- Dynamic SQL in a Procedure [Advanced]

Write a stored procedure called meridian.flexible_report that: - Takes IN parameters: p_table_name (VARCHAR(128)), p_where_clause (VARCHAR(500)), p_order_by (VARCHAR(200)). - Constructs and executes a dynamic SQL statement using EXECUTE IMMEDIATE or PREPARE/EXECUTE. - Returns the result as a dynamic result set.

Discuss the SQL injection risk of this approach and describe two mitigation strategies.

Exercise 30 -- Comprehensive Business Operation [Challenge]

Design and implement a stored procedure called meridian.process_loan_payment that handles a customer making a monthly loan payment:

  1. Accepts: p_loan_account_id, p_payment_account_id, p_payment_amount.
  2. Validates: both accounts exist and are active, payment account has sufficient funds, loan account has a positive balance, payment does not exceed loan balance.
  3. Processes: debits the payment account, credits the loan account (reducing the loan balance), records transactions on both accounts, calculates remaining loan balance and estimated payoff date.
  4. Returns: p_remaining_loan_balance, p_estimated_payoff_date, p_status_code, p_message.

Include full error handling, audit logging, and appropriate COMMIT/ROLLBACK logic.

Section D: Integration and Design (Exercises 31-35)

Exercise 31 -- Designing an Audit System [Advanced]

Design a complete audit system for Meridian Bank's CUSTOMER, ACCOUNT, and TRANSACTION tables. Your design should include:

  1. Audit table DDL (one audit table per source table, or a single unified audit table -- justify your choice).
  2. Triggers for all three tables covering INSERT, UPDATE, and DELETE.
  3. A stored procedure to query the audit trail for a specific customer (showing all changes to their data in chronological order).
  4. A view that summarizes audit activity by table, by day, for management reporting.

Exercise 32 -- Performance Comparison [Challenge]

Design an experiment to compare two implementations of the fund transfer operation:

Implementation A: A stored procedure (like meridian.transfer_funds in Section 12.11). Implementation B: The same logic executed as individual SQL statements from an application (simulating multiple round trips).

Describe: - The test environment setup. - The metrics you would collect (elapsed time, CPU time, number of lock acquisitions, log records generated). - How you would simulate network latency for Implementation B. - What results you would expect and why.

Exercise 33 -- View Refactoring [Intermediate]

You inherit a database with a deeply nested view structure:

  • v_level1 is based on meridian.account and meridian.customer (a join).
  • v_level2 is based on v_level1 with additional filtering and a join to meridian.branch.
  • v_level3 is based on v_level2 with aggregation (GROUP BY, SUM, COUNT).
  • v_level4 is based on v_level3 with additional filtering (HAVING equivalent as WHERE on the aggregated view).

A query against v_level4 is running slowly. The EXPLAIN shows the optimizer is not merging the view definitions effectively.

Propose a refactoring strategy. Should you flatten the views into a single view? Create an MQT? Rewrite as a stored procedure? Discuss the trade-offs of each approach.

Exercise 34 -- Trigger Migration [Advanced]

You are migrating Meridian Bank's database from DB2 for z/OS to DB2 for LUW. List all the trigger-related differences you would need to account for. Specifically address:

  1. Trigger body syntax differences.
  2. Transition variable and transition table behavior.
  3. Trigger ordering mechanisms.
  4. INSTEAD OF trigger support.
  5. Trigger debugging and monitoring tools.

For each difference, provide the z/OS syntax and the equivalent LUW syntax.

Exercise 35 -- Complete Module Design [Challenge]

Design and implement a complete "Customer Onboarding" module for Meridian Bank using views, triggers, and stored procedures:

  1. A stored procedure meridian.onboard_customer that: - Creates the customer record. - Creates an initial checking account with the opening deposit. - Creates an initial savings account with a $0 balance. - Assigns the customer to a branch. - Returns the new customer_id, both account_ids, and a welcome message.

  2. Views that the customer service application will use: - v_new_customer_summary showing recently onboarded customers. - v_onboarding_queue showing customers awaiting document verification.

  3. Triggers that fire during onboarding: - Audit trigger recording the customer creation. - Audit trigger recording each account creation. - A trigger that updates a branch_statistics table when new accounts are created.

Write all DDL and SQL PL code. Include error handling for every failure scenario you can identify.

Return to Chapter 12 | Continue to Quiz