Scott Stanfield
Vertigo Software, Inc.
September 1998
Summary: Discusses the design of the Fitch & Mather database. (30 printed pages) Includes:
Introduction
Building the Data Model
Overall Database Design
Table Details
Table Design Notes
Triggers
Stored Procedures
Conclusion
About the Author
For More Information
This article describes the design of the Fitch & Mather database. It discusses the methods used to create a logical database based on Fitch & Mather's requirements. A physical implementation of the logical design was created in Microsoft® SQL Server™ 6.5. A list of stored procedures and triggers and what each one does is included in this document.
MSDN's Steve Kirk described a method to build a data model from use cases. The following is a quote from his article describing database design for Duwamish Books, Phase 1, an example application:
Statements of the business transactions to be modeled provide a starting point for the data model. The nouns in these statements can be thought of as data and the verbs as the processes that operate upon data. The nouns are developed into data entities with attributes that describe the entities and the relationships between entities. This suite of statements represents the primary business processes of the bookstore with the emphasized Nouns and Verbs forming the basic data model. Words used as NOUNS are in all caps and those used as VERBS are in italic caps. ("Designing a Simple Retail-Oriented Database")
We've adopted the approach suggested by Steve Kirk in the Duwamish Books application and applied the techniques here. Think of each sentence in bold a statement of fact, or a simplification of how different entities relate to each other.
From these statements, we should be able to derive a list of tables (or entities) and their relationships (one-to-many or many-to-many). This knowledge will be used to translate the statements to a logical diagram, and then to a physical diagram.
To make the test cases more realistic, we're modeling the database after a two-person trip to Comdex. This will enable us to test the database design against a real-world scenario.
A USER completes EXPENSE REPORTS.
Example Alice and Dolores incur expenses over a three-day period at Comdex. They both file expense reports for the trip.
An expense report is completed on an individual basis. Both managers and employees submit expense reports. They are both USERS of the system.
An EXPENSE REPORT has one or more LINE ITEMS.
Example Alice paid for the room at the Holiday Inn (1), a taxi cab to and from the airport (2, 3), and meals at the Spanish Steps Restaurant at Caesars Palace (4, 5). Her expense report will have five line items.
It doesn't make much sense to file an expense report with no line items, but the system will allow this. There is no easy way to enforce the cardinality that requires an ExpenseReport to have at least one item because, at some point during creation, an ExpenseReport will have zero line items. The expense report total amount is derived from the sum of the LINE ITEMS, so an expense report with no line items will have a reimbursement amount of $0.00.
Each LINE ITEM falls into a single EXPENSE CATEGORY.
Example The hotel room expense will fall under the "Room" category. All meals are entered under the "Food" category. Any travel expense, including plane, bus and taxi cabs, are filed under the "Travel" category.
The expense report template has columns that correspond to categories in the database. If a line item doesn't match a category, the entire transaction will be aborted and the expense report won't be recorded.
Some USERS have a maximum amount for each EXPENSE REPORT.
Example Because Alice is the VP of Sales, she has no expense report limit. Dolores, on the other hand, is limited to a maximum of $500 per expense report and a $100 limit on entertainment expenses.
Regardless of the line item expenses, we will restrict some users to a maximum reimbursement limit. Notice that this is a limit only on an individual expense report. Nothing in the database would prevent users from submitting 30 expense reports in one month. Monthly limits would be a nice enhancement for a future version.
Some USERS have spending LIMITS for some CATEGORIES.
Example Dolores does a minimal amount of entertaining for clients, so she is limited to $100 for entertainment expenses. Jack, on the other hand, frequently "wines and dines" prospective clients, so his entertainment limit is $300.
Fitch & Mather would like to apply spending limits on individual categories for particular users. This would be useful if a particular employee, for example, is allowed to expense their fuel costs, but only up to $50.
Some CATEGORIES have global limits.
Example No one, not even managers, can submit expense reports with fuel expenses greater than $100.
If necessary, a limit can be applied on a category basis, across the board. No line item would be allowed to exceed this limit.
A MANAGER approves or denies their USER's EXPENSE REPORTS.
Example Alice approves Dolores's expense report. She denies Jack's expense report because the description for one of the line items isn't clear. Because she doesn't report to any other administrative entity, she approves her own expense report.
It becomes clear from this example that managers wear two hats. On one hand, they submit expense reports for reimbursement just like everyone else. On the other hand, they are responsible for approving expenses.
There is an interesting case to examine. If a manager doesn't have a boss, who approves their expense report? The database will allow the manager to approve his or her own expense report in this case.
Because managers and employees are equivalent, a single table called USERS will be sufficient to track all employees.
An EXPENSE REPORT is in one of three STATES: waiting for approval, approved, or denied.
Example When Dolores uploads her expense report, it is entered into the database and tagged as "waiting for approval." Alice can review the report and change the status to "approved" or "denied." Either of these actions will generate an e-mail message to Dolores to notify her of the change.
We'll put the possible states into a separate table called Status. Future versions of FMCorp might add more states, like "waiting for receipts" or "routed to accounting."
The FMCorp database is a SQL Server database. SQL Server offers a rich set of tools for database architecture. The FMCorp database takes advantage of many ANSI-SQL 92 features.
Figure 1 shows the tables in FMCorp.
Figure 1. FMCorp database design
A key icon in the first column of the table identifies the primary key for each table. A line connecting the foreign key (FK) to the referring table indicates foreign keys. You can locate the FK by finding the infinity sign.
The infinity sign represents the "many" side in a one-to-many relationship. For example, a user can, theoretically, submit an infinite number of expense reports. You read this relationship by following the link between Users and ExpenseReports.
This diagram was generated using the Database Diagram option in the Microsoft Visual InterDev™ Web development system. I added the text boxes by hand. The layout and text is actually saved in the database.
If you are familiar with designs in Visual InterDev, you might notice that the relationship lines are a thatched color instead of solid. That's because we declared the relationship between each table in SQL script, but dropped the automatic enforcement. This is a feature of SQL 92 called Declarative Referential Integrity, or DRI. Later you'll see how triggers are more powerful than the automatic DRI.
In Figure 2, you'll see the table names and relations, but not any other information about each column. This section describes in detail the implementation and purpose of each table. The actual SQL create table statement is shown as well because the declarative referential integrity is defined in these statements.
The Users table stores the list of employees that are allowed to use the system. Figure 2 shows the column properties in table properties in Visual InterDev.
Figure 2. Column properties for the Users table
The actual SQL statement used to create the Users table is shown here:
create table Users
(
UserID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
Password varchar(50) NOT NULL DEFAULT ('password'),
NTLogin varchar(50) NULL,
ManagerID int NOT NULL REFERENCES Users(UserID),
Email varchar(50) NOT NULL UNIQUE,
Limit smallmoney NULL DEFAULT(NULL)
)
There is always one special row in the database with a UserID of 1: the Administrator. It is added when you run the FMCorp.sql script. The delete trigger on the Users table prevents it from being deleted. If it were deleted, it would not be possible to add a new user using the Web site because the ManagerID must refer to an existing user.
How does SQL Server prevent this scenario? SQL Server allows you to insert a record into a table and reference the new key in the same statement. Here's the line from the "sp_populate" query that initializes the table:
Insert Into Users (FirstName,LastName,password,ntlogin,ManagerID,email)
Values('Admin','Administrator','',NULL, 1, 'info@fmcorp.com')
We know that this will be the first record in the Users table, and that the UserID identity column seed starts at 1 by default. Therefore, a 1 is placed in the ManagerID slot.
The same technique is used to add Alice Campbell because she managers herself. If we had to add her to the database using the AdminUsers Web page, we'd have to make her manager Admin first, and then change it to Campbell later.
The default contents of the Users table are shown in Figure 3.
Figure 3. Initial contents of the Users table
The ExpenseReports table holds expense reports. Figure 4 shows the column properties for the each expense report.
Figure 4. Column properties for ExpenseReports table
create table ExpenseReports
(
ExpenseReportID int NOT NULL IDENTITY PRIMARY KEY,
UserID int NOT NULL REFERENCES Users(UserID),
Description varchar(255) NOT NULL,
Submitted smalldatetime NOT NULL DEFAULT (getdate()),
StatusID int NOT NULL DEFAULT (1)
REFERENCES StatusTypes (StatusID),
StatusDate smalldatetime NOT NULL DEFAULT (getdate())
)
The default data added by the stored procedure sp_populate is shown in Figure 5.
Figure 5. Initial contents of the ExpenseReports table
The Items table stores the individual line items, or expenses, in a given expense report. Each entry should correspond to a single receipt, although this system doesn't track receipts.
Figure 6. Column properties for Items table
create table Items
(
ItemID int NOT NULL IDENTITY PRIMARY KEY,
ExpenseReportID int NOT NULL REFERENCES
ExpenseReports (ExpenseReportID),
Description varchar(255) NOT NULL,
ExpenseDate smalldatetime NOT NULL DEFAULT (getdate()),
Amount smallmoney NOT NULL DEFAULT (0),
CategoryID int NOT NULL REFERENCES
ExpenseCategories(CategoryID)
DEFAULT (1)
)
The initial data for this table is shown here in Figure 7.
Figure 7. Initial contents of the Items table
StatusTypes holds a list of valid states for an expense report. There are currently only three states, so it is a simple table.
Figure 8. Column properties for StatusTypes table
create table StatusTypes
(
StatusID int NOT NULL PRIMARY KEY,
Description varchar(30) NOT NULL
)
The StatusTypes table is a "range" table. It restricts the StatusID column in the ExpenseReports table to a small set of valid numbers. We could also enforce this with an insert/update trigger in the ExpenseReports table, but then we'd need a place to store the description. Storing the description in this table allows for easier localization and opens the system up for new status types.
The only values used in FMCorp are added when the database is initially created. They are shown in Figure 9.
Figure 9. Initial values for StatusTypes
The Limits table allows the administrator to enforce dollar limits on a per-user and per-category basis. An entry in this table will restrict a particular user from submitting an expense report with a large dollar amount for a particular category.
Figure 10. Column properties for Limits table
create table Limits
(
UserID int NOT NULL REFERENCES Users(UserID),
CategoryID int NOT NULL REFERENCES
ExpenseCategories(CategoryID) DEFAULT (1),
Limit smallmoney NOT NULL DEFAULT (0),
constraint Limits_PK primary key (UserID, CategoryID)
)
Because this table's primary key is a composite of UserID and CategoryID, the separate constraint line at the bottom is necessary to define this. Usually when you see a composite key made up of two foreign keys, it is a good indication that this table is in the middle of a many-to-many relationship. In this case, there is a many-to-many relationship between Users and Categories. The attribute on that relationship, in this case, is a dollar limit.
In order to test the robustness of the triggers and this table, you can submit an expense report for Jack that has a $500 Room line item. Next, use the AdminLimits table to limit his room expenses to only $200. Try to submit the report and the new transaction will be rolled back.
This table has no initial entries.
Figure 11 shows the simple ExpenseCategories table. In addition to working as a simple list of possible expense categories, it also stores the global dollar limit for each category.
Figure 11. Column properties for ExpenseCategories table
create table ExpenseCategories
(
CategoryID int NOT NULL PRIMARY KEY,
Description varchar(30) NOT NULL,
Limit smallmoney NULL DEFAULT(NULL)
)
The initial values for this table are shown in Figure 12. The descriptions for CategoryID 2 through 7 correspond to the columns in the Excel expense report template.
Figure 12. Initial contents of the Limits table
Some tips and tricks for table design are presented in the following section.
Which is more readable? This:
CREATE TABLE "dbo"."Limits" (
"UID" "int" NOT NULL ,
"ETypeID" "int" NOT NULL CONSTRAINT "DF__Limits__ETypeID__7FCB01AD" DEFAULT (1),
"DollarLimit" "smallmoney" NOT NULL
CONSTRAINT "DF__Limits__DollarLi__00BF25E6" DEFAULT (0),
CONSTRAINT "Limits_PK" PRIMARY KEY CLUSTERED
(
"UID",
"ETypeID"
),
CONSTRAINT "FK__Limits__ETypeID__7ED6DD74" FOREIGN KEY
(
"ETypeID"
) REFERENCES "dbo"."ExpenseTypes" (
"ETypeID"
),
CONSTRAINT "FK__Limits__UID__7DE2B93B" FOREIGN KEY
(
"UID"
) REFERENCES "dbo"."Users" (
"UID"
)
)
GO
Or this:
create table Limits
(
UID int NOT NULL REFERENCES Users(UID),
ETypeID int NOT NULL REFERENCES ExpenseTypes(ETypeID) DEFAULT (1),
DollarLimit smallmoney NOT NULL DEFAULT (0),
constraint Limits_PK primary key (UID, ETypeID),
)
go
The scripting feature in SQL Server creates the first version. I hand-tuned each create table statement into the form shown in the second version.
I find the second version is more readable because the foreign keys are identified on the same line with the column name. The extra double quotation marks are removed because none of our column names include strange characters that need quoting.
Each foreign key constraint has a name. SQL Server generates one for us if we don't specify a name, but is "DF__Limits__DollarLi__00BF25E6" very useful? By removing the constraint names, we'll let SQL Server silently generate these names for us and we'll never need to see them again.
You might be saying that the constraint names will appear in a raised error message, but later you'll see that we actually turn off all the DRI constraints. We do this in order to take advantage of advanced triggers.
Visual InterDev still uses the DRI to draw the lines between tables on the database design editor. Plus the declared foreign keys are self-documenting.
Before identity columns were added to SQL Server, implementing a similar feature was difficult and didn't scale well. Some techniques required a special table to keep track of the current maximum value and an atomic stored procedure to read and update the value.
Now identity columns make declaring a primary key very easy. You can use them in tough situations that would necessitate the use of a composite key (like the example in the preceding Items discussion) or a primary key based on text (using the LastName field in the Users table).
Because all of our identity primary keys are 4-byte integers, they don't take up too much space as a percentage of total bytes in a record. They're also easy to pass between stored procedures and the middle-tier objects that use them.
One more benefit of identity columns is that their values can't change, so you don't have to verify their uniqueness in an update trigger.
Triggers are the unsung heroes of relational databases. They are the last line of defense against bad data. We use them here to enforce our referential integrity and business rules.
Let's talk business rules. Some three-tier pundits want all the business rules enforced in the middle tier. Forget the middle tier—it's too cumbersome. The best place to enforce business rules that deal with data is right before the data is written to the database. That way, if you have extra add-on applications later that don't use the middle-tier objects, they'll get caught by the triggers.
Here's a short list of the business rules we have to enforce:
Custom triggers are the only way to effectively enforce these business rules while still maintaining an open database.
There is one downside to using triggers to enforce the business rules: They'll never fire if DRI is enabled!
This happens because constraint violations are tested before triggers fire. This means you can't use FOREIGN KEY constraints in the table definition if you want triggers to work. You have to alter each table to remove their DRI using the NO CHECK option. The following SQL script in FMCorp.sql illustrates how DRI is disabled:
alter table Users NOCHECK CONSTRAINT ALL
alter table ExpenseCategories NOCHECK CONSTRAINT ALL
alter table Limits NOCHECK CONSTRAINT ALL
alter table ExpenseReports NOCHECK CONSTRAINT ALL
alter table Items NOCHECK CONSTRAINT ALL
alter table StatusTypes NOCHECK CONSTRAINT ALL
We still use DRI to self-document the tables; it just won't be used to enforce foreign-key integrity.
Now we have to reconstruct all the work DRI did for us through custom triggers. Fortunately, there's an easy way to do this.
Now that DRI is out of the picture, we have to do the work by hand. How do you know where to add triggers? Follow these steps:
Figure 13: Spot the triggers
Figure 13 illustrates the three possibilities for triggers (delete, insert, or both).
Each "D" or "IU" will correspond to a fairly generic block of code in the trigger. Once we handle the DRI constraints in the first part of the trigger, the business rules can be enforced in the second part.
In our trigger walkthrough, we'll encounter three blocks of code that enforce the DRI:
Here's an example of a typical block of code that enforces foreign key constraints when you try to delete records that comprise the primary key side. Use this code when you don't want a cascading delete. In this example, we'll abort the whole transaction if someone attempts to delete a StatusID that is currently in use by an ExpenseReport:
Create Trigger tr_StatusTypes_d
On StatusTypes
For Delete
As
-- rollback if there are any FK ExpenseReports.StatusID
if exists (Select * from ExpenseReports where StatusID in
(select StatusID from deleted) )
begin
raiserror ('FK ExpenseReports.StatusID->StatusTypes.StatusID', 16, 1)
rollback tran
return
end
Think of this block of trigger code as enforcing the PK-FK relationship from the PK side. Stated in more generic terms:
Create Trigger tr_PK_TABLE_d
On StatusTypes
For Delete
As
-- rollback if there are any FK ExpenseReports.StatusID
if exists (Select * from FK_TABLE where FK in
(select PK from deleted) )
begin
raiserror ('FK ExpenseReports.StatusID->StatusTypes.StatusID', 16, 1)
rollback tran
return
end
When the delete of one record should trigger the delete of more records, we simulate a cascading delete with the following code:
-- cascade the delete to the limits table
declare @counter int
delete limits
from limits, deleted
where limits.UserID = deleted.UserID
select @counter =@@ROWCOUNT
if (@counter > 0)
raiserror('%d Limit rows were deleted due to delete in Users table',
10, 1, @counter)
Whether to cascade the deletes is purely a design issue. In some cases, it makes sense. When a user is deleted from the User table, their limits should be deleted too. However, if a user is managing other employees, their employees shouldn't be deleted!
Note the difference in the raiserror numbers. In the cascade delete case, it's nice to know how many related records were removed. We'll use an error level of 10 to indicate this is a message, not a warning. Rolling back a transaction to prevent a cascading delete is a little more severe. An error level of 16 indicates that we're rolling back the transaction.
A generic formulation for a cascading delete would look like this:
declare @counter int
delete FK_TABLE
from FK_TABLE, deleted
where FK_TABLE.FK = deleted.PK
select @counter =@@ROWCOUNT
if (@counter > 0)
raiserror('%d FK_TABLE rows were deleted due to delete in PK_TABLE',
10, 1, @counter)
An Insert/Update trigger is placed on the foreign key side to make sure any updated foreign keys refer to real values. Here's an example from the Users table:
Create Trigger tr_Users_iu
On Users
For Insert, Update
As
if update(ManagerID)
if exists (select * from inserted where inserted.ManagerID not in
(select Users.UserID from Users) )
begin
raiserror ('New or updated User.ManagerID must exist.', 16, 1)
rollback tran
end
Remember User.ManagerID is a foreign key to User.UserID. Just because the primary key table and foreign key table are the same doesn't make the trigger work any differently.
A more generic formulation of this block of code would look like this:
if update (FK)
if exists (select * from inserted where inserted.FK not in
(select PK from PK_TABLE) )
begin
raiserror ('New or updated FK_TABLE.FK must exist.', 16, 1)
rollback tran
end
Think of this block of trigger code as enforcing the PK-FK relationship from the FK side.
The trigger names adhere to a pretty simple naming convention that looks like this:
tr_[table name]_[i]|[u]|[d]
All the triggers start with "tr_", followed by the name, and then a letter indicating what kind of action calls the trigger (insert, update, or delete). We were able to handle the insert and update cases in the same block of code. Some of the triggers are shown in Figure 14.
Another rule of thumb is that every FK should have a line item in the Insert/Update trigger. Also, every FK-PK relationship in the primary table should either cascade delete (Users.UserID->Limits.UserID) or roll the whole transaction back (Users.UserID->ExpenseReports).
Figure 14. Triggers in Data View
| Table name | Delete trigger | Insert/Update trigger |
| ExpenseCategories | tr_ExpenseCategories_d | |
| ExpenseReports | tr_ExpenseReports_d | tr_ExpenseReports_iu |
| Items | tr_Items_iu | |
| Limits | tr_Limits_iu | |
| StatusTypes | tr_StatusTypes_d | |
| Users | tr_Users_d | tr_Users_iu |
We don't allow an ExpenseCategory to be deleted if any items or limits are currently referring to it. Notice the two generic SQL blocks that enforce the PK-FK relationship:
Create Trigger tr_ExpenseCategories_d
On dbo.ExpenseCategories
For Delete
As
-- rollback if there are any Items that use this type
if exists (select * from Items where CategoryID in
(select CategoryID from deleted) )
begin
raiserror ('Can''t delete ExpenseCategories record(s) because of
FK Items.CategoryID', 16, 1)
rollback tran
return
end
if exists (select * from Limits where CategoryID in
(select CategoryID from deleted) )
begin
raiserror ('Can''t delete ExpenseCategories record(s) because of
FK Limits.CategoryID', 16, 1)
rollback tran
return
end
If an expense report is deleted, remove the related Items. The following code is an example of a cascading delete:
Create Trigger tr_ExpenseReports_d
On dbo.ExpenseReports
For Delete
As
-- cascade the delete to the Items and tracklog table
declare @counter int
delete Items
from Items, deleted
where Items.ExpenseReportID = deleted.ExpenseReportID
select @counter = @@ROWCOUNT
if (@counter > 0)
raiserror('%d Items rows were deleted due to delete in ExpenseReports table',
10, 1, @counter)
When a new expense report is added, make sure the UserID and StatusIDs are valid. The following are two examples of the use of the generic insert/update SQL:
Create Trigger tr_ExpenseReports_iu
On dbo.ExpenseReports
For Insert, Update
As
if update(UserID)
if exists (select * from inserted where inserted.UserID not in
(select Users.UserID from Users) )
begin
raiserror ('New or updated ExpenseReport.UserID must exist.', 16, 1)
rollback tran
return
end
if update(StatusID)
if exists (select * from inserted where inserted.StatusID not in
(select StatusID from StatusTypes) )
begin
raiserror ('New or updated ExpenseReport.StatusID must exist.', 16, 1)
rollback tran
return
end
This is by far the largest trigger. The first two blocks of code ensure new items have a valid ExpenseReportID and are assigned to a valid CategoryID.
The next section of code enforces three business rules:
Before we bother checking the limits, the trigger continues only if the Amount column is being updated in this batch. Because of the way the rest of the logic is coded, we can only continue if a single expense report is being changed. Because there is only one expense report in the batch, we can look up the one and only user and store the ID in @UserID:
Create Trigger tr_Items_iu
On dbo.Items
For Insert, Update
As
if update(ExpenseReportID)
if exists (select * from inserted where inserted.ExpenseReportID not in
(select ExpenseReportID from ExpenseReports) )
begin
raiserror ('The Item.ExpenseReportID does not exist.', 16, 1)
rollback tran
return
end
if update(CategoryID)
if exists (select * from inserted where inserted.CategoryID not in
(select CategoryID from ExpenseCategories) )
begin
raiserror ('The Item.CategoryID does not exist.', 16, 1)
rollback tran
return
end
if update(Amount)
begin
-- The following code assumes only one unique ExpenseReport
-- (and therefore one user) is part of the transaction.
-- Rollback if this is not the case.
--
declare @result int
declare @UserID int
select @result = count (distinct ExpenseReportID)
from inserted
if @result > 1
begin
raiserror ('Only one expense report in a batch can update the Amount column.',
16, 1)
rollback tran
return
end
-- Lookup the UserID for this batch
--
select @UserID = UserID
from ExpenseReports er, inserted i
where er.ExpenseReportID = i.ExpenseReportID
-- Abort if this new ExpenseItem exceeds predefined individual limits
--
select @result = count(*)
from inserted i, Limits l, ExpenseReports e
where i.ExpenseReportID = e.ExpenseReportID
and l.UserID = @UserID
and Amount > Limit
if @result >= 1
begin
raiserror ('Expense item exceeds limits in this category for this user',
16, 1)
rollback tran
return
end
-- Abort if this ExpenseItem pushes the user over their designated total
-- ExpenseReport limit
declare @total smallmoney
select @total = sum(Items.Amount)
from inserted i, Items
where i.ExpenseReportID = Items.ExpenseReportID
select @total = @total + sum(Amount)
from inserted i
select @result = count(*)
from Users
where UserID = @UserID
and @total > Limit
if @result >= 1
begin
raiserror ('Expense report exceeds spending limits for this user', 16, 1)
rollback tran
return
end
-- Abort if this ExpenseItem exceeds the global limits defined in the -- ExpenseCategories table
--
select @result = count(*)
from inserted i, ExpenseCategories ec
where i.CategoryID = ec.CategoryID
and Amount > Limit
if @result >= 1
begin
raiserror ('Item exceeds spending limits for this category.', 16, 1)
rollback tran
return
end
endThe Limits table trigger ensures valid UserID and CategoryIDs for a new Limits record:
Create Trigger tr_Limits_iu
On dbo.Limits
For Insert, Update
As
if update(UserID)
if exists (select * from inserted where inserted.UserID not in
(select Users.UserID from users) )
begin
raiserror ('No matching User found. Statement will be aborted.', 16, 1)
rollback tran
return
end
if update(CategoryID)
if exists (select * from inserted where inserted.CategoryID not in
(select ExpenseCategories.CategoryID from ExpenseCategories) )
begin
raiserror ('No matching ExpenseCategory found.', 16, 1)
rollback tran
end
This simple trigger makes sure status records aren't deleted if any expense reports are using that status type:
Create Trigger tr_StatusTypes_d
On dbo.StatusTypes
For Delete
As
-- rollback if there are any FK ExpenseReports.StatusID
if exists (Select * from ExpenseReports where StatusID in
(select StatusID from deleted) )
begin
raiserror ('FK ExpenseReports.StatusID->StatusTypes.StatusID', 16, 1)
rollback tran
return
end
The first SQL block rolls back the transaction if an attempt is made to delete a user who has expense reports logged in the system. The second SQL block doesn't allow the deletion of a manager. The employees under that manager must be removed first.
The third block is special case: It doesn't allow anyone to delete the administration user (UserID = 1).
If the first three conditions are met, the related limit records are deleted:
Create Trigger tr_Users_d
On dbo.Users
For Delete
As
-- rollback if this user has any expense reports
if exists (select * from ExpenseReports where UserID in
(select UserID from deleted) )
begin
raiserror ('Can''t delete a User that has any Expense Reports', 16, 1)
rollback tran
return
end
-- rollback if this user is a manager
if exists (select * from Users where ManagerID in
(select UserID from deleted) )
begin
raiserror('Can''t delete a User that is managing other employees.', 16, 1)
rollback tran
return
end
-- don't allow deletion of the "root" user
if exists (select * from deleted where UserID=1)
begin
raiserror('Can''t remove the Admin user.', 16, 1)
rollback tran
return
end
-- cascade the delete to the limits table
declare @counter int
delete limits
from limits, deleted
where limits.UserID = deleted.UserID
select @counter =@@ROWCOUNT
if (@counter > 0)
raiserror('%d Limit rows were deleted due to delete in Users table',
10, 1, @counter)
The last trigger in the group prevents new users with invalid ManagerIDs from being added to the system:
Create Trigger tr_Users_iu
On dbo.Users
For Insert, Update
As
-- Don't bother checking for updated UserID;
-- It's an Identity column so it can't change.
if update(ManagerID)
if exists (select * from inserted where inserted.ManagerID not in
(select Users.UserID from Users) )
begin
raiserror ('New or updated User.ManagerID must exist.', 16, 1)
rollback tran
end
One of our triggers enforces a rule that doesn't allow you to delete a user if they happen to be a manager.
We could have handled this case in a couple of ways:
They all have drawbacks. We decided to punt the whole issue and let the system administrators deal with the problem on a case-by-case basis.
Think about what this situation really means: A manager with maybe five people in her department leaves the company. You need to add the replacement manager, and then walk through all five users and change their manager in the drop-down combo box. Now you can delete the manager who walked. Not a big deal.
Other options would be to feed all deletes and inserts through stored procedures that would handle the dirty work. This doesn't work well for future applications that might write directly to the database, expecting triggers or DRI to handle the exceptions.
What's the best way to test the triggers? Use the marked-up printout and mentally walk through each line that connects the table. Because the lines are essentially bi-directional, you have to come up with test cases that test both directions.
For example, when examining the relationship between Users and ExpenseReports, ask yourself these questions:
Teach Yourself Transact-SQL in 21 Days (McEwan & Soloman) from SAMS Publishing for trigger syntax and examples.
Inside Microsoft SQL Server 6.5 (Soukup) from Microsoft Press for the "alter table" syntax. See pps. 572-573.
The FMCorp database contains a small set of stored procedures that automate the handling of expense reports and users, and management of the database as a whole. They include the following.
| Stored procedure name | Contents | Used by |
| sp_GetExpenseItems | Returns all expense items, including the category of each, for a particular expense report. | Web page: ReportDetails.asp |
| sp_GetExpenseReport | Returns expense report details, including the total amount and the user who submitted the report. | Web page: ReportDetails.asp |
| sp_GetUserID | Returns the UserID that matches a given e-mail address. | Component: ExpenseReadDB GetUserID |
| sp_GetMgrSummary | Returns a list of expense reports for a manager's employees. | Web page: MgrReportList.cpp |
| sp_GetUserSummary | Returns a list of expense reports submitted by a given user. | Web page: UserReportList.cpp |
| sp_NewExpenseItem | Adds a new item to an expense report. | Component: ExpenseWriteDB NewExpenseReport() |
| sp_NewExpenseReport | Creates a new expense report and returns the ExpenseReportID. | Component: ExpenseWriteDB AddExpenseItem() |
| sp_Login | Validates an e-mail/password combination for login. | Component: ExpenseReadDB Login() |
| sp_NTLogin | Validates a Windows NT domain and user name combination for login. | Component: ExpenseReadDB NTLogin() |
| sp_populate | Used during the database creation to populate the database with fictitious employees and expense reports. Also populates the StatusTypes and ExpenseCategories tables. | FMCorp.sql setup script |
The developers decided to use stored procedures for these retrieval operations because of the performance benefit. Stored procedures are precompiled collections of SQL statements and control-of-flow language that execute very quickly. Stored procedures are stored within a database; can be executed with one call from an application; and accept user-declared variables, conditional execution, and other powerful programming features.
The best practices you can take away from this article include:
Scott Stanfield is the president of Vertigo Software, Inc. Vertigo Software is a San Francisco Bay Area-based consulting firm that specializes in the design and development of Windows DNA applications and components. He can be reached at scott@vertigosoftware.com or on the Web at http://www.vertigosoftware.com/.