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
SELECTcritical conditions into a table variable
- find unsupported conditions with an
EXCEPTinside a CTE,
SELECT'ing into a variable and
RAISERRORif 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?