BA372 — XML and Web Services
BA372 — Stored Procedures and Triggers)
(Based on notes and exercises by B. Marshall)
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."
- 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.
- 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 during the table change and hence, 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 stores a credit limit for each of its client as a business control. Credit limit
changes can be made by only a few individuals and any and all changes to the credit limit should be logged.
- Needed functionality:
- Must control access to changes to credit limits (authorization).
- Must log changes to credit limits.
- Must record denied attempts to change credit limits. (why?)
- Some implementation options:
- Code all this in a program; e.g., C#, which executes SQL commands.
- Write a (C#) program which calls a stored procedure and passes client_id, new_credit_limit, and operator parameters.
- Write a database trigger which automatically makes a log entry whenever credit limit changes are attempted or made.
- Problem: What are some considerations which factor into selecting one of these alternatives?:
- Modularity: are logical functions neatly subdivided?
- Data independence: oops: (business logic) code in the database.
- Extensibility: what is involved in implementing changes to the policy?
- What skills are needed to create each implementation? Can those skills be distributed across several developers?
- 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.
Problem: We must log all HTTP requests coming into our Web server. However, we are not interested in requests coming from our own developers and testers.
- How do we approach this problem without and with stored procedures?