我可以为视图中的列指定插入的默认值吗？

发布于 2024-10-01 09:26:43 字数 862 浏览 1 评论 0原文

一些第三方软件使用“暂存”表进行各种导入，以便我们尝试导入的任何内容都可以通过其业务逻辑进行验证。系统中的任何表都可以作为目标，标准做法是将 col1 指定为您正在执行的导入的“类型”。

表中的所有列都是通用的，即。 “data1”、“data2”、“data3”等...

因此 col1 是指定的“类型”列，并将设置为“ContactImport”或“ProductImport”。我实际上不想返回“类型”列...或者至少将其重命名为“ImportType”。

因此，我开始创建视图以更好地管理我们的导入/导出。然而，如果人们使用 v_ContactImport 视图并且必须手动将 col1 设置为“ContactImport”，那么似乎很容易出错，对吧？

那么有没有一种方法可以让视图为 col1 提供默认值。

因为我很着急，不确定这有多清楚，所以这是视图本身的一个简单示例：

CREATE VIEW v_ContactImport 
AS

SELECT data2 as FirstName,
       data3 as LastName,
       data4 as Phone       
FROM StagingTable
WHERE data1='ContactImport'

并且想这样做

INSERT INTO v_ContactImport (FirstName, LastName, Phone)
VALUES ('Jack', 'Handey', '5555555555' )

，那么如果我选择 v_ContactImport 该记录将会返回，因为 data1 会自动设置为“ContactImport

”猜测这是不可能的，但我想我会问。

原文

Some third party software uses a "staging" table for various imports, so that anything we try to import can be validated through their business logic. Any table in the system can be a target, and it is standard practice to assign col1 as the "type" of import you are performing.

All the columns in the table are generic, ie. "data1", "data2", "data3" etc...

So col1 is the designated "type" column and would be set to "ContactImport" or "ProductImport". I'd like to not actually return the "type" column... or at least rename it to "ImportType."

So I've started creating views to better manage our imports/exports. However it seems like it could be error prone if people are using the v_ContactImport view and have to manually set col1 to "ContactImport" right?

So is there a way I can have the view give a default value to col1.

Since I'm rushing and not sure how clear that is, here's a quick example of the view itself:

CREATE VIEW v_ContactImport 
AS

SELECT data2 as FirstName,
       data3 as LastName,
       data4 as Phone       
FROM StagingTable
WHERE data1='ContactImport'

And would like to just do

INSERT INTO v_ContactImport (FirstName, LastName, Phone)
VALUES ('Jack', 'Handey', '5555555555' )

So then if I select v_ContactImport that record would come back because data1 would automatically be set to 'ContactImport'

Kinda guessing this isn't possible, but thought I'd ask.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江南月 2024-10-08 09:26:43

您可以定义视图，然后（在 SQL Server 2005 及更高版本中，其他 DBMS 中可能有类似的结构）创建 INSTEAD OF 触发器。从 BOL 剪切、粘贴和修改，它看起来像：

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on v_ContactImport
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement that adds in your column
  INSERT INTO StagingTable
       SELECT 'ContactImport', Col1, Col2, ColEtc
       FROM inserted
END
GO

You could define your view, and then (in SQL Server 2005 and up, and there's probably similar structures in other DBMSs) create an INSTEAD OF trigger. Cutting, pasting, and modifying from BOL, it would look something like:

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on v_ContactImport
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement that adds in your column
  INSERT INTO StagingTable
       SELECT 'ContactImport', Col1, Col2, ColEtc
       FROM inserted
END
GO

回复收藏 0 原文