Jaylen Jaylen - 4 months ago 21
SQL Question

How can I validate data before insert/update with SQL Server?

I have a table defined like this

CREATE TABLE [dbo].[ObjectRelationClauses]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY,
[RelationId] INT NOT NULL,
[OperatorType] NVARCHAR(3) NOT NULL,
[LocalPropertyId] INT NOT NULL,
[ForeignPropertyId] INT NULL,
[ForeignValue] VARCHAR(255) NULL,
[ParentClauseId] INT NULL
)


I need to be able to raise an error if the value of both
ForeignPropertyId
and
ForeignValue
columns both
null
, otherwise I want to perform the operation.

Here is what I tried

CREATE TRIGGER [dbo].[Trigger_ObjectRelationClauses]
ON [dbo].[ObjectRelationClauses]
FOR INSERT, UPDATE
AS
BEGIN
SET NoCount ON
IF(ForeignPropertyId IS NULL AND ForeignValue IS NULL)
BEGIN
RAISERROR('Either ForeignPropertyId or ForeignValue must be provided to perform this action!')
END

END


but this gives me a syntax error. Perhaps, the way I am using
RAISERROR
is wrong.

How can I correctly add a trigger to validate the data on
INSERT
and
UPDATE
?

Answer

This looks like a job for a check constraint, not a trigger:

ALTER TABLE [dbo].[ObjectRelationClauses]
ADD CONSTRAINT foreign_chk CHECK 
([ForeignPropertyId] IS NOT NULL OR [ForeignValue] IS NOT NULL);