BA372 - Stored Procedures and Triggers
- Stored Procedures:
- Stored procedures are “akin to functions or subroutines that perform database actions.” They are “modules of logic and database actions” stored in a DBMS. (Kroenke, Database Concepts, 2nd ed., p.136)
- They are written in languages which extend standard SQL. SQL Server uses TRANSACT-SQL and Oracle uses PL/SQL.
- They are able to: handle input and output parameters, execute multiple steps, return result sets and return codes, and raise errors.
- Triggers:
- Triggers are stored procedures invoked by the DBMS when specified events occur. For example, a trigger can be set to automatically “fire” whenever records are updated in a particular table.
- Oracle supports before, after, and instead of triggers while SQL server only supports after and instead of. Do you see the difference?
- Triggers can ‘see’ what happened during a table change using temporary tables that are created on-the-fly immediately before the trigger fires.
- INSERTed records appear in the temporary table called INSERTED.
- DELETEd records appear in the temporary table called DELETED.
- On an UPDATE, the old values of affected records are reported in the DELETED table and the new values appear in the INSERTED table. You could think of it this way: on an update, the DBMS completely deleted the old record and inserted a new one in order to change a field.
- Triggers are powerful but should be used carefully because they can create a number of performance and dependency problems.
- Modern database functionality allows us to define transactions.
- Affected records are locked to prevent negative concurrency effects.
- Logical units of work are either completed or rolled back. Thus if rows in two tables are affected by a logical unit of work, the DBMS ensures that:
- no other processes will change affected rows during the process, and
- either all or none of the changes are reflected in the database.
- Commands: BEGIN TRANSACTION, COMMIT, and ROLLBACK
- If the system fails (as in a power outage) while the transaction is in progress, the server will continue on, either committing or rolling back the process when it restarts.
- In complex computing environments, a transaction can affect databases stored in multiple geographical locations.
- Stored procedures and system architectures
- Important concepts:
- Good system architecture considers how to organize or deploy system components (hardware, software, data storage, business logic, security, development resources, etc.) to effectively or efficiently meet organizational needs.
- A system has data independence: when its data exists independently of the code which processes it. Why is this good? Does an Excel spreadsheet model exhibit data independence?
- A system is modular when it is neatly divided into subsystems.
- Extensible information systems can be modified or changed by adding features. The easier it is to extend it, the more extensible it is.
- Heterogeneous environments include components which perform the same function differently. Many IT organizations today have heterogeneous operating environments with multiple operating systems, data stores, programming languages, and server platforms.
- Imagine that a company tracks its clients and stores a credit limit for each client as a business control. Credit limit changes can be made by only a few individuals and any changes to the credit limit should be tracked. Needed functionality:
- Control updates to credit limits (authorization)
- Log changes to credit limits
- Record denied attempts to change credit limits (why?)
- Some implementation options:
- A VB program executes a series of SQL commands in a database transaction
- A VB program, web application, or web service calls a single stored procedure using client id and new amount parameters
- The VB program and stored procedure are decomposed to call subroutines which handle authentication and logging in callable subroutines
- A database trigger automatically logs credit limit changes whenever rows are INSERTed, UPDATEd, or DELETEd in the database.
- Consider the following as they apply to each of the implementation options.
- Discuss how the implementation options affect
- modularity (are logical functions neatly subdivided),
- data independence (oops, code in the database),
- extensibility (who has to be involved to make various changes),
- heterogeneity (multiple or new platform support), and
- system testing.
- If you divide the development task into interface requirements (screen design and implementation) vs. business logic (How is stored data affected? What controls are needed?), what skills are needed to create each implementation? Can those skills be distributed across several developers?
- Given that a database transaction should be used to ensure that all credit limit changes are correctly logged, compare the performance and reliability implications of each implementation option. (Bandwidth used, chance of leaving unresolved database locks, time to complete the transaction, database memory and lock usage, scalability, etc.)
- Are risks and controls affected by which implementation option is chosen? Which people and code components are involved in changing security-related functions?
