BA372 — XML and Web Services
BA372 — Stored Procedures and Triggers)
(Based on notes and exercises by B. Marshall)
Case study: log all HTTP requests coming into our Web server. However, we are not interested in requests coming from our own developers and testers.
- Stored Procedures:
- Stored procedures are akin to functions (methods) 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: T(RANSACT)-SQL
- Oracle: PL/SQL
- DB2: SQL PL
- MySQL: SPL
- They can handle input and output parameters, execute multiple
steps, return result sets and return codes, and raise errors.
- Triggers are stored
procedures invoked by the DBMS when a specific type of event occurs. For
example, a trigger can be set to automatically 'fire' whenever records
in a particular table are inserted, updated or deleted.
- Great for logging.
- Oracle supports before, after, and
instead of triggers. SQL server supports after and instead of triggers.
- After and instead of 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 a temporary table called INSERTED.
- DELETEd records appear in a temporary table called DELETED.
- On UPDATE: old values are in the DELETED table; new values are in the INSERTED table.
- Triggers are powerful but should be used carefully because they can create a number of performance and dependency problems.
- Case: 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 C# program executing a series of SQL commands.
- A C# program which calls a stored
procedure using client_id, new_credit_limit, and operator parameters.
- A database trigger automatically logs credit limit changes whenever credit limit rows are INSERTed, UPDATEd, or DELETEd
in the database.
- Consider the following as they apply to each of these implementation options:
- Modularity: are logical functions neatly subdivided?
- Data independence: oops: (business logic) code in the database.
- Extensibility: what's involved in make various changes?
- Interoperability between platforms, languages, programs?
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
- 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,
- Are risks and controls affected by which
implementation option is chosen? Which people and code components are
involved in changing security-related functions?
- In-database processing is likely to be a lot faster than moving data in-and-out of the database and process these data in a program.
- Hohmann (p. 160): "One of the most generally accepted principles of enterprise application
architecture is to put business logic within the services layer and/or the domain model layer
of the architecture. However, while this is a good principle, it isn't an absolute rule that
should be dogmatically followed. Many times, a well-defined stored procedure or database trigger
is a far simpler and substantially more efficient solution. Moreover, you may find that the only
way to safely offer integration solutions to your customer is to put certain kinds of logic in
the database. I would rather rely on the database to invoke a stored procedure than rely on
a customer to remember to call the right API."
- How can we approach this problem?