CHECK Constraints Based on Simple Expressions

By far, most CHECK constraints are simple expressions that just test some characteristic of a value in a column or columns. These constraints often don’t reference any data other than the single column but can reference any of the columns in a single row.

As a few examples, consider the following:

• Empty strings: Prevent users from inserting one or more space characters to avoid any real input into a column—CHECK (LEN(ColumnName) > 0). This constraint is on 90 percent of the varchar and char columns in databases I design.
• Date range checks: Make sure a reasonable date is entered, for example:
  • The date a rental is required to be returned should be greater than one day after the RentalDate (assume the two columns are implemented with the date datatype): CHECK (ReturnDate > dateadd(day,1,RentalDate)).
  • Date of some event that’s supposed to have occurred already in the past: CHECK (EventDate <= GETDATE()).
• Value reasonableness: Make sure some value, typically a number of some sort, is reasonable for the situation. Reasonable, of course, does not imply that the value is necessarily correct for the given situation, which is usually the domain of the middle tier of objects—just that it is within a reasonable domain of values. For example:
  • Values must be a nonnegative integer. This is common, because there are often columns where negative values don’t make sense (hours worked, miles driven, and so on): CHECK (MilesDriven >= 0).
  • Royalty rate for an author that’s less than or equal to 30 percent. If this rate ever could be greater, it isn’t a CHECK constraint. So if 15 percent is the typical rate, the UI might warn that it isn’t normal, but if 30 percent is the absolute ceiling, it would be a good CHECK constraint: CHECK (RoyaltyRate <= .3).

CHECK constraints of this variety are always a good idea when you have situations where there are data conditions that must always be true. Another way to put this is that the very definition of the data is being constrained, not just a convention that could change fairly often or even be situationally different. These CHECK constraints are generally extremely fast and won’t negatively affect performance except in extreme situations. As an example, I’ll just show the code for the first, empty string check, because simple CHECK constraints are easy to code once you have the syntax. A common CHECK constraint that I add to string type columns (varchar, char) prevents blank data from being entered. This is because, most of the time, if a value is required, it isn’t desired that the value for a column be blank, unless having no value for the column makes sense(as opposed to having a NULL value, meaning that the value is not currently known).

For example, in the Album table, the Name column doesn’t allow NULLs. The user has to enter something, but what about when the enterprising user realizes that '' is not the same as NULL? What will be the response to an empty string? Ideally, of course, the UI wouldn’t allow such nonsense for a column that had been specified as being required, but the user just hits the space bar, but to make sure, we will want to code a constraint to avoid it.

To avoid letting a user get away with a blank row, you can add the following constraint to prevent this from ever happening again (after deleting the two blank rows). It works by using the LEN function that does a trim by default, eliminating any space characters, and checking the length:

 ALTER TABLE Music.Album WITH CHECK

  ADD CONSTRAINT chkMusicAlbum$Name$noEmptyString

  CHECK (LEN(Name) > 0); --note,len does a trim by default, so any string

 --of all space characters will return 0

Testing this with data that will clash with the new constraint

 INSERT INTO Music.Album ( AlbumId, Name, ArtistId, PublisherId, CatalogNumber )

 VALUES ( 4, '', 1, 1,'dummy value' );

you get the following error message

All too often, nonsensical data is entered just to get around your warning, but that is more of a UI or managerial oversight problem than a database design concern, because the check to see whether 'ASDFASDF' is a reasonable name value is definitely not of the definite true/false variety. (Have you seen what some people name their kids?) What’s generally the case is that the user interface will then prevent such data from being created via the UI, but the CHECK constraint is there to prevent other processes from putting in completely invalid data as well.

These check constraints are very useful when you are loading data into the table from an outside source. Often, when data is imported from a file, like from the Import Wizard, blank data will be propagated as blank values, and the programmers involved might not think to deal with this condition. The check constraints make sure that the data is put in correctly. And as long as you are certain to go back and recheck the trusted status and values, their existence helps to remind you even if they are ignored, like using SSIS’s bulk loading features. In Figure 7-2, you will see that you can choose to (or choose not to) check constraints on the OLEDB destination output. In this case, it may either disable the constraint, or set to not trusted to speed loading, but it will limit the data integrity and optimizer utilization of the constraint until you reset it to trusted as was demonstrated in the previous section.

CHECK Constraints Using Functions

Sometimes, you really need to implement a complex data check where a simple Boolean expression using the columns in the table and base T-SQL scalar functions just won’t do. In standard SQL, you can use a subquery in your constraints. However, in SQL Server, subqueries are not allowed, but you can use a scalar T-SQL function, even if it accesses another table.

In general, using functions is a fairly atypical solution to ensuring data integrity, but it can be far more powerful and, in many ways, quite useful when you need to build slightly complex data integrity protection. For the most part, CHECK constraints usually consist of the simple task of checking a stable format or value of a single column, and for these tasks, a standard CHECK constraint using the simple <BooleanExpression> is perfectly adequate.

However, a CHECK constraint need not be so simple. A UDF can be complex and might touch several tables in the instance. Here are some examples:

• Complex scalar validations (often using CLR functions): For example, in a situation where a regular expression would be easier to use than a LIKE comparison.
• Validations that access other tables : For example, to check a domain that is based on values in several tables, rather than a simple foreign key. In the example, I will implement an entry mask that is table based, so it changes based on a related table’s value.

I should warn you that calling a UDF has a great deal of overhead, and while you might get the urge to encapsulate a simple scalar for use in a CHECK constraint, it almost always isn’t worth the overhead. As we have mentioned, CHECK constraints are executed once per row affected by the DML modification statement, and this extra cost will be compounded for every row affected by the modification query. I realize that this can be counterintuitive to a good programmer thinking that encapsulation is one of the most important goals of programming, but SQL is quite different from other types of programming in many ways because of the fact that you are pushing so much of the work to the engine, and the engine has its own way of doing things that you must respect.

Hence, it’s best to try to express your Boolean expression without a UDF unless it’s entirely necessary to access additional tables or do something more complex than a simple expression can. In the following examples, I’ll employ UDFs to provide powerful rule checking, which can implement complex rules that would prove difficult to code using a simple Boolean expression.

You can implement the UDFs in either T-SQL or a .NET language (VB .NET, C#, or any .NET language that lets you exploit the capabilities of SQL Server 2005+ to write CLR-based objects in the database). In many cases, especially if you aren’t doing any kind of table access in the code of the function, the CLR will perform much better than the T-SQL version.

As an example, I need to access values in a different table, so I’m going to build an example that implements an entry mask that varies based on the parent of a row. Consider that it’s desirable to validate that catalog numbers for albums are of the proper format. However, different publishers have different catalog number masks for their clients’ albums. (A more natural, yet distinctly more complex example would be phone numbers and addresses from around the world.)

For this example, I will continue to use the tables from the previous section. Note that the mask column, Publisher.CatalogNumberMask , needs to be considerably larger (five times larger in my example code) than the actual CatalogNumber column, because some of the possible masks use multiple characters to indicate a single character. You should also note that it’s a varchar, even though the column is stored as a char value, because using char variables as LIKE masks can be problematic because of the space padding at the end of such columns (the comparison thinks that the extra space characters that are padded on the end of the fixed-length string need to match in the target string, which is rarely what’s desired).

To do this, I build a T-SQL function that accesses this column to check that the value matches the mask, as shown (note that we’d likely build this constraint using T-SQL rather than by using the CLR, because it accesses a table in the body of the function):

 CREATE FUNCTION Music.Publisher$CatalogNumberValidate

 (

  @CatalogNumber char(12),

  @PublisherId int --not based on the Artist ID

 )

 RETURNS bit

 AS

 BEGIN

  DECLARE @LogicalValue bit, @CatalogNumberMask varchar(100);

  SELECT @LogicalValue = CASE WHEN @CatalogNumber LIKE CatalogNumberMask

 THEN 1

 ELSE 0 END

  FROM Music.Publisher

  WHERE PublisherId = @PublisherId;

  RETURN @LogicalValue;

 END;

When I loaded the data in the start of this section, I preloaded the data with valid values for the CatalogNumber and CatalogNumberMask columns:

 SELECT Album.CatalogNumber, Publisher.CatalogNumberMask

 FROMMusic.Album as Album

  JOIN Music.Publisher as Publisher

 ON Album.PublisherId = Publisher.PublisherId;

This returns the following results:

Now, let’s add the constraint to the table, as shown here:

 ALTER TABLE Music.Album

  WITH CHECK ADD CONSTRAINT

   chkMusicAlbum$CatalogNumber$CatalogNumberValidate

   CHECK (Music.Publisher$CatalogNumbervalidate

                         (CatalogNumber,PublisherId) = 1);

If the constraint gives you errors because of invalid data existing in the table (because you were adding data, trying out the table, or in real development, this often occurs with test data from trying out the UI that they are building), you can use a query like the following to find them:

 SELECT Album.Name, Album.CatalogNumber, Publisher.CatalogNumberMask

 FROMMusic.Album AS Album

   JOIN Music.Publisher AS Publisher

    on Publisher.PublisherId = Album.PublisherId

 WHERE Music.Publisher$CatalogNumbervalidate(Album.CatalogNumber,Album.PublisherId) = 1;

Now, let’s attempt to add a new row with an invalid value:

 INSERT Music.Album(AlbumId, Name, ArtistId, PublisherId, CatalogNumber)

 VALUES (4,'Who''s Next',2,2,'1'),

This causes the error, because the catalog number of '1' doesn’t match the mask set up for PublisherId number 2:

Now, changing change the catalog number to something that matches the entry mask the constraint is checking:

 INSERT Music.Album(AlbumId, Name, ArtistId, CatalogNumber, PublisherId)

 VALUES (4,'Who''s Next',2,'AC12345',2);

 SELECT * FROM Music.Album;

This returns the following results, which you can see matches the ‘[a-z][a-z][0-9][0-9][0-9][0-9][0-9]’ mask set up for the publisher with PublisherId = 2:

Using this kind of approach, you can build any single-row validation code for your tables. As described previously, each UDF will fire once for each row and each column that was modified in the update. If you are making large numbers of inserts, performance might suffer, but having data that you can trust is worth it.

We will talk about triggers later in this chapter, but alternatively, you could create a trigger that checks for the existence of any rows returned by a query, based on the query used earlier to find improper data in the table:

 SELECT *

 FROM Music.Album AS Album

  JOIN Music.Publisher AS Publisher

  on Publisher.PublisherId = Album.PublisherId

 WHERE Music.Publisher$CatalogNumberValidate

  (Album.CatalogNumber, Album.PublisherId) <> 1;

There’s one drawback to this type of constraint, whether implemented in a constraint or trigger. As it stands right now, the Album table is protected from invalid values being entered into the CatalogNumber column, but it doesn’t say anything about what happens if a user changes the CatalogEntryMask on the Publisher table. If this is a concern, you’d need to add a CHECK constraint to the Publisher table that validates changes to the mask against any existing data.

Enhancing Errors Caused by Constraints

The real downside to check constraints is the error messages they produce upon failure. The error messages are certainly things you don’t want to show to a user, if for no other reason other than they will generate help desk calls every time typical users see them. Dealing with these errors is one of the more annoying parts of using constraints in SQL Server.

Whenever a statement fails a constraint requirement, SQL Server provides you with an ugly message and offers no real method for displaying a clean message automatically. Luckily, SQL Server 2005 implemented vastly improved error-handling capabilities in T-SQL over previous versions. In this section, I’ll briefly detail a way to refine the ugly messages you get from a constraint error message, much like the error from the previous statement:

I’ll show you how to map this to an error message that at least makes some sense. First, the parts of the error message are as follows:

• Error number —Msg 547: The error number that’s passed back to the calling program. In some cases, this error number is significant; however, in most cases it’s enough to say that the error number is nonzero.
• Level —Level 16: A severity level for the message. 0 through 18 are generally considered to be user messages, with 16 being the default. Levels 19–25 are severe errors that cause the connection to be severed (with a message written to the log) and typically involve data corruption issues.
• State —State 0: A value from 0–127 that represents the state of the process when the error was raised. This value is rarely used by any process.
• Line —Line 1: The line in the batch or object where the error is occurring. This value can be extremely useful for debugging purposes.
• Error description : A text explanation of the error that has occurred.

In its raw form, this is the exact error that will be sent to the client. Using the new TRY-CATCH error handling, we can build a simple error handler and a scheme for mapping constraints to error messages (or you can do much the same thing in client code as well for errors that you just cannot prevent from your user interface). Part of the reason we name constraints is to determine what the intent was in creating the constraint in the first place. In the following code, we’ll implement a very rudimentary error-mapping scheme by parsing the text of the name of the constraint from the message, and then we’ll look this value up in a mapping table. It isn’t a “perfect” scheme, but it does the trick when using constraints as the only data protection for a situation (it is also helps you to document the errors that your system may raise as well).

First, let’s create a mapping table where we put the name of the constraint that we’ve defined and a message that explains what the constraint means:

 CREATE SCHEMA utility; --used to hold objects for utility purposes

 GO

 CREATE TABLE utility.ErrorMap

 (

  ConstraintName sysname NOT NULL primary key,

  Message varchar(2000) NOT NULL

 );

 GO

 INSERT utility.ErrorMap(constraintName, message)

 VALUES ('chkMusicAlbum$CatalogNumber$CatalogNumberValidate',

  'The catalog number does not match the format set up by the Publisher'),

Then, we create a procedure to do the actual mapping by taking the values that can be retrieved from the ERROR_%() procedures that are accessible in a CATCH block and using them to look up the value in the ErrorMap table:

 CREATE PROCEDURE utility.ErrorMap$MapError

 (

  @ErrorNumber int = NULL,

  @ErrorMessage nvarchar(2000) = NULL,

  @ErrorSeverity INT= NULL

 ) AS

  BEGIN

  SET NOCOUNT ON

  --use values in ERROR_ functions unless the user passes in values

  SET @ErrorNumber = Coalesce(@ErrorNumber, ERROR_NUMBER());

  SET @ErrorMessage = Coalesce(@ErrorMessage, ERROR_MESSAGE());

  SET @ErrorSeverity = Coalesce(@ErrorSeverity, ERROR_SEVERITY());

  --strip the constraint name out of the error message

  DECLARE @constraintName sysname;

  SET @constraintName = substring( @ErrorMessage,

  CHARINDEX('constraint "',@ErrorMessage) + 12,

  CHARINDEX('"',substring(@ErrorMessage,

  CHARINDEX('constraint "',@ErrorMessage) +

 12,2000))-1)

  --store off original message in case no custom message found

  DECLARE @originalMessage nvarchar(2000);

  SET @originalMessage = ERROR_MESSAGE();

  IF @ErrorNumber = 547 --constraint error

  BEGIN

  SET @ErrorMessage =

  (SELECT message

  FROM utility.ErrorMap

  WHERE constraintName = @constraintName

  );

  END

  --if the error was not found, get the original message with generic 50000 error number

  SET @ErrorMessage = isNull(@ErrorMessage, @originalMessage);

  THROW 50000, @ErrorMessage, @ErrorSeverity;

  END

 GO

Now, see what happens when we enter an invalid value for an album catalog number:

 BEGIN TRY

  INSERT Music.Album(AlbumId, Name, ArtistId, CatalogNumber, PublisherId)

  VALUES (5,'who are you',2,'badnumber',2);

 END TRY

 BEGIN CATCH

  EXEC utility.ErrorMap$MapError;

 END CATCH

The error message is as follows:

rather than:

This is far more pleasing, even if it was a bit of a workout getting to this new message.

AFTER Triggers

AFTER triggers fire after the DML statement has completed. They are the most common trigger that people use, because they have the widest array of uses. Though triggers may not seem very useful, back in SQL Server 6.0 and earlier, there were no CHECK constraints, and even FOREIGN KEYS where just being introduced, so all data protection was managed using triggers. Other than being quite cumbersome to maintain, some fairly complex systems were created using hardware that is comparable to one of my Logitech Harmony remote controls.

In this section on AFTER triggers, I will present examples that demonstrate several forms of triggers that I use to solve problems that are reasonably common. I’ll give examples of the following usages of triggers:

• Range checks on multiple rows
• Maintaining summary values (only as necessary)
• Cascading inserts
• Child-to-parent cascades
• Maintaining an audit trail
• Relationships that span databases and servers

From these examples, you should be able to extrapolate almost any use of AFTER triggers. Just keep in mind that, although triggers are not the worst thing for performance, they should be used no more than necessary.

Maintaining Summary Values

Maintaining summary values is generally not necessary, and doing so typically is just a matter of poor normalization or perhaps a misguided attempt to optimize where a better database design would have sufficed. However, there are cases where some form of active summarization may be necessary:

• There is no other reasonable method available.
• The amount of data to be summarized is large.
• The amount of reads of the summary values is far greater than the activity on the lower values.

As an example, let’s extend the previous example of the Account and AccountActivity tables from the “Range Checks on Multiple Rows” section. To the Account table, I will add a BalanceAmount column:

 ALTER TABLE Accounting.Account

  ADD BalanceAmount numeric(12,2) NOT NULL

  CONSTRAINT DfltAccounting_Account_BalanceAmount DEFAULT (0.00);

Then, we will update the Balance column to have the current value of the data in the -AccountActivity rows. First, running this query to view the expected values:

 SELECT Account.AccountNumber,

  SUM(coalesce(AccountActivity.TransactionAmount,0.00)) AS NewBalance

 FROM Accounting.Account

  LEFT OUTER JOIN Accounting.AccountActivity

  ON Account.AccountNumber = AccountActivity.AccountNumber

 GROUP BY Account.AccountNumber;

This returns the following:

Now, update the BalanceAmount column values to the existing rows using the following statement:

 WITH Updater as (

 SELECT Account.AccountNumber,

  SUM(coalesce(TransactionAmount,0.00)) as NewBalance

 FROM Accounting.Account

  LEFT OUTER JOIN Accounting.AccountActivity

  On Account.AccountNumber = AccountActivity.AccountNumber

 GROUP BY Account.AccountNumber)

 UPDATE Account

 SET BalanceAmount = Updater.NewBalance

 FROM Accounting.Account

  JOIN Updater

  ON Account.AccountNumber = Updater.AccountNumber;

That statement will make the basis of our changes to the trigger that we added in the previous section (the changes appear in bold). The only change that needs to be made is to filter the Account set down to the accounts that were affected by the DML that cause the trigger to fire. Using an EXISTS filter lets you not have to worry about whether one new row was created for the account or 100.

 ALTER TRIGGER Accounting.AccountActivity$insertUpdateTrigger

 ON Accounting.AccountActivity

 AFTER INSERT,UPDATE AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  -- @rowsAffected int = (SELECT COUNT(*) FROM deleted);

 BEGIN TRY

  --[validation section]

  --disallow Transactions that would put balance into negatives

  IF EXISTS ( SELECT AccountNumber

  FROM Accounting.AccountActivity as AccountActivity

  WHERE EXISTS (SELECT *

  FROM inserted

  WHERE inserted.AccountNumber =

  AccountActivity.AccountNumber)

  GROUP BY AccountNumber

  HAVING sum(TransactionAmount) < 0)

 BEGIN

  IF @rowsAffected = 1

  SELECT @msg = 'Account: ' + AccountNumber +

  ' TransactionNumber:' +

  cast(TransactionNumber as varchar(36)) +

  ' for amount: ' + cast(TransactionAmount as varchar(10))+

  ' cannot be processed as it will cause a negative balance'

  FROM inserted;

  ELSE

  SELECT @msg = 'One of the rows caused a negative balance';

  THROW 50000, @msg, 16;

 END

 --[modification section]

 IF UPDATE (TransactionAmount)

 BEGIN

 ;WITH Updater as (

 SELECT Account.AccountNumber,

 SUM(coalesce(TransactionAmount,0.00)) as NewBalance

 FROM Accounting.Account

 LEFT OUTER JOIN Accounting.AccountActivity

 On Account.AccountNumber = AccountActivity.AccountNumber

 --This where clause limits the summarizations to those rows

 --that were modified by the DML statement that caused

 --this trigger to fire.

 WHERE EXISTS (SELECT *

  FROM Inserted

  WHERE Account.AccountNumber = Inserted.AccountNumber)

 GROUP BY Account.AccountNumber)

 UPDATE Account

 SET BalanceAmount = Updater.NewBalance

 FROM Accounting.Account

  JOIN Updater

  on Account.AccountNumber = Updater.AccountNumber;

 END

 END TRY

  BEGIN CATCH

  IF @@trancount > 0

  ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

  END CATCH

 END;

Now, insert a new row into AccountActivity :

 INSERT INTO Accounting.AccountActivity(AccountNumber, TransactionNumber,

           Date, TransactionAmount)

 VALUES ('1111111111','A0000000000000000004','20050714',100);

Next, examine the state of the Account table, comparing it to the query used previously to check what the balances should be:

 SELECT Account.AccountNumber,Account.BalanceAmount,

       SUM(coalesce(AccountActivity.TransactionAmount,0.00)) AS SummedBalance

 FROM Accounting.Account

         LEFT OUTER JOIN Accounting.AccountActivity

            ON Account.AccountNumber = AccountActivity.AccountNumber

 GROUP BY Account.AccountNumber,Account.BalanceAmount;

which returns the following, showing that the sum is the same as the stored balance:

The next step—the multirow test—is very important when building a trigger such as this. You need to be sure that if a user inserts more than one row at a time, it will work. In our example, we will insert rows for both accounts in the same DML statement and two rows for one of the accounts. This is not a sufficient test necessarily, but it’s enough for demonstration purposes at least:

 INSERT into Accounting.AccountActivity(AccountNumber, TransactionNumber,

 Date, TransactionAmount)

 VALUES ('1111111111','A0000000000000000005','20050714',100),

 ('2222222222','A0000000000000000006','20050715',100),

 ('2222222222','A0000000000000000007','20050715',100);

Again, the query on the AccountActivity and Account should show the same balances:

If you wanted a DELETE trigger (and in the case of a ledger like this, you generally do not want to actually delete rows but rather insert offsetting values, so to delete a $100 insert, you would insert a –100), the only difference is that instead of the EXISTS condition referring to the inserted table, it needs to refer to the deleted table:

 CREATE TRIGGER Accounting.AccountActivity$deleteTrigger

 ON Accounting.AccountActivity

 AFTER DELETE AS

 BEGIN

  SET NOCOUNT ON;

 SET ROWCOUNT 0; --in case the client has modified the rowcount

 --use inserted for insert or update trigger, deleted for update or delete trigger

 --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

 --that is equal to number of merged rows, not rows being checked in trigger

 DECLARE @msg varchar(2000), --used to hold the error message

 --use inserted for insert or update trigger, deleted for update or delete trigger

 --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

 --that is equal to number of merged rows, not rows being checked in trigger

  -- @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  @rowsAffected int = (SELECT COUNT(*) FROM deleted);

 BEGIN TRY

 --[validation section]

 --[modification section]

  ;WITH Updater as (

  SELECT Account.AccountNumber,

  SUM(coalesce(TransactionAmount,0.00)) as NewBalance

  FROM Accounting.Account

  LEFT OUTER JOIN Accounting.AccountActivity

 On Account.AccountNumber = AccountActivity.AccountNumber

  WHERE EXISTS (SELECT *

  FROM deleted

  WHERE Account.AccountNumber =

  deleted.AccountNumber)

  GROUP BY Account.AccountNumber, Account.BalanceAmount)

  UPDATE Account

  SET BalanceAmount = Updater.NewBalance

  FROM Accounting.Account

  JOIN Updater

  ON Account.AccountNumber = Updater.AccountNumber;

  END TRY

  BEGIN CATCH

  IF @@trancount > 0

  ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

  END CATCH

 END;

 GO

So now, delete a couple of transactions:

 DELETE Accounting.AccountActivity

 WHERE TransactionNumber in ('A0000000000000000004',

  'A0000000000000000005'),

Checking the balance now, you will see that the balance for Account 1111111111 has been decremented to 200:

I can’t stress enough that this type of strategy should be the exception, not the rule. But when you have to implement summary data, using a trigger is the way to go in most cases. One of the more frustrating things to have to deal with is summary data that is out of whack, because it takes time away from making progress with creating new software.

Cascading Inserts

A cascading insert refers to the situation whereby after a row is inserted into a table, one or more other new rows are automatically inserted into other tables. This is frequently done when you need to initialize a row in another table, quite often a status of some sort.

For this example, we’re going to build a small system to store URLs for a website-linking system. During low-usage periods, an automated browser connects to the URLs so that they can be verified (hopefully, limiting broken links on web pages).

To implement this, I’ll use the set of tables in Figure 7-3.

 CREATE SCHEMA Internet;

 GO

 CREATE TABLE Internet.Url

 (

  UrlId int not null identity(1,1) constraint PKUrl primary key,

  Name varchar(60) not null constraint AKInternet_Url_Name UNIQUE,

  Url varchar(200) not null constraint AKInternet_Url_Url UNIQUE

 );

 --Not a user manageable table, so not using identity key (as discussed in

 --Chapter 5 when I discussed choosing keys) in this one table. Others are

 --using identity-based keys in this example.

 CREATE TABLE Internet.UrlStatusType

 (

  UrlStatusTypeId int not null

  CONSTRAINT PKInternet_UrlStatusType PRIMARY KEY,

  Name varchar(20) NOT NULL

  CONSTRAINT AKInternet_UrlStatusType UNIQUE,

  DefaultFlag bit NOT NULL,

  DisplayOnSiteFlag bit NOT NULL

 );

 CREATE TABLE Internet.UrlStatus

 (

  UrlStatusId int not null identity(1,1)

  CONSTRAINT PKInternet_UrlStatus PRIMARY KEY,

  UrlStatusTypeId int NOT NULL

  CONSTRAINT

  Internet_UrlStatusType$defines_status_type_of$Internet_UrlStatus

  REFERENCES Internet.UrlStatusType(UrlStatusTypeId),

  UrlId int NOT NULL

  CONSTRAINT Internet_Url$has_status_history_in$Internet_UrlStatus

  REFERENCES Internet.Url(UrlId),

  ActiveTime datetime2(3) NOT NULL,

  CONSTRAINT AKInternet_UrlStatus_statusUrlDate

  UNIQUE (UrlStatusTypeId, UrlId, ActiveTime)

 );

 --set up status types

 INSERT Internet.UrlStatusType (UrlStatusTypeId, Name,

  DefaultFlag, DisplayOnSiteFlag)

 VALUES (1, 'Unverified',1,0),

 (2, 'Verified',0,1),

 (3, 'Unable to locate',0,0);

The Url table holds URLs to different sites on the Web. When someone enters a URL, we initialize the status to 'Unverified'. A process should be in place in which the site is checked often to make sure nothing has changed (particularly the unverified ones!).

You begin by building a trigger that inserts a row into the UrlStatus table on an insert that creates a new row with the UrlId and the default UrlStatusType based on DefaultFlag having the value of 1.

 CREATE TRIGGER Internet.Url$afterInsertTrigger

 ON Internet.Url

 AFTER INSERT AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  -- @rowsAffected int = (SELECT COUNT(*) FROM deleted);

  BEGIN TRY

  --[validation section]

  --[modification section]

  --add a row to the UrlStatus table to tell it that the new row

  --should start out as the default status

  INSERT INTO Internet.UrlStatus (UrlId, UrlStatusTypeId, ActiveTime)

  SELECT inserted.UrlId, UrlStatusType.UrlStatusTypeId,

  SYSDATETIME()

  FROM inserted

  CROSS JOIN (SELECT UrlStatusTypeId

  FROM UrlStatusType

  WHERE DefaultFlag = 1) as UrlStatusType;

  --use cross join with a WHERE clause

  --as this is not technically a join

  --between inserted and UrlType

  END TRY

  BEGIN CATCH

  IF @@trancount > 0

  ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

 END CATCH

 END;

The idea here is that for every row in the inserted table, we’ll get the single row from the UrlStatusType table that has DefaultFlag equal to 1. So, let’s try it:

 INSERT Internet.Url(Name, Url)

 VALUES ('More info can be found here',

 'http://sqlblog.com/blogs/louis_davidson/default.aspx'),

 SELECT * FROM Internet.Url;

 SELECT * FROM Internet.UrlStatus;

This returns the following results:

Cascading from Child to Parent

All the cascade operations that you can do with constraints (CASCADE or SET NULL) are strictly from parent to child. Sometimes, you want to go the other way around and delete the parents of a row when you delete the child. Typically, you do this when the child is what you’re interested in and the parent is simply maintained as an attribute of the child. Also typical of this type of situation is that you want to delete the parent only if all children are deleted.

In our example, we have a small model of my game collection. I have several game systems and quite a few games. Often, I have the same game on multiple platforms, so I want to track this fact, especially if I want to trade a game that I have on multiple platforms for something else. So, we have a table for the GamePlatform (the system) and another for the actual game itself. This is a many-to-many relationship, so we have an associative entity called GameInstance to record ownership, as well as when the game was purchased for the given platform. Each of these tables has a delete-cascade relationship, so all instances are removed. What about the games, though? If all GameInstance rows are removed for a given game, we want to delete the game from the database. The tables are shown in Figure 7-4.

 --start a schema for entertainment-related tables

 CREATE SCHEMA Entertainment;

 go

 CREATE TABLE Entertainment.GamePlatform

 (

  GamePlatformId int NOT NULL CONSTRAINT PKEntertainmentGamePlatform PRIMARY KEY

  Name varchar(50) NOT NULL CONSTRAINT AKEntertainmentGamePlatform_Name UNIQUE

 );

 CREATE TABLE Entertainment.Game

 (

  GameId int NOT NULL CONSTRAINT PKEntertainmentGame PRIMARY KEY,

  Namevarchar(50) NOT NULL CONSTRAINT AKEntertainmentGame_Name UNIQUE

  --more details that are common to all platforms

 );

 --associative entity with cascade relationships back to Game and GamePlatform

 CREATE TABLE Entertainment.GameInstance

 (

  GamePlatformId int NOT NULL,

  GameId int NOT NULL,

  PurchaseDate date NOT NULL,

  CONSTRAINT PKEntertainmentGameInstance PRIMARY KEY (GamePlatformId, GameId),

  CONSTRAINT

  EntertainmentGame$is_owned_on_platform_by$EntertainmentGameInstance

 FOREIGN KEY (GameId) REFERENCES Entertainment.Game(GameId)

  ON DELETE CASCADE,

 CONSTRAINT

  EntertainmentGamePlatform$is_linked_to$EntertainmentGameInstance

  FOREIGN KEY (GamePlatformId)

  REFERENCES Entertainment.GamePlatform(GamePlatformId)

 ON DELETE CASCADE

 );

Then, I insert a sampling of data:

 INSERT into Entertainment.Game (GameId, Name)

 VALUES (1,'Lego Pirates of the Carribean').

 (2,'Legend Of Zelda: Ocarina of Time'),

 INSERT into Entertainment.GamePlatform(GamePlatformId, Name)

 VALUES (1,'Nintendo Wii'), --Yes, as a matter of fact I am still a

 (2,'Nintendo 3DS'), --Nintendo Fanboy, why do you ask?

 INSERT into Entertainment.GameInstance(GamePlatformId, GameId, PurchaseDate)

 VALUES (1,1,'20110804'),

 (1,2,'20110810'),

 (2,2,'20110604'),

 --the full outer joins ensure that all rows are returned from all sets, leaving

 --nulls where data is missing

 SELECT GamePlatform.Name as Platform, Game.Name as Game, GameInstance. PurchaseDate

 FROM Entertainment.Game as Game

  FULL OUTER JOIN Entertainment.GameInstance as GameInstance

  ON Game.GameId = GameInstance.GameId

  Full Outer Join Entertainment.GamePlatform

  ON GamePlatform.GamePlatformId = GameInstance.GamePlatformId;

As you can see, I have two games for Wii and only a single one for Nintendo 3DS:

So, we create a trigger on the table to do the “reverse” cascade operation:

 CREATE TRIGGER Entertainment.GameInstance$afterDeleteTrigger

 ON Entertainment.GameInstance

 AFTER DELETE AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  -- @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  @rowsAffected int = (SELECT COUNT(*) FROM deleted);

  BEGIN TRY

  --[validation section]

  --[modification section]

 --delete all Games

  DELETE Game --where the GameInstance was deleted

 WHERE GameId in (SELECT deleted.GameId

 FROM deleted --and there are no GameInstances

 WHERE not exists (SELECT * --left

 FROM GameInstance

 WHERE GameInstance.GameId =

 deleted.GameId));

  END TRY

  BEGIN CATCH

  IF @@trancount > 0

  ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

  END CATCH

 END

It’s as straightforward as that. Just delete the games, and let the trigger cover the rest. Delete the row for the Wii:

 DELETE Entertainment.GamePlatform

 WHERE GamePlatformId = 1;

Next, check the data

 SELECT GamePlatform.Name AS Platform, Game.Name AS Game, GameInstance. PurchaseDate

 FROM Entertainment.Game AS Game

  FULL OUTER JOIN Entertainment.GameInstance as GameInstance

  ON Game.GameId = GameInstance.GameId

  FULL OUTER JOIN Entertainment.GamePlatform

  ON GamePlatform.GamePlatformId = GameInstance.GamePlatformId;

You can see that now we have only a single row in the Game table:

Maintaining an Audit Trail

A common task that’s implemented using triggers is the audit trail or audit log. You use it to record previous versions of rows or columns so you can determine who changed a given row. Often, an audit trail is simply for documentation purposes, so we can go back to other users and ask why they made a change.

An audit trail is straightforward to implement using triggers. In our example, we’ll build an employee table and audit any change to the table. I’ll keep it simple and have a copy of the table that has a few extra columns for the date and time of the change, plus the user who made the change and what the change was. In Chapter 12, I will talk a bit more about audit trail type objects with an error log. This particular type of audit trail I am discussing in this chapter is typically there for an end user’s needs. In this case, the purpose of the auditTrail is not necessarily for a security purpose but more to allow the human resources managers to see changes in the employee’s status.

We implement an employee table (using names with underscores just to add variety) and then a replica to store changes into:

 CREATE SCHEMA hr;

 GO

 CREATE TABLE hr.employee

 (

  employee_id char(6) NOT NULL CONSTRAINT PKhr_employee PRIMARY KEY,

  first_name varchar(20) NOT NULL,

  last_name varchar(20) NOT NULL,

  salary decimal(12,2) NOT NULL

 );

 CREATE TABLE hr.employee_auditTrail

 (

  employee_id char(6) NOT NULL,

  date_changed datetime2(0) not null --default so we don't have to

  --code for it

  CONSTRAINT DfltHr_employee_date_changed DEFAULT (SYSDATETIME()),

  first_name varchar(20) NOT NULL,

  last_name varchar(20) NOT NULL,

  salary decimal(12,2) NOT NULL,

  --the following are the added columns to the original

  --structure of hr.employee

  action char(6) NOT NULL

  CONSTRAINT chkHr_employee_action --we don't log inserts, only changes

  CHECK(action in ('delete','update')),

  changed_by_user_name sysname NOT NULL

  CONSTRAINT DfltHr_employee_changed_by_user_name

  DEFAULT (original_login()),

  CONSTRAINT PKemployee_auditTrail PRIMARY KEY (employee_id, date_changed)

 );

Now, we create a trigger with code to determine whether it’s an UPDATE or a DELETE , based on how many rows are in the inserted table:

 CREATE TRIGGER hr.employee$updateAndDeleteAuditTrailTrigger

 ON hr.employee

 AFTER UPDATE, DELETE AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  -- @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  @rowsAffected int = (SELECT COUNT(*) FROM deleted);

  BEGIN TRY

  --[validation section]

  --[modification section]

 --since we are only doing update and delete, we just

 --need to see if there are any rows

 --inserted to determine what action is being done.

 DECLARE @action char(6);

 SET @action = CASE WHEN (SELECT COUNT(*) FROM INSERTED) > 0

 THEN 'update' ELSE 'delete' END;

 --since the deleted table contains all changes, we just insert all

 --of the rows in the deleted table and we are done.

  INSERT employee_auditTrail (employee_id, first_name, last_name,

  salary, action)

  SELECT employee_id, first_name, last_name, salary, @action

  FROM deleted;

  END TRY

  BEGIN CATCH

  IF @@trancount > 0

  ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

  END CATCH

 END;

We create some data:

 INSERT hr.employee (employee_id, first_name, last_name, salary)

 VALUES (1, 'Phillip','Taibul',10000);

Now, unlike the real world in which we live, the person gets a raise immediately:

 UPDATE hr.employee

 SET salary = salary * 1.10 --ten percent raise!

 WHERE employee_id = 1;

 SELECT *

 FROM hr.employee;

This returns the data with the new values:

Check the audit trail table:

 SELECT *

 FROM hr.employee_auditTrail;

You can see that the previous values for the row are stored here:

This can be a cheap and effective auditing system for many smaller systems. If you have a lot of columns, it can be better to check and see which columns have changed and implement a table that has tablename , columnname , and previous value columns, but often, this simple strategy works quite well when the volume is low and the number of tables to audit isn’t large. Keeping only recent history in the audit trail table helps as well.

Formatting User Input

Consider the columns firstName and lastName. What if the users who were entering this were heads-down, paid-by-the-keystroke kinds of users? Would we want them to go back and futz around with “joHnson” and make sure that it was formatted as “Johnson”? Or what about data received from services that still use mainframes, in which lowercase letters are still considered a work of the underlord? We don’t want to have to make anyone go in and reformat the data by hand (even the newbie intern who doesn’t know any better).

One good place for this kind of operation is an INSTEAD OF trigger, often using a function to handle the formatting. Here, I’ll present them both in their basic state, generally capitalizing the first letter of each word. This way, we can handle names that have two parts, such as Von Smith, or other more reasonable names that are found in reality. The crux of the function is that I’m simply capitalizing the first character of every letter after a space. The function needs to be updated to handle special cases, such as McDonald.

I am going to simply code this function in T-SQL. The syntax for functions hasn’t changed much since SQL Server 2000, though in 2005, Microsoft did get a bit more lenient on what you’re allowed to call from a function, such as CURRENT_TIMESTAMP—the standard version of GETDATE()—which was one of the most requested changes to functions in SQL Server 2000.

First we start with the following table and definition:

 CREATE SCHEMA school;

 Go

 CREATE TABLE school.student

 (

  studentId int identity not null

  CONSTRAINT PKschool_student PRIMARY KEY,

  studentIdNumber char(8) not null

  CONSTRAINT AKschool_student_studentIdNumber UNIQUE,

  firstName varchar(20) not null,

  lastName varchar(20) not null,

 --implementation columns, we will code for them in the trigger too

  rowCreateDate datetime2(3) not null

  CONSTRAINT dfltSchool_student_rowCreateDate

  DEFAULT (current_timestamp),

  rowCreateUser sysname not null

  CONSTRAINT dfltSchool_student_rowCreateUser DEFAULT (current_user)

 );

Then, we will create a simple function to format a string value. I will use T-SQL to keep it simple for you to implement and use, but if this were going to be heavily used by many different systems, it would probably behoove you to build a proper function that deals with all of the cases (including allowing exceptions, since some people have nonstandard names!). You might want to use CLR, and you would probably also want versions to use for address standardization, phone number format checking, and so on.

 CREATE FUNCTION Utility.TitleCase

 (

  @inputString varchar(2000)

 )

 RETURNS varchar(2000) AS

 BEGIN

  -- set the whole string to lower

  SET @inputString = LOWER(@inputstring);

  -- then use stuff to replace the first character

  SET @inputString =

  --STUFF in the uppercased character in to the next character,

  --replacing the lowercased letter

  STUFF(@inputString,1,1,UPPER(SUBSTRING(@inputString,1,1)));

  --@i is for the loop counter, initialized to 2

  DECLARE @i int = 2;

  --loop from the second character to the end of the string

  WHILE @i < LEN(@inputString)

  BEGIN

  --if the character is a space

  IF SUBSTRING(@inputString,@i,1) = ' '

  BEGIN

  --STUFF in the uppercased character into the next character

  SET @inputString = STUFF(@inputString,@i +

  1,1,UPPER(SUBSTRING(@inputString,@i + 1,1)));

  END

  --increment the loop counter

  SET @i = @i + 1;

  END

  RETURN @inputString;

 END;

Now, we can alter our trigger from the previous section, which was used to set the rowCreateDate rowCreate user for the school.student table. This time, you’ll modify the trigger to title-case the name of the student. The changes are in bold:

 CREATE TRIGGER school.student$insteadOfInsertTrigger

 ON school.student

 INSTEAD OF INSERT AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

 @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  -- @rowsAffected int = (SELECT COUNT(*) FROM deleted);

  BEGIN TRY

 --[validation section]

 --[modification section]

 --<perform action>

 INSERT INTO school.student(studentIdNumber, firstName, lastName,

 rowCreateDate, rowCreateUser)

 SELECT studentIdNumber,

  Utility.titleCase(firstName),

  Utility.titleCase(lastName),

  CURRENT_TIMESTAMP, ORIGINAL_LOGIN()

  FROM inserted; --no matter what the user put in the inserted row

  END TRY --when the row was created, these values will be inserted

  BEGIN CATCH

 IF @@trancount > 0

  ROLLBACK TRANSACTION;

 THROW; --will halt the batch or be caught by the caller's catch block

  END CATCH

 END;

Then, insert a new row with funky formatted data:

 INSERT school.student(studentIdNumber, firstName, lastName)

 VALUES ( '0000001','CaPtain', 'von nuLLY'),

And include two more rows in a single insert with equally funkily formatted data:

 INSERT school.student(studentIdNumber, firstName, lastName)

 VALUES ( '0000002','NORM', 'uLl'),

 ( '0000003','gREy', 'tezine'),

Next, we can check the data using a simple SELECT statement:

 SELECT *

 FROM school.student

Now, you see that this data has been formatted:

I’ll leave it to you to modify this trigger for the UPDATE version, because there are few differences, other than updating the row rather than inserting it.

It is not uncommon for this kind of formatting to be done at the client to allow for overriding as needed. Just as I have said many times, T-SQL code could (and possibly should) be used to manage formatting when it is always done. If there are options to override, then you cannot exclusively use a trigger for sure. In our example, you could just use the INSERT trigger to format the name columns initially and then not have an UPDATE trigger to allow for overrides. Of course this largely depends on the requirements of the system, but in this chapter my goal is to present you with options for how to protect your data integrity.

Redirecting Invalid Data to an Exception Table

On some occasions, instead of returning an error when an invalid value is set for a column, you simply want to ignore it and log that an error had occurred. Generally, this wouldn’t be used for bulk loading data (using SSIS’s facilities to do this is a much better idea), but some examples of why you might do this follow:

• Heads-down key entry: In many shops where customer feedback or payments are received by the hundreds or thousands, there are people who open the mail, read it, and key in what’s on the page. These people become incredibly skilled in rapid entry and generally make few mistakes. The mistakes they do make don’t raise an error on their screens; rather, they fall to other people—exception handlers—to fix. You could use an INSTEAD OF trigger to redirect the wrong data to an exception table to be handled later.
• Values that are read in from devices: An example of this is on an assembly line, where a reading is taken but is so far out of range it couldn’t be true, because of the malfunction of a device or just a human moving a sensor. Too many exception rows would require a look at the equipment, but only a few might be normal and acceptable. Another possibility is when someone scans a printed page using a scanner and inserts the data. Often, the values read are not right and have to be checked manually.

For our example, I’ll design a table to take weather readings from a single thermometer. Sometimes, this thermometer sends back bad values that are impossible. We need to be able to put in readings, sometimes many at a time, because the device can cache results for some time if there is signal loss, but it tosses off the unlikely rows.

We build the following table, initially using a constraint to implement the simple sanity check. In the analysis of the data, we might find anomalies, but in this process, all we’re going to do is look for the “impossible” cases:

 CREATE SCHEMA Measurements;

 GO

 CREATE TABLE Measurements.WeatherReading

 (

  WeatherReadingId int NOT NULL identity

  CONSTRAINT PKWeatherReading PRIMARY KEY,

  ReadingTime datetime2(3) NOT NULL

  CONSTRAINT AKMeasurements_WeatherReading_Date UNIQUE,

  Temperature float NOT NULL

  CONSTRAINT chkMeasurements_WeatherReading_Temperature

  CHECK(Temperature between -80 and 150)

  --raised from last edition for global warming

 );

Then, we go to load the data, simulating what we might do when importing the data all at once:

 INSERT into Measurements.WeatherReading (ReadingTime, Temperature)

 VALUES ('20080101 0:00',82.00), ('20080101 0:01',89.22),

 ('20080101 0:02',600.32),('20080101 0:03',88.22),

 ('20080101 0:04',99.01);

As we know with CHECK constraints, this isn’t going to fly:

Select all the data in the table, and you’ll see that this data never gets entered. Does this mean we have to dig through every row individually? Yes, in the current scheme. Or you could insert each row individually, which would take a lot more work for the server, but if you’ve been following along, you know we’re going to write an INSTEAD OF trigger to do this for us. First we add a table to hold the exceptions to the Temperature rule:

 CREATE TABLE Measurements.WeatherReading_exception

 (

  WeatherReadingId int NOT NULL identity

  CONSTRAINT PKMeasurements_WeatherReading_exception PRIMARY KEY,

  ReadingTime datetime2(3) NOT NULL,

  Temperature float NOT NULL

 );

Then, we create the trigger:

 CREATE TRIGGER Measurements.WeatherReading$InsteadOfInsertTrigger

 ON Measurements.WeatherReading

 INSTEAD OF INSERT AS

 BEGIN

  SET NOCOUNT ON;

  SET ROWCOUNT 0; --in case the client has modified the rowcount

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  DECLARE @msg varchar(2000), --used to hold the error message

  --use inserted for insert or update trigger, deleted for update or delete trigger

  --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

  --that is equal to number of merged rows, not rows being checked in trigger

  @rowsAffected int = (SELECT COUNT(*) FROM inserted);

  -- @rowsAffected int = (SELECT COUNT(*) FROM deleted);

  BEGIN TRY

 --[validation section]

 --[modification section]

 --<perform action>

 --BAD data

 INSERT Measurements.WeatherReading_exception

 (ReadingTime, Temperature)

 SELECT ReadingTime, Temperature

 FROM inserted

 >WHERE NOT(Temperature between -80 and 120);

 --GOOD data

 INSERT Measurements.WeatherReading (ReadingTime, Temperature)

 SELECT ReadingTime, Temperature

 FROM inserted

 WHERE (Temperature between -80 and 120);

  END TRY

  BEGIN CATCH

  IF @@trancount > 0

  ROLLBACK TRANSACTION;

  THROW; --will halt the batch or be caught by the caller's catch block

  END CATCH

 END

Now, we try to insert the rows with the bad data still in there:

 INSERT INTO Measurements.WeatherReading (ReadingTime, Temperature)

 VALUES ('20080101 0:00',82.00), ('20080101 0:01',89.22),

 ('20080101 0:02',600.32),('20080101 0:03',88.22),

 ('20080101 0:04',99.01);

 SELECT *

 FROM Measurements.WeatherReading;

The good data is in the following output:

The nonconforming data can be seen by viewing the data in the exception table:

 SELECT *

 FROM Measurements.WeatherReading_exception;

This returns the following result:

Now, it might be possible to go back and work on each exception, perhaps extrapolating the value it should have been, based on the previous and the next measurements taken:

 (88.22 + 89.22) /2 = 88.72

Of course, if we did that, we would probably want to include another attribute that indicated that a reading was extrapolated rather than an actual reading from the device. This is obviously a very simplistic example, and you could even make the functionality a lot more interesting by using previous readings to determine what is reasonable.

Forcing No Action to Be Performed on a Table

Our final INSTEAD OF trigger example deals with what’s almost a security issue. Often, users have too much access, and this includes administrators who generally use sysadmin privileges to look for problems with systems. Some tables we simply don’t ever want to be modified. We might implement triggers to keep any user—even a system administrator—from changing the data.

In this example, we’re going to implement a table to hold the version of the database. It’s a single-row “table” that behaves more like a global variable. It’s here to tell the application which version of the schema to expect, so it can tell the user to upgrade or lose functionality:

 CREATE SCHEMA System;

 go

 CREATE TABLE System.Version

 (

  DatabaseVersion varchar(10) NOT NULL

 );

 INSERT into System.Version (DatabaseVersion)

 VALUES ('1.0.12'),

Our application always looks to this value to see what objects it expects to be there when it uses them. We clearly don’t want this value to get modified, even if someone has db_owner rights in the database. So, we might apply an INSTEAD OF trigger:

 CREATE TRIGGER System.Version$InsteadOfInsertUpdateDeleteTrigger

 ON System.Version

 INSTEAD OF INSERT, UPDATE, DELETE AS

 BEGIN

 SET NOCOUNT ON;

 SET ROWCOUNT 0; --in case the client has modified the rowcount

 --use inserted for insert or update trigger, deleted for update or delete trigger

 --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

 --that is equal to number of merged rows, not rows being checked in trigger

 DECLARE @msg varchar(2000), --used to hold the error message

 --use inserted for insert or update trigger, deleted for update or delete trigger

 --count instead of @@rowcount due to merge behavior that sets @@rowcount to a number

 --that is equal to number of merged rows, not rows being checked in trigger

  @rowsAffected int = (SELECT COUNT(*) FROM inserted);

 IF @rowsAffected = 0 SET @rowsAffected = (SELECT COUNT(*) FROM deleted);

 --no need to complain if no rows affected

 IF @rowsAffected = 0 RETURN;

 --No error handling necessary, just the message.

 --We just put the kibosh on the action.

 THROW 50000, 'The System.Version table may not be modified in production', 16;

 END;

Attempts to delete the value, like so

 UPDATE system.version

 SET DatabaseVersion = '1.1.1';

 GO

will result in the following:

Checking the data, you will see that it remains the same:

 SELECT *

 FROM System.Version;

 Returns:

The administrator, when doing an upgrade, would then have to take the conscious step of running the following code:

 ALTER TABLE system.version

  DISABLE TRIGGER version$InsteadOfInsertUpdateDeleteTrigger;

Now, you can run the update statement:

 UPDATE system.version

 SET DatabaseVersion = '1.1.1';

And checking the data

 SELECT *

 FROM System.Version;

you will see that it has been modified:

Reenable the trigger using ALTER TABLE . . . ENABLE TRIGGER:

 ALTER TABLE system.version

  ENABLE TRIGGER version$InsteadOfInsertUpdateDeleteTrigger;

Using a trigger like this (not disabled, of course, which is something you can catch with a DDL trigger) enables you to “close the gate,” keeping the data safely in the table, even from accidental changes.