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 andRAISERROR
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?