引用 SQL Server 2005 中 DEFAULT 定义中的另一列 [英] Referencing another column in DEFAULT definition in SQL Server 2005

问题描述

我想在我的表格中定义一个具有以下要求的列:

I want to define a column in my table with following requirements:

1. 该列应该是可插入的.如果 INSERT 语句中提供了该值，则应插入该值.
2. 如果 INSERT 语句中未引用该列，则应将其设置为其他两列的总和.

由于第一个要求，我不能使用计算列，因为它们不可插入.由于第二个原因，我不能使用 DEFAULT，因为它不允许引用定义中的其他列.我还有什么其他选择?

Because of the first requirement, I cannot user computed columns, since they are not insertable. Because of the second, I cannot use DEFAULT, because it doesn't allow referencing other columns in the definition. What other options do I have?

顺便说一句，该列应该是 NOT NULL.

BTW, the column should be NOT NULL.

推荐答案

给你，我用一个示例模式来演示这个，因为你没有提供你的真实表/列名称.

Here you go, I'm demonstrating this with an example schema since you've not provided your real table/column names.

表格:

CREATE TABLE test
  (
     id   INT NOT NULL PRIMARY KEY IDENTITY, --made up key
     col1 INT, --first column to add, wasn't sure if this was nullable or not
     col2 INT, --second column to add, wasn't sure if this was nullable or not
     col3 INT NOT NULL --this is the column to optionally insert into
  ) 

这里是触发器定义:

CREATE TRIGGER demo
ON test
INSTEAD OF INSERT
AS
    INSERT INTO test (col1,col2,col3)
    SELECT inserted.col1,
           inserted.col2,
           CASE
             WHEN inserted.col3 IS NULL THEN COALESCE(inserted.col1, 0) + COALESCE(inserted.col2, 0)
             ELSE inserted.col3
           END
    FROM   inserted

基本上它会用触发器中的语句替换表上执行的任何插入语句，因此我使用 inserted 临时表检查是否尝试插入到我们的非可为空的可选列 col3 为 NULL.如果是，我用添加 col1 和 col2 替换它(我用零合并，因为你没有提到两个源列是否可以为空).

Basically it replaces any insert statement done on the table with the one in the trigger, so I check using the inserted temporary table to see if the value that is trying to be inserted into our non-nullable optional column, col3, is NULL. If it is, I replace it with the addition of col1 and col2 (I'm coalescing with zero as you didn't mention if the two source columns are nullable or not).

然后您可以运行包含或不包含它的插入语句，尽管事实上 col3 不可为空:

You can then run insert statements which either include it or not, despite the fact col3 is not nullable:

INSERT INTO test(col1,col2)
SELECT 12, 31
GO

INSERT INTO test(col1, col2, col3)
SELECT 1, 2, 89
GO

结果是:

ID  COL1 COL2 COL3
------------------  
1   12   31    43
2   1    2     89

如果触发器不存在，您可能会在尝试运行第一个插入语句时遇到错误，告诉您它无法将 NULL 插入 col3.

If the trigger wasn't there, you could have got an error trying to run that first insert statement, telling you it couldn't insert NULL into col3.

另请注意，指定值的第二个插入语句没有按照要求被添加替换.

Notice also that the second insert statement that specifies a value has not been replaced by the addition, as requested.

这是一个有效的 SQL Fiddle.

Here's a working SQL Fiddle.

这篇关于引用 SQL Server 2005 中 DEFAULT 定义中的另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！