3
\$\begingroup\$

I have the following SQL structure (simplified for brevity):

TABLE [Posts] 
     [Id]       INT NOT NULL IDENTITY PRIMARY KEY, 
     [ParentId] INT NULL, 
     [Type]     INT NOT NULL, 
     FOREIGN KEY ( [ParentId] ) REFERENCES [Posts] ( [Id] ) 
     FOREIGN KEY ( [Type] ) REFERENCES [PostTypes] ( [Type] ) 

TABLE [PostTypes]
     [Id]   INT NOT NULL PRIMARY KEY,
     [Name] SYSNAME NOT NULL

TABLE [PostTypeValidParents]
     [ChildType]  INT NOT NULL, 
     [ParentType] INT NOT NULL, 
     PRIMARY KEY ( [ChildType], [ParentType] ), 
     FOREIGN KEY ( [ChildType] ) REFERENCES [PostTypes] ( [Type] ), 
     FOREIGN KEY ( [ParentType] ) REFERENCES [PostTypes] ( [Type] ) 

INSERT [PostTypes] 
       ( [Type], [name] ) 
VALUES ( 0, 'Global' ), 
       ( 1, 'Category' ), 
       ( 2, 'Thread' ), 
       ( 3, 'Reply' )

INSERT [PostTypeValidParents] 
       ( [ChildType], [ParentType] ) 
VALUES ( 1, 0 ),
       ( 1, 1 ), 
       ( 2, 1 ), 
       ( 3, 2 )

Now, I want to create a trigger which validate [Posts] based on the rules defined in [PostTypeValidParents]. Here's what I have so far:

CREATE TRIGGER [ValidatePost]
ON [Posts]
AFTER INSERT, UPDATE
AS
BEGIN
    IF ( EXISTS ( SELECT * FROM [deleted] ) AND 
         UPDATE ( [Type] ) )
        RAISERROR ( N'The [Posts].[Type] column cannot be modified.', 16, 1 );

    DECLARE @parents TABLE 
      (
        [ChildType]  INT NOT NULL,
        [ParentType] INT NOT NULL 
      );

    INSERT @parents
    SELECT c.[Type], 
           CASE 
             WHEN p.[Type] IS NULL THEN 0 
             ELSE p.[Type] 
           END
    FROM   [inserted] c
           LEFT JOIN [Forum].[Posts] p
                  ON c.[ParentId] = p.[Id];

    DECLARE @childType NVARCHAR(30), 
            @parentType NVARCHAR(30);

    WITH [Conflicts] AS 
      (
        SELECT [ChildType], [ParentType]
        FROM   @parents
        EXCEPT
        SELECT [ChildType], [ParentType]
        FROM   [PostTypeValidParents] 
      )
    SELECT TOP 1
           @childType = c.[Name], 
           @parentType = p.[Name]
    FROM   [Conflicts] x
           JOIN [PostTypes] c
             ON x.[ChildType] = c.[Type]
           JOIN [PostTypes] p
             ON x.[ParentType] = p.[Type];

    IF ( @childType IS NOT NULL )
        RAISERROR ( N'Posts of type ''%s'' may not have a parent of type ''%s''.', 16, 1, @childType, @parentType );
END

My basic pattern here is,

  1. check for read-only fields and RAISERROR if modified
  2. SELECT critical conditions into a table variable
  3. find unsupported conditions with an EXCEPT inside a CTE, SELECT'ing into a variable and RAISERROR if not null.

It seems to work well enough (though I haven't thoroughly tested it yet). My problem is that this seems a bit verbose for what should be a relatively simple validation trigger. I'll have to write many of these in the near future, so I'm hoping to find a simpler pattern that accomplishes the same thing. Can anyone offer suggestions on how to reduce this code?

\$\endgroup\$
1
\$\begingroup\$

Your code is correct as long as you plan to validate business integrity using triggers.

But I would question whether business validation in SQL Server is a right choice:

  • You have no means (other than parsing return error string) to process errors on client side.
  • This kind of code may cause deadlocks under heavy load (I can't find them here but it doesn't mean there aren't any :))
  • Each insert causes at least 2 table searches

Alternative way (how most applications works) is to rely on a business logic inside client application to enforce business rules. It's much easier (and straightforward) to verify that your parent is of a valid type.

Also, based on your PostTypes table I think each type of "posts" should be different enough to store it in its own table. In that case the whole story of validating types is gone, and all the validation will be done by foreign keys.

\$\endgroup\$
  • \$\begingroup\$ Thanks for taking the time to answer. :) I don't have control over the client code, in fact several different client apps will connect to this db, so I don't want to rely on a BLL in the client. About splitting [Posts] into separate tables, I'd initially designed it that way, but there's a lot of common code for all post types (revisions, flags, access control, etc.) that would need to be duplicated. I suppose I could use something like a TPT inheritance model, but I'm not sure how to do that in pure SQL. \$\endgroup\$ – p.s.w.g Mar 12 '13 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.