引用 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:
- 该列应该是可插入的.如果 INSERT 语句中提供了该值,则应插入该值.
- 如果 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屋!