检查计算列上的约束 [英] check constraint on computed column

问题描述

大家好

我创建了此表

Hi all

I created this table

a | b | c |  total  | evaluation  
1 | 2 | 3 |    6    | 
5 | 2 | 8 |    15   |      

--------------> 合计"列是求和(a + b + c)的结果

我想检查总计"列上的约束
如果合计= 12组评估=''完美''
否则将评估设置为弱"

--------------> " Total" is computed column is result for sum (a+b+c)

I want to check constraint on Total column
if total = 12 set evaluation =''Perfect''
else set evaluation = ''weak''

推荐答案

尝试一下:

Try this:

ALTER PROCEDURE dbo.ManageLaborEval
	(
@a int,
@b int,
@c int
)

AS
BEGIN

	Declare @sum bigint;
	Set @sum = @a + @b + @c;

	Declare @Evaluation NVARCHAR(30);
	IF @sum = 12
	BEGIN
		Set @Evaluation = 'Perfect';
	END
	ELSE
	BEGIN
		Set @Evaluation = 'weak';
	END
		
	INSERT INTO Table1(a, b, c, total, evaluation) VALUES (@a,@b,@c, @sum, @Evaluation)

END

希望对您有帮助，

Azee ...

Hope it helps,

Azee...

无论是更新一行还是将多行插入事务中，此解决方案都将起作用.这是一个触发器，每当插入一个或多个行或更新一个或多个行时，它就会自动运行.


USE [您的数据库名称]
GO
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
创建触发器[dbo] .CheckTheTotal]
开启[dbo].[YourTableName]
插入之后，更新
AS
开始
更新YourTableName SET评估=总计= 12时的情况，然后在"YourTableName.YourPrimaryKeyName IN"中选择"Perfect"否则"Weak"结束(从INSERTED中选择YourPrimaryKeyName)；
END

This solution will work whether one or multiple rows are updated or inserted in a transaction. It is a trigger that will run automaticaly whenever one or more rows are inserted or one or more rows are updated.


USE [YourDatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].CheckTheTotal]
ON [dbo].[YourTableName]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE YourTableName SET Evaluation = CASE WHEN total=12 THEN ''Perfect'' ELSE ''Weak'' END WHERE YourTableName.YourPrimaryKeyName IN (SELECT YourPrimaryKeyName FROM INSERTED);
END

这篇关于检查计算列上的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！