BA372 — XML and Web Services
BA372 — Stored Procedures and Triggers)
(Partially based on notes and exercises by B. Marshall)
- Stored Procedures:
- Stored procedures are in-database functions (methods). They are modules of logic and database actions stored and executed in(side) 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.
- In other words: they have full programming capabilities.
- Triggers are stored procedures which are automatically invoked by the DBMS when a specific type of event occurs. For
example, a trigger can be set to automatically execute 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 with care because they can create a number of performance and dependency problems.
- Case: Imagine a table which contains data on a workers' performance reviews and another table which stores events associated with those performance reviews.
We would like a process which automatically updates the status of the performance review when a certain type of event happens; for instance, when
an event of type 8, 9 or 99 occurs, the status of the performance review must be set to that value. All other event types do not result in a status update.
We know how to do 1. Let's try option 3:
- Store all functionality in a C# program which interacts with the database through SQL.
- Create a stored procedure in the database that updates the performance review table and call that stored procedure from a C# program.
- Create a trigger in the database which will automatically update the performance review table when an event of a certain type is entered in the events table.
- Set up first: create the tables if they already exist:
-- Drop the tables if they exist
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.perf_reviews') AND type IN (N'U'))
DROP TABLE dbo.perf_reviews;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.perf_review_events') AND type IN (N'U'))
DROP TABLE dbo.perf_review_events;
-- Create new ones
CREATE TABLE dbo.perf_reviews
perf_review_id INT NOT NULL,
worker_id INT NOT NULL,
manager_id INT NOT NULL,
review_year INT NOT NULL,
status_id INT NOT NULL
CREATE TABLE perf_review_events
pref_review_event_id INT NOT NULL identity(1,1),
perf_review_id INT NOT NULL,
event_date DATETIME NOT NULL,
event_type_id INT NOT NULL,
event_comment VARCHAR(256) NULL
--PKs and FKs skipped
- Next: fill the tables with some data:
INSERT INTO dbo.perf_reviews (perf_review_id, worker_id, manager_id, review_year, status_id) VALUES(1, 101, 201, 2017, 99);
INSERT INTO dbo.perf_reviews (perf_review_id, worker_id, manager_id, review_year, status_id) VALUES(2, 102, 201, 2017, 99);
INSERT INTO dbo.perf_reviews (perf_review_id, worker_id, manager_id, review_year, status_id) VALUES(3, 101, 201, 2018, 0);
INSERT INTO dbo.perf_reviews (perf_review_id, worker_id, manager_id, review_year, status_id) VALUES(4, 102, 201, 2018, 0);
INSERT INTO dbo.perf_reviews (perf_review_id, worker_id, manager_id, review_year, status_id) VALUES(5, 103, 202, 2018, 0);
INSERT INTO perf_review_events (perf_review_id, event_date, event_type_id, event_comment) VALUES(1, '01/01/2018', 99, null);
INSERT INTO perf_review_events (perf_review_id, event_date, event_type_id, event_comment) VALUES(2, '01/01/2018', 99, 'completed in second round');
INSERT INTO perf_review_events (perf_review_id, event_date, event_type_id, event_comment) VALUES(3, '05/01/2018', 0, null);
INSERT INTO perf_review_events (perf_review_id, event_date, event_type_id, event_comment) VALUES(4, '05/01/2018', 0, null);
INSERT INTO perf_review_events (perf_review_id, event_date, event_type_id, event_comment) VALUES(5, '04/15/2018', 0, null);
- BTW, we have at least six(!) methods for loading data into a database:
Next, set up the trigger:
- Use a fill-in-the-table GUI.
- Use insert statements in a database GUI client.
- Use insert statements in a database command line interface (CLI).
- Store all insert statements in a file and source the file in the CLI.
- Write a program which issues insert statements against the database.
- Use direct (aka bulk) load of a file with record values; e.g., csv file.
--drop the trigger if it already exists
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[perf_review_events]') AND type IN (N'U'))
DROP TRIGGER [dbo].[perf_review_events]
--create the trigger
CREATE TRIGGER dbo.UpdatePerfReviewStatus
DECLARE @review_id int;
DECLARE @event_type int;
SELECT @review_id = perf_review_id FROM INSERTED;
SELECT @event_type = event_type_id FROM INSERTED;
--note: this coding does NOT cover for multi-row inserts
IF @event_type = 8 OR @event_type = 9 OR @event_type = 99
SET status_id = @event_type
WHERE perf_review_id = @review_id
Try this out in SQL Server Management Studio (See Assignment 2) and test with the following:
--insert an event with event_type = 9
INSERT INTO perf_review_events (perf_review_id, event_date, event_type_id, event_comment) VALUES(5, '04/15/2018', 9, null);
--then see what it did the perf_review
SELECT * FROM perf_reviews where perf_review_id = 5;
Case: Imagine that a company stores a credit limit for each of its clients as a business control. Furthermore, credit limit
changes can be made by only a few individuals and any and all changes to credit limits should be logged.
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."
- 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?)
- Our options:
- Code all this in a program; e.g., C#, which interacts with the database though SQL.
- Write a 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 with and without stored procedures/triggers?