Testing Database Stored Procedures and Triggers

Mary Romero Sweeney
Independent Consultant

(CSTP Elective)

This tutorial counts as training towards the Certified Software Test Professional requirements.

Today's data-based applications increasingly use objects called stored procedures. These procedures contain application logic that was formerly on the front-end of the application. For reasons like performance and security, this logic is being moved to the application's backend. This change has made it increasingly likely that your testing effort is missing important application functionality. This course will tell you what you need to know about stored procedures: how to test them and, in addition, how they can be used to improve your testing efforts.

In addition you will learn about a special purpose stored procedure: a database trigger. Testing triggers can be tricky unless you know some basics. Strategies for effectively learning about and testing these critical objects will be covered.

This course is the third in the series on database testing. For best results, the student should know about database design (see course: Database Application Testing Fundamentals ) and basic SQL (see course: Structured Query Language for Test Professionals.

Outline/Learning Objectives

Introduction

SQL Review
- SQL Essentials Review
- Joins
- Views

Module 1: Creating and Testing Basic Stored Procedures

What is a stored procedure? Why are they important?
The basic stored procedure syntax
Creating your first stored procedure
Testing your simple stored procedure
Stored procedure parameters
Exercise 1

Module 2: Testing Stored Procedures using SQL Scripts

Creating test harnesses for Ad-hoc testing
Adding error handling for testing
Strategies for maintaining your SQL tests
Exercise 2

Module 3: Using Stored Procedures for Testing

Converting from an ad-hoc test to a stored procedure test
Data Driven Testing using a SQL Table
Demo: Accessing your stored procedure using a scripting language
Exercise 3

Module 4: Introduction to Triggers

What are Triggers?
Creating and Using a Trigger
Trigger Issues
Special Considerations for Testing Database Triggers
Exercise 4

Module 5: Database Security

Understanding database security
Common database hacks and how to protect against them
- SQL Injection
- Server vulnerability
Test cases for keeping your data secure
Exercise 5

Biography

Mary Sweeney has been developing, using, and testing relational database systems for 20 years. She's the author of Visual Basic for Testers (Apress, 2001) and several published articles on test automation topics. Mary is a college professor and also performs independent consultation and training (www.ExceedTraining.com). She has a bachelor's degree in mathematics and computer science from Seattle University.