Create a Trigger (or better, a Stored Procedure) in SQL Server to validate against another table

  • Article

I saw this question in a mailing list recently: 

You are creating a sports betting application, where you choose between 2 teams that are playing on a given day. A user picks one of the teams, you have to validate that the team they chose is actually playing that day. How do you validate within the database using a trigger?

The question specifically asked how to achieve this using a database trigger, and that solution is shown below.  However, a debate also ensued on the better architectural design.  Should this be implemented as application validation logic, or should it be implemented as a trigger? 

The quick answer is "neither", implement it as a stored procedure.

There are actually several considerations, including the data source, its volatility, and the size of the lookup data.

First, lookup data is not always data within the same database. Your data source may actually be a web service on some far away server.  I would not attempt to implement web service lookups within the database (at least, not within SQL Server 2000... for Yukon, time will tell if this becomes a viable best practice or not).  If you are validating against a web service before you insert into your application, this should definitely be implemented as an application layer validation routine.  For the rest of this discussion, we assume that the lookup data is a table within the same database as the table being inserted into.

Second, the decision on whether to implement validation within the application or the database involves whether the data being validated against is volatile or not.  If the data being validated against is frequently modified, then it will be difficult to effectively cache a version of the data at the application layer that provides any type of speed improvement over a trigger or stored procedure.  If the lookup data is volatile, there will be likely be a larger impact on performance due to out of process calls from the database layer to the database for validation.  If the validation data is fairly static with infrequent updates, then this would be a good candidate for application layer validation logic, reducing the number of times that the lookup data is retrieved from the data store by comparing against a version cached at the application layer. 

Another consideration should be the size of the lookup data itself.  If there are several megs of data within the possible validation set, then caching it at the application layer may not make sense simply because the imposed memory overhead would create memory pressure on the application server.  Further, your application logic is not going to be able to perform lookups on several megabytes of information as quickly as it could within the database (assuming that network traffic overhead is a very minimal consideration).

The best answer is "it depends."  Moving the data to the application layer or the database should be a decision based on architectural needs.  This decision should also be reviewed against the application in a stress test scenario to ensure that the architectural optimization you believe you are achieving actually provides the improvement you believe it does.  Some questions you should ask yourself:

  •  How many applications are going to touch this data? 
  • Can you be sure that consistency checks will be properly enforced at the application layer?
  • Does the size of the lookup data lend itself to faster lookups within the RDBMS, leveraging its more efficient indexing than you are likely to achieve using code?

Instead of using a trigger, it makes more sense to restrict direct table access to users while providing CRUD interfaces to the data only through stored procedures.  This approach allows you to restrict who can modify the data while allowing you to encapsulate modification logic.  Better, the functionality within the example below can easily be implemented as a stored procedure with a few parameters.  You still face the architectural debate of whether this is "business logic" that should be encapsulated within the application or data consistency validation rules that belong in the database, but you better constrain who can modify the data and how.  Better, if you import data from one source to another, you don't have to drop the trigger first (lest the trigger fire for every single inserted row in the bulk import).

The original question asked about triggers with lookups, so here it is.  This example has 3 tables: Team, Schedule, and UserPick.  Team is a table defining teams, here I used football teams.  The schedule defines the 2 teams that are playing and when they are playing, and the UserPick table defines the user's choice of which team they think will win.  A trigger is fired before the row is inserted into the UserPick table, validating that the chosen team is actually one of the two scheduled for the game.  An added bonus shows how to insert a value into an identity column using SET IDENTITY_INSERT tablename ON.

CREATE TABLE [Team] (
[TeamID] [int] NOT NULL ,
[TeamName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Schedule] (
[GameID] [int] IDENTITY (1, 1) NOT NULL ,
[GameDateTime] [datetime] NOT NULL ,
[HomeTeamID] [int] NOT NULL ,
[VisitorTeamID] [int] NOT NULL ,
CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED
(
[GameID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Schedule_Team] FOREIGN KEY
(
[HomeTeamID]
) REFERENCES [Team] (
[TeamID]
),
CONSTRAINT [FK_Schedule_Team1] FOREIGN KEY
(
[VisitorTeamID]
) REFERENCES [Team] (
[TeamID]
)
) ON [PRIMARY]
GO

CREATE TABLE [UserPick] (
[GameID] [int] NOT NULL ,
[TeamID] [int] NOT NULL ,
CONSTRAINT [PK_UserPick] PRIMARY KEY CLUSTERED
(
[GameID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/*Create the trigger for inserts */
CREATE TRIGGER UserPickInserted
ON UserPick
FOR INSERT
AS
DECLARE @homeTeamID int,
@visitorTeamID int,
@gameID int,
@teamID int

SELECT @gameID = i.GameID,
@teamID = i.TeamID FROM inserted i

/*See if the inserted value matches the id for either the home team or visitor team*/
SELECT @homeTeamID = s.HomeTeamID,
@visitorTeamID = s.VisitorTeamID
FROM Schedule s INNER JOIN inserted i
ON s.GameID = i.GameID
WHERE s.HomeTeamID = i.TeamID
OR s.VisitorTeamID = i.TeamID

IF @homeTeamID is null and @visitorTeamID is null
BEGIN
RAISERROR ('Invalid selection: Game %d, TeamID %d.',
16, 1, @gameID,@teamID)
ROLLBACK TRANSACTION
END

 To test this, try something like the following:

/*Insert some goofy data*/
insert into Team values (1, 'Falcons','Atlanta')
insert into Team values (2, 'Ravens','Baltimore')
insert into Team values (3, 'Cowboys','Dallas')
insert into Team values (4, 'Texans','Houston')
insert into Team values (5, 'Rams','St. Louis')
insert into Team values (6, 'Bears','Chicago')

SET IDENTITY_INSERT Schedule ON
insert into Schedule(GameID, GameDateTime, HomeTeamID,VisitorTeamID)
values(1, '2004-09-04 14:20:16.727', 1,2)
insert into Schedule(GameID, GameDateTime, HomeTeamID,VisitorTeamID)
values(2, '2004-09-04 14:20:16.727', 3,4)
insert into Schedule(GameID, GameDateTime, HomeTeamID,VisitorTeamID)
values(3, '2004-09-04 14:20:16.727', 5,6)
SET IDENTITY_INERT Schedule OFF
/*Test an insertion that should be OK*/
insert into UserPick values(1,1)
/*Test an insertion that should be invalid*/
insert into UserPick values(2,5)

As I stated previously, this is likely better implemented as a stored procedure rather than as a trigger.  Here is the stored procedure implementation for the insert.  Note that you can also restrict the stored procedure's access per role or user independently of the table's permissions, allowing you to defend against SQL injection attacks (if someone inserts malicious SQL code, they won't have the permissions to execute it).

/*Create the procedure for insert */
CREATE PROCEDURE dbo.InsertUserPick(@gameID int, @teamID int)
AS
DECLARE @homeTeamID int,
@visitorTeamID int

/*See if the inserted value matches the id for either the home team or visitor team*/
SELECT @homeTeamID = s.HomeTeamID,
@visitorTeamID = s.VisitorTeamID
FROM Schedule s
WHERE s.GameID = @gameID
AND (s.HomeTeamID = @teamID
OR s.VisitorTeamID = @teamID)

IF @homeTeamID is null and @visitorTeamID is null
BEGIN
RAISERROR ('Invalid selection: Game %d, TeamID %d.',
16, 1, @gameID,@teamID)
END
ELSE
BEGIN
INSERT INTO UserPick(GameID,TeamID) VALUES (@gameID,@teamID)
END