Rule 4: Only One Kind of Sex Change Allowed

[Previous] [Next]

Only one kind of sex change is allowed: from male to gelding, that is, from h (hingst = male) to v (vallack = gelding).

Rule 4 is a bit trickier than the preceding three. Just as with the three rules we've talked about so far, you can support this rule in the database. But if you do, you leave the realm of ANSI SQL standards. To implement this rule in the database, you must use a trigger.² Even though many modern database systems support triggers, they do it in different ways. Therefore, any rule supported by a trigger in the database is particular to that DBMS. If you move your database to another DBMS, you'll have to rewrite all your triggers.³

So here we describe two different solutions:

• A trigger solution in the database.
• Another solution away from the database. For us, this means a Microsoft Visual Basic solution. For you, it might mean a Microsoft Visual C++, a Java, a Delphi, or even a COBOL solution.

The Trigger Solution

A trigger is a special kind of stored procedure. Like an ordinary stored procedure, a trigger is written in some extension of SQL. In the case of SQL Server, this extended language is named Transact-SQL and is a heritage from the old Sybase times.⁴

One important thing to know about triggers is that you can't call them. A trigger fires when the event it's written for occurs.

Every trigger belongs to one and only one database table and fires in response to an UPDATE, an INSERT, or a DELETE event. You can write your trigger to fire on any one of these events or any combination of them.

SQL Server 7.0 allows several triggers to exist for the same operation. This is in contrast to older versions, in which only one trigger could fire for an INSERT, an UPDATE, or a DELETE event. A distinct advantage of this new capability is that you can now write several simple triggers rather than only one complicated trigger.

Let's take a look at the first version of a suitable trigger and see how it works. Then we'll modify the code to make it really good.

CREATE TRIGGER trHorsesSexUpdate ON Horses FOR UPDATE AS IF UPDATE(Sex) BEGIN IF ((SELECT Sex FROM INSERTED) <> 'v') OR ((SELECT Sex FROM DELETED) <> 'h') RAISERROR ('The only valid update of the Sex ' + 'column is from "h" to "v"', 16, 1) END

The first two lines tell us and SQL Server that this trigger—trHorsesSexUpdate—is meant to fire whenever an update is made to the Horses table. The third line is the mandatory word AS, which specifies that the code that follows is the action the trigger is to take. The implementation of the trigger begins with the fourth line.

The first line of the implementation examines whether the Sex column has been updated. The BEGIN and END keywords enclose the block of code that should execute if the Sex column has been updated. That piece of code is fairly easy to follow, once you understand that Transact-SQL uses the SELECT keyword to assign data to a variable.

• When you look at the code, you can easily see that we first check whether the new value for the sex column is different from v. (In a trigger, you will always find the new value in the virtual INSERTED table.)⁵ Since our rule will accept sex changes from h to v only, the new value must always be set to v for the change to adhere to the rule.
• Then we investigate the old value to see whether it's anything other than h. (In a trigger, you'll always find the original value in the virtual DELETED table.) Since our rule accepts sex changes from h to v only, the old value must always be h for the change to be acceptable.

Whenever the new value is anything other than v or the old value is anything other than h, the modification is invalid and shouldn't be accepted. If this is the case, the RAISERROR statement sets up an error to return to the caller.

Let's see how this works in practice!

Horse 303 is Black Diamond. This horse is male. Consequently, his sex value is h. It should be possible to change that value to v, making him a gelding instead. To perform that operation, we send the following SQL batch to the database:

BEGIN TRANSACTION UPDATE Horses SET Sex = 'v' WHERE HorseId = 303

As always, we start with the BEGIN TRANSACTION statement to make it possible to roll back any unwanted changes to the database.

Here's the answer from SQL Server:

(1 row(s) affected)

Evidently, the operation was successful, and Black Diamond is now a gelding. A check of the database confirms this:

SELECT HorseId, HorseName, Sex FROM Horses WHERE HorseId = 303 HorseId HorseName Sex ----------- -------------------- ---- 303 Black Diamond v (1 row(s) affected)

Now that Black Diamond is a gelding, it shouldn't be possible to change his sex back to male. Let's try and see what happens:

UPDATE Horses SET Sex = 'h' WHERE HorseId = 303 Server: Msg 50000, Level 16, State 1 The only valid update of the Sex column is from "h" to "v" (1 row(s) affected)

As you can see, the trigger has executed and returns an error with the exact error message that you programmed. But watch out! There's a trap in here. Take a look at the last line. It says that one row is affected. A new check reveals that the sex of Black Diamond has indeed changed to h, something the trigger was supposed to protect it from.

SELECT HorseId, HorseName, Sex FROM Horses WHERE HorseId = 303 HorseId HorseName Sex ----------- -------------------- ---- 303 Black Diamond h (1 row(s) affected)

Rolling back

Why did this happen? The trigger checked for invalid sex modifications but merely reported them. A trigger works only on changes already made; it doesn't stop them from happening. When the trigger made its check, the update had already been done. The change needs to be rolled back.

You can do that in the trigger, or you can do it in the client program. Which is best? Well, the general idea of a trigger is to give administrators centralized control over data resources. One of the greatest advantages of using triggers is that the organization doesn't have to depend on client programs or client programmers to protect data. So the trigger itself should be responsible for the rollback.

In this case, implementing centralized rollback is easy enough. All you have to do is insert a ROLLBACK TRANSACTION statement before the RAISERROR statement. Of course, since this increases the number of lines below the second IF statement, you must also insert another BEGIN and END pair.⁶ In the code which follows, you can see what the trigger looks like with this modification, shown in boldface.

CREATE TRIGGER trHorsesSexUpdate ON Horses FOR UPDATE AS IF UPDATE(Sex) BEGIN IF ((SELECT Sex FROM INSERTED) <> 'v') OR ((SELECT Sex FROM DELETED) <> 'h')  BEGIN ROLLBACK TRANSACTION RAISERROR ('The only valid update of the Sex ' + 'column is from "h" to "v"', 16, 1)  END END

The next time you try this operation, the result will be different. As before, the first update succeeds because it updates the sex of Black Diamond from male to gelding, that is, from h to v:

UPDATE Horses SET sex = 'v' WHERE HorseId = 303 (1 row(s) affected)

It looks all right, but we'd better check:

SELECT HorseId, Horsename, Sex FROM Horses WHERE HorseId = 303 HorseId HorseName Sex ----------- -------------------- ---- 303 Black Diamond v (1 row(s) affected)

Yes, Black Diamond is now a gelding. Let's try to reverse that with a new update statement, making him a male again. This time it shouldn't succeed, and it doesn't:

UPDATE Horses SET Sex = 'h' WHERE HorseId = 303 Server: Msg 50000, Level 16, State 1 The only valid update of the Sex column is from "h" to "v"

So far, the result looks the same as the last time we were unsuccessful in trying to change the sex of Black Diamond from gelding to male. The only difference—an important one—is that the (1 row(s) affected) is missing. In all other respects, the message is the same. As you might remember from the last attempt, the update was carried out even though the message said it wasn't valid. This time the missing (1 row(s) affected) message indicates that no update has taken place. Now let's see what happened in the database. Here goes:

SELECT HorseId, HorseName, Sex FROM Horses WHERE HorseId = 303 HorseId HorseName Sex ----------- -------------------- ---- 303 Black Diamond v (1 row(s) affected)

This is what we need. The trigger effectively protected Black Diamond from performing the impossible sex change from gelding to male. We're safe!

Only, Black Diamond really is a male, so we'd better make sure that the database reflects that. We can't update him, and we can't roll the former transaction back because we already committed it by not starting with a BEGIN TRANSACTION statement this time. Our only chance is to temporarily remove the trigger, do the update, and then put the trigger back in place again.

This is something to bear in mind. If you use triggers to implement rules like this one, there's no way to go back to the original value if someone made a valid but incorrect modification. You could find yourself in a situation in which you can't easily go back to the original and correct value.

Checking for an ongoing transaction

Before you issue a ROLLBACK statement, you should be sure that you have an ongoing transaction. In SQL Server, in contrast with several other similar DBMSs, there are in principle two kinds of transactions:

• User-defined transactions, by which the client process (or a stored procedure) starts the transaction by issuing a BEGIN TRANSACTION statement.
• System-defined transactions. If the client doesn't issue a BEGIN TRANSACTION statement, SQL Server will. SQL Server never does an update, an insertion, or a deletion outside a transaction. If there's no user-defined transaction, the system itself defines one.

In other cases, as when a trigger calls a stored procedure, the previously ongoing transaction might already have been rolled back. If this happens, SQL Server issues a different error message from the one that you might want to return to the client.

The safe way, then, is always to find out whether a transaction is taking place before you do a rollback. You can do that by investigating the @@TRANCOUNT variable. This is a global variable—all global variables in SQL Server have names that are preceded by two @ signs. The @@TRANCOUNT variable reports the number of transactions that are going on right now for the current connection.

Here's the extended trigger code:

CREATE TRIGGER trHorsesSexUpdate ON Horses FOR UPDATE AS IF UPDATE(Sex) BEGIN IF ((SELECT Sex FROM INSERTED) <> 'v') OR ((SELECT Sex FROM DELETED) <> 'h')  IF @@TRANCOUNT > 0   BEGIN ROLLBACK TRANSACTION RAISERROR ('The only valid update of the Sex column is ' + 'from "h" to "v"', 16, 1) END END

Making it set oriented or record oriented?

We have one final issue to handle. For the regular programmer, this is a tough one. SQL, in principle, is a set-oriented language. Every SQL statement works on a set, not on a record, whereas a programmer using a general-purpose language normally works on a single record. The trigger we have constructed so far is an example of this difference. It works all right if the UPDATE statement that fires the trigger affects one row only. If it affects more than one row, anything can happen. You should adapt the trigger to the set orientation of SQL. You can choose from two options:

• Make the trigger set oriented, allowing it to work on an updated set—that is, on the entire set rather than on one individual member of the set. The trick is to make the second IF statement check whether any row in the updated set violates the rule.
• Make sure that the UPDATE that fires the trigger is record oriented and affects only a single row. If not, roll back the transaction by virtue of its being set oriented rather than record oriented.

The first solution is more flexible and allows set-oriented as well as record-oriented updates. A set-oriented trigger, however, also demands more resources. Under certain conditions, it might cause bad, sometimes even really bad, performance. Be careful when you design set-oriented triggers. Sometimes their flexibility comes at too high a price in lost performance.

Anyway, let's make our trigger set oriented, if for no other reason than to see how to do it.

CREATE TRIGGER trHorsesSexUpdate ON Horses FOR UPDATE AS IF UPDATE(Sex) BEGIN  IF (EXISTS (SELECT * FROM INSERTED WHERE Sex <> 'v')) OR (EXISTS (SELECT * FROM DELETED WHERE Sex <> 'h')) IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION RAISERROR ('The only valid update of the Sex column is ' + 'from "h" to "v"', 16, 1) END END

Now, if you try to update every horse that was born in 1988 to be a gelding, the trigger will stop you unless every horse born in 1988 was a male:

BEGIN TRANSACTION UPDATE Horses SET Sex = 'v' WHERE Birthyear = 1988 Server: Msg 50000, Level 16, State 1 The only valid update of the Sex column is from "h" to "v"

You should also check whether your connection has an active transaction:

SELECT @@TRANCOUNT ----------- 0 (1 row(s) affected)

If, on the other hand, you try to change the status of all male horses to gelding, the operation should succeed. You'd better start off with a BEGIN TRANSACTION statement if you don't want to ruin your database.

BEGIN TRANSACTION UPDATE Horses SET Sex = 'v' WHERE Sex = 'h' (346 row(s) affected)

Is there a transaction?

SELECT @@TRANCOUNT ----------- 1 (1 row(s) affected)

Yes, there is. You'd better roll it back and make the entire set of male horses revert to males rather than geldings.

ROLLBACK TRANSACTION

The trigger solution is complete

Now you have a trigger that makes sure that rule 4 is followed at all times.

• The trigger is set oriented.
• It checks that the rule is followed for every updated row.
• It's selective—that is, it fires at every update but checks for the rule only if the sex column is updated.
• It rolls back changes if the rule is violated but only if a transaction is still under way.

As promised, we'll also at least hint at a solution that makes the trigger record oriented. You can do that as follows:

• Check how many rows were updated. You can do that by looking at the global @@ROWCOUNT variable. The syntax is IF @@ROWCOUNT > 1…
• If @@ROWCOUNT returns a value greater than 1, set up an error message telling the user that more than one row was updated, make a rollback, and then exit the trigger.

The Visual Basic Solution

Now let's take a look at a Visual Basic solution. We'll start by reminding you that in our design patterns, records move between tiers in the form of Extensible Markup Language (XML) or in disconnected ADO recordsets. Such recordsets can contain one or several records.

So, just as with the trigger, you have a choice between a set-oriented and a record-oriented solution. A record-oriented solution would take a recordset and work on the current record. A set-oriented solution would take a recordset and work on each record within it. Which one should you choose?

A set-oriented solution

In our view, there's no question about it. You should go for a set-oriented solution. Here goes:

Private Function InvalidSexChange(rs As Recordset) As Boolean ' Takes a recordset. Validates any sex changes ' in each of its records. ' The only sex change allowed is from 'h' to 'v'. ' All other sex changes violate the business rule. ' ' Works on the local cloned recordset to avoid changing ' which record is the current record. ' Returns: ' ======= ' True if any violation ' False if no violation Dim blnFoundErrors As Boolean 'False is default Dim rsClone As Recordset   InvalidSexChange = blnFoundErrors End Function

This, of course, is only part of the solution. We want to take you through it step by step.

First the name of the function is InvalidSexChange, and it returns True or False depending on whether the recordset contains an invalid sex change. You can see from the remarks that the function returns True if the proposed change is invalid and False if it isn't.

You can also see from the remarks, as well as from the Dim statements the following:

• The function uses a local Boolean variable in which you will save any violations. The default value, as always with Boolean variables in Visual Basic, is False. You'll soon see that any violation will set it to True. If there's no violation, the default value remains all the way to the end of the function.
• The function uses a cloned ADO recordset in order not to disturb the current record setting. To validate all records, the function must go through one record at a time, constantly changing the current record setting. The client process expects to have the same record current as before the call. To preserve that, we'll clone the recordset into a new variable—see Dim rsClone.

Using XML rather than an ADO recordset for data transportation wouldn't necessarily change this code. Chances are that you would reconvert your data from XML to another ADO recordset before validating it—that is, if you wouldn't prefer to use the Document Object Model (DOM) to navigate your data set.

Cloning and moving

Let's add some more code to clone the recordset and then move through it. As usual, the bold part of the code is new.

Private Function InvalidSexChange(rs As Recordset) As Boolean ' Takes a recordset. Validates any sex changes ' in each of its records. ' The only sex change allowed is from 'h' to 'v'. ' All other sex changes violate the business rule. ' ' Works on the local cloned recordset to avoid changing ' which record is the current record. ' Returns: ' ======= ' True if any violation ' False if no violation Dim blnFoundErrors As Boolean 'False is default Dim rsClone As Recordset  Set rsClone = rs.Clone (adLockReadOnly) rsClone.MoveFirst Do While Not rsClone.EOF  rsClone.MoveNext Loop InvalidSexChange = blnFoundErrors End Function

First the recordset is cloned, and then the clone is saved in the new rsClone variable. A cloned recordset is a duplicate of the original, and changes to the clone don't affect the original. The two are separate entities.

To save time and resources, we have added the optional adLockReadOnly parameter, which of course makes the clone read-only. Then we move to the first record of the clone⁷ to start a loop that will go on until we have reached the end of the recordset. For each loop, we move to the next record, making it the current record.

Filter away all unchanged records

If you don't modify a record at all, you can't break any modification rules. Going through unmodified records is not very useful, so you should set a filter that shows only modified records.

Every record in an ADO recordset has an EditMode property. It has a set of four possible values, represented by the following symbolic constants:

• adEditNone, which states that no editing operation is in progress on the current record
• adEditInProgress, which states that the current record has been modified but not yet saved
• adEditAdd, which states that the current record is new and has not yet been saved
• adEditDelete, which indicates that the record has been deleted but not yet permanently removed from the database

In the case of our current example, we're interested only in those records that have been changed and not yet saved to the database. This includes existing records that have been modified but not saved. It also includes existing records that have been marked for deletion in the recordset but that haven't yet been deleted from the database. Finally, it includes new records that exist in the recordset but not yet in the database.

Let's set up such a filter on the cloned recordset. In the following code, we do that by setting a filter to adFilterPendingRecords, effectively hiding all the records with their EditMode property equalling adEditNone. We set such a filter on the first line that's set in bold type. In Chapter 23, "Some Final Issues," you'll see an example of how you could use the EditMode property to distinguish between a record meant to insert a new row in the database and one meant to update an existing row.

The next step is to make sure that you check the rule only for records in which the sex value has really been changed. For those records, and only for those, you'll have to make sure that the original sex value was h for hingst (male) and the new value was v for vallack (gelding). Checking this is enough because any change that's not from male to gelding is invalid. Here's the code:

 Private Function InvalidSexChange(rs As Recordset) As Boolean ' Takes a recordset. Validates any sex changes ' in each of its records. ' The only sex change allowed is from 'h' to 'v'. ' All other sex changes violate the business rule. ' ' Works on the local cloned recordset to avoid changing ' which record is the current record. ' Returns: ' ======= ' True if any violation ' False if no violation Dim blnFoundErrors As Boolean 'False is default Dim rsClone As Recordset Set rsClone = rs.Clone(adLockReadOnly) rsClone.MoveFirst  rsClone.Filter = adFilterPendingRecords Do While Not rsClone.EOF  If rsClone("Sex").Value <> _ rsClone("Sex").OriginalValue Then If rsClone("Sex").Value <> "v" Or _ rsClone("Sex").OriginalValue <> "h" Then 'Rule violation - return true: blnFoundErrors = True Exit Do End If End If rsClone.MoveNext Loop InvalidSexChange = blnFoundErrors End Function 

For each record, the code investigates whether the sex field has changed. Its method is to compare the Value and OriginalValue properties. OriginalValue is the value that was originally fetched from the database; Value is the actual value after any modifications.

If there has been a change—if the two values differ—the code goes on to check whether the change was from any value other than h to any value other than v. If so, there is a violation, and the Boolean blnFoundErrors variable is set to True. Furthermore, the code uses Exit Do to exit from the loop as soon as it finds an error.

Rule 4 Is Set

For rule 4, you have alternative implementations:

• You can implement the rule in a SQL Server trigger.
• You can implement the rule in a function, written in the COM language of your choice, be it Visual Basic or any other suitable language. Our choice is definitely Visual Basic.

Advantages of using a SQL Server trigger

There are some advantages to using a SQL Server trigger. Here's at least a partial list:

• If you design a trigger correctly, it's foolproof. There's no way a user can avoid firing a trigger when it should fire.
• It doesn't matter how the client programmer writes his or her code. When that code orders a certain operation, any rule that's correctly implemented as a trigger on that operation will follow the rule or abort.
• In many cases, a trigger offers better performance than any other rule implementation. (Incidentally, our rule 4 isn't such a case. If you implement this rule as a trigger, you must do the actual update before the rule can be checked. If you implement it in Visual Basic code, all you have to do is to take a look at the recordset. You don't even have to go to the database to check which values should be considered valid.)

Disadvantages of using a SQL Server trigger

Among the problems with using the trigger are these:

• Good SQL procedure programmers don't abound. Even if you find one for creating the procedures, you might have to find another to maintain them.
• As a language, SQL was never meant for the programming of procedures. It shouldn't be surprising, therefore, that compared with a language such as Visual Basic, SQL lacks power, flexibility, and support for structured programming.
• Record-oriented triggers might fail in producing the correct result if set-oriented operations are issued.
• Triggers can issue other updates, insertions, and deletions, which in turn might fire other triggers, which again in turn…In other words, triggers run the risk of being hazardous to your health—we mean performance!
• Triggers aren't standardized. If you choose triggers for rule implementation, you bind your application to a certain database server. This might not be a bad idea at all, but you should be aware of it. Chances are you must rewrite your triggers if you want to move the database to another server, say, from SQL Server to Oracle. You might have to change some of your client code too.

Advantages of using a Visual Basic function and ADO

Since everything isn't supercool with SQL Server triggers, you might be interested in advantages that arise from using Visual Basic instead. Here are some:

• Visual Basic is a beautiful language for this kind of job. It's easier to use than most, it works fine with ADO recordsets, and it compiles to really fast code. You might find that it's not purely object oriented. Still, it's a very good object-based language. Today, by a large margin, more programmers use Visual Basic than any other programming language.
• Visual Basic programmers abound. If you lose one, you'll find many to fill his or her place. They might not be equally good, but you should never have a problem finding many that are good enough—or better! You'd probably find it much more difficult to find a first-class SQL programmer.
• Visual Basic and ADO are very much de facto standards, at least as long as you stay in a Microsoft environment. ADO isolates your application from many of the intricacies of the database it uses. Most of the time, you'll be able to move your application from one database server to another without having to rewrite more than a very small part of your code—or any code at all.
• The risk of hurting performance that you run using set-oriented triggers, which cause other set-oriented triggers to fire, is much smaller using Visual Basic and ADO.

Disadvantages of using a Visual Basic function and ADO

The disadvantages of using Visual Basic would mirror the advantages of using SQL Server triggers, at least if we were talking about two-tier applications. But we are not! We're talking about three-tier applications. The only disadvantage of using Visual Basic is the following:

• You must make sure that all additions, updates, and deletions go through a set of business objects that implement rule 4. If you do that successfully, you'll have the same level of database integrity that a trigger solution gives you. But you'll have to make sure of it—it's not automatic.

Advice: Business object implementation in Visual Basic

Our advice is to implement rule 4 in a business object, using Visual Basic to program it.