在使用SQL Server插入/更新之前，如何验证数据？ [英] How can I validate data before insert/update with SQL Server?

问题描述

我有一个这样定义的表

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
)

如果 ForeignPropertyId 和 ForeignValue的值都需要产生一个错误列均为 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.

这是什么我尝试过

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

但这给了我一个语法错误。也许，我使用 RAISERROR 的方式是错误的。

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

如何正确添加触发器以验证数据在 INSERT 和 UPDATE 上？

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

推荐答案

这看起来像是检查约束的工作，而不是触发器：

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);

这篇关于在使用SQL Server插入/更新之前，如何验证数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！