Rule 6: Don t Delete a Horse That Has Run at Least One Race

[Previous] [Next]

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:

  • Using a trigger
  • Using a Visual Basic procedure

click to view at full size.

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

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.

Disabling the foreign key relationship

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.

click to view at full size.

Figure 4-15. A disabled relationship has a different pattern than a functional one.

Making the trigger

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

Testing the deactivate part of the trigger

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)

Testing the record orientation of the trigger

You might want to test the record orientation of the trigger too. This is easy. Just send a delete message that would delete a whole bunch of horses:

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.

The drawbacks of using such a trigger

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:

  • Prevent improper deletions of horses. We have replaced that function with our trigger.
  • Prevent improper insertions of RaceEntrants rows. These would be rows in which the value in the Horse column didn't refer to an equal value in the HorseId column of the Horses table. This function is no longer implemented.
  • Prevent improper updates of RaceEntrants rows. Such an update would change the value in the Horse column to one not present in the HorseId column of the Horses table. Like the preceding function, this function isn't implemented anymore.

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:

  • You can decide to create one or two new triggers, making them stop every attempt to insert or update any Horse row in the RaceEntrants table with a value that according to the rule is invalid. With this choice, you add to the maintenance burden.
  • You can decide to implement rule 6 as a Visual Basic procedure instead.

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



Designing for scalability with Microsoft Windows DNA
Designing for Scalability with Microsoft Windows DNA (DV-MPS Designing)
ISBN: 0735609683
EAN: 2147483647
Year: 2000
Pages: 133

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net