Request review of sql validation trigger 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, 


check for read-only fields and RAISERROR if modified 
SELECT critical conditions into a table variable 
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?
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.