禁止使用默认约束插入行

Question

是否有任何方法可以防止在表中指定列中插入数据并仅使用默认(约束)值?

。我有列:

LogInsert (DF GETDATE())
LogUser   (DF ORIGINAL_LOGIN())

都由DEFAULT约束定义。我不希望允许用户插入到这些列中，而是在插入新行时使用这里的默认值。

这会引发一个错误。

INSERT  INTO T1
        ( C1
        ,C2
        ,LogInsert
        ,LogUser
        )
VALUES  ( 'A'
        ,'B'
        ,'20160101 10:53'
        ,'domainuser'
        );

用户应该能够执行下面的脚本而没有错误。

INSERT  INTO T1
        ( C1, C2 )
VALUES  ( 'A', 'B' );

Answer 1

你总是可以给你的用户一个视图而不是一个表。然后，您可以选择完全隐藏列，或者(像这里一样)通过视图:

计算列，这样它们就不能将值插入列中。

create table dbo._T1 (
    ID int IDENTITY(1,1) not null,
    Inserted datetime2 constraint DF__T1_Inserted DEFAULT (SYSDATETIME()) not null,
    ABC varchar(10) not null,
    constraint PK__T1 PRIMARY KEY (ID)
)
go
create view dbo.T1
with schemabinding
as
    select
        ID,
        COALESCE(Inserted,SYSDATETIME()) as Inserted,
        ABC
    from dbo._T1
go
insert into dbo.T1 (ABC) values ('abc')
go
insert into dbo.T1 (ABC,Inserted) values ('def',SYSDATETIME())

结果:

(1 row(s) affected)
Msg 4406, Level 16, State 1, Line 19
Update or insert of view or function 'dbo.T1' failed because it contains a derived or constant field.

所有的用户查询继续只使用T1。它只是碰巧是一个视图而不是一个表。

---

上面以COALESCE(Inserted,SYSDATETIME())为例。这里使用什么并不重要，也不需要匹配例如默认定义。重要的是，在Inserted列上执行一些计算，使其成为视图中的只读列。

Answer 2

您可以在表上创建一个Check Constraint，例如下面的

CREATE TABLE [dbo].[T1]
(
     [C1] VARCHAR(50)
    ,[LogInsert] DATETIME DEFAULT GETDATE()
    ,[LogUser] VARCHAR(500) DEFAULT ORIGINAL_LOGIN()
)
ALTER TABLE [T1]  WITH CHECK ADD  CONSTRAINT [CK_T1_LogInsert] CHECK ([LogInsert] = GETDATE())
ALTER TABLE [T1]  WITH CHECK ADD  CONSTRAINT [CK_T1_LogUser] CHECK ([LogUser] = ORIGINAL_LOGIN())

INSERT INTO [dbo].[T1] ([C1]) VALUES ('A')
INSERT INTO [dbo].[T1] ([C1]) VALUES ('B')
INSERT INTO [dbo].[T1] ([C1]) VALUES ('C')
SELECT * FROM [dbo].[T1]
--Will Fail
INSERT INTO [dbo].[T1] ([C1],[LogInsert]) VALUES ('D','2016-11-11 00:00')
INSERT INTO [dbo].[T1] ([C1],[LogUser]) VALUES ('D','Not Your UserName')

或

您可以强制用户只使用存储过程插入，而不允许将其作为参数，对于批量插入，也可以使用表变量。