A horse must not be deleted if it has run in a race. If it has, and if a user wants it deleted, it should be deactivated instead of deleted.
This rule states that a horse can be permanently deleted from the database if it has never run a race. With access to the data model, you can rephrase this clause as, "If the horse isn't represented in the RaceEntrants table, it can be deleted."
The idea behind this rule is to make it possible to erase horses that are entered in the database by mistake, and also horses that retired before even making a single race. But as soon as a horse has been entered in its first race, that option is gone.
That first part of the rule is easy enough to implement. In fact, it's already implemented in the database with a foreign key constraint. You can see this in Figure 4-13.
If we were happy with that first part of the rule, we could consider the rule already implemented. But we're not going to be happy until the second part of the rule is implemented as well.
If a user asks for the deletion of a horse, and that horse can't be deleted on account of this rule, the horse should be deactivated instead. This means that the Active column in the Horses table should be set to False. In other words, the deletion should be changed to an update.
Again, there are several possible ways to do this. We'll look at two options:
Figure 4-13. Fragment of a data model, showing that horses in the Horses tables are connected to races through a foreign key constraint on the RaceEntrants table.
Using a trigger might seem the obvious solution, but it won't work. As we have said before, a trigger fires after the fact. The horse is deleted from the database before the trigger fires. The problem is that the foreign key constraint, which is enforced before the fact, will prevent the deletion from being executed at all. Ergo, the trigger won't fire.
Let's do an experiment. Let's try to delete a horse that is represented in the RaceEntrants table. To make sure we don't sabotage the database, we'll start with a BEGIN TRANSACTION statement. Horse 365 is named Tuxedo, and he has made a number of starts.
BEGIN TRANSACTION DELETE FROM Horses WHERE HorseId = 365 Server: Msg 547, Level 16, State 1 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_RaceEntrants_Horses'. The conflict occurred in database 'MSPress_ADBRacing', table 'RaceEntrants', column 'Horse'. The statement has been aborted. |
As we expected, the deletion is never performed. It's denied by the foreign key constraint. The trigger didn't execute at all. Any trigger designed to fire on deletions in a certain table won't fire if a foreign key constraint prevents the deletion in the first place.
There is, however, a way to get around this. You can make the foreign key constraint impotent without taking it away. You can do this declaratively with SQL, and you can do it graphically with Enterprise Manager. The choice is yours.
You can disable a foreign key relationship easily by using SQL Server Enterprise Manager, which is a Microsoft Management Console (MMC) snap-in application. All you have to do is bring up SQL Server Enterprise Manager and expand the database in question, which for our sample application is MSPress_ADBRacing. Finally you just select Diagrams in the left pane console tree. The right pane displays any existing diagrams for the selected database. There you can double-click one of the diagrams displayed. What you're interested in is a diagram that includes the Horses table and the RaceEntrants table.
If you find no such diagram, you can easily create one. In either case, open the diagram and then right-click on the relationship line between the two tables. Click Properties in the shortcut menu to display the dialog box shown in Figure 4-14.
Select the Relationships tab and clear the Enable Relationship For INSERT And UPDATE check box, as you see in Figure 4-14.
Figure 4-14. You keep the foreign key constraint, but it's no longer effective.
Interestingly, as Figure 4-15 shows, a disabled relationship is shown with a striped pattern in diagrams. This makes it easy to separate it from ordinary relationships.
Figure 4-15. A disabled relationship has a different pattern than a functional one.
Now, with the foreign key constraint out of the way, it should be worthwhile to create a trigger. Let's start with one that does exactly the same job as the constraint. In view of what will come later, we'll make it different, but in one respect only. We'll make it record oriented.
Please make allowance for the fact that all rows of the trigger code won't fit in the space available on the page. To make the trigger code more readable, we have made some modifications that might make the trigger unable to run if you create it exactly as shown here.
CREATE TRIGGER trHorsesDeleteCheckEntrants ON Horses FOR DELETE AS -- First check that only one horse was deleted: IF @@ROWCOUNT > 1 BEGIN IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION RAISERROR ('Only one horse may be deleted at a time. ' + 'Aborted and rolled back.', 16, 1) RETURN END -- Then check whether the horse is represented in the -- RaceEntrants table: DECLARE @HorseId int SELECT @HorseId = HorseId FROM DELETED IF EXISTS (SELECT * FROM RaceEntrants WHERE Horse = @HorseId) BEGIN PRINT 'Horse can't be deleted. Is deactivated ' + 'instead. Deletion is aborted and rolled back.' ROLLBACK TRANSACTION UPDATE Horses SET Active = 0 WHERE HorseId = @HorseId END |
This trigger executes ROLLBACK TRANSACTION if more than one horse was deleted. Another strategy would be to raise the error only and give responsibility to the client process, which might be a Microsoft Transaction Server (MTS) object or a COM+ object, to issue the rollback on account of the error notification it receives. Such an object would then call SetAbort, which eventually would result in a rollback controlled by Microsoft Distributed Transaction Coordinator.9
Now, if you try to delete Tuxedo10 again, the trigger aborts the deletion and replaces it with an update, setting the Active column in Tuxedo's table row to 0, which is the same as False.
BEGIN TRANSACTION DELETE FROM Horses WHERE HorseId = 365 Horse can't be deleted. Is deactivated instead. Deletion is aborted and rolled back. (1 row(s) affected) |
Looking at the present state of the Tuxedo record, you'll find that the trigger actually kept Tuxedo in the database and set his Active value to 0:
SELECT HorseName, Active FROM Horses WHERE HorseId = 365 HorseName Active -------------------- ------ Tuxedo 0 (1(1 row(s) affected) |
BEGIN TRANSACTION DELETE FROM Horses WHERE Sex = 'h' Server: Msg 50000, Level 16, State 1 Only one horse may be deleted at a time. Aborted and rolled back. |
Looking back, you can easily remember that at first we found the foreign key constraint between the Horses and RaceEntrants tables to be a hindrance for a trigger solution. When we disabled the relationship between the two tables, we were able to make the trigger fire and do its thing.
Now that we have a solution to rule 6, we should be happy. But can we be happy? Not necessarily. The foreign key constraint that we disabled had more than one function. Let's review those functions:
It would be very bad indeed to leave things as they are right now. You must solve two new problems, which in fact didn't exist until you solved your first one. Or you must back out of the first solution and make the foreign key constraint functional again. How do you feel about this situation? If you think it's a choice between the plague and cholera, who are we to contradict you? Here are your two possible solutions:
In a two-tier environment, in which the Visual Basic procedure must reside in the client, only the first solution is a good one. In a three-tier environment, the second solution is wonderful. This is the one you should go for.
The code will be quite simple. Here's the pseudocode that shows what you'll have to do in the Delete procedure of your business object:
Check whether the HorseId is represented in RaceEntrants If not: Go on with the deletion Else: Perform an update instead |