SQLServer 表字段为 NULL 而视图为NOT NULL 问题

之前有处理几次这个问题，不过还是简单记录下来吧。

--  创建测试表和视图

--  DROP DATABASE Demo;
CREATE DATABASE Demo;
GO
USE Demo
GO

--  DROP TABLE [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable](
	[Identifier] [uniqueidentifier] NOT NULL,[Name] [varchar](50) NULL,[value] [numeric](18,4) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] 
ADD CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED([Identifier])
GO

ALTER TABLE [dbo].[TestTable] 
ADD CONSTRAINT [DF_TestTable_Identifier] DEFAULT (newsequentialid()) FOR [Identifier]
GO


--  DROP VIEW [dbo].[VTestTable] 
CREATE VIEW [dbo].[VTestTable]    
AS    
SELECT [Identifier],[Name],[value]
FROM dbo.TestTable
GO

插入数据到视图(正常)：

INSERT INTO [VTestTable]([Identifier],[value])
SELECT NEWID(),'KK',NULL
GO

UPDATE [VTestTable] SET [value]=NULL
GO

由于字段[value]没有默认值,读取或插入数据时null改为0 ：

--  由于字段[value]没有默认值,读取或插入数据时null改为0
ALTER VIEW [dbo].[VTestTable]    
AS    
SELECT [Identifier],ISNULL([value],0) [value]
FROM dbo.TestTable
GO

此时对视图更新或插入失败：

--  此时对视图更新或插入失败
INSERT INTO [VTestTable]([Identifier],NULL
GO

UPDATE [VTestTable] SET [value]=NULL
GO

原因是：视图不允许为NULL！

解决方法（一）：

--  解决方法(一)：增加触发器
--  DROP TRIGGER [dbo].[tr_VTestTable_insert]
CREATE TRIGGER [dbo].[tr_VTestTable_insert]
ON DBO.[VTestTable]
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO TestTable([Name],[value])
    SELECT [Name],0) FROM inserted;
END
GO

--  DROP TRIGGER [dbo].[tr_VTestTable_update]
CREATE TRIGGER [dbo].[tr_VTestTable_update]
ON DBO.[VTestTable]
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE t1 SET 
    t1.[Name] = t2.[Name],t1.[value] = ISNULL(t2.[value],0)
    FROM TestTable AS t1,inserted AS t2 WHERE t1.[Identifier] = t2.[Identifier]
END
GO

--  此时操作正常!
INSERT INTO [VTestTable]([Identifier],NULL
GO
UPDATE [VTestTable] SET [value]=NULL --更新为null,触发器替换为0
GO

--  解决方法(二)：增加默认值约束

/*--删除刚创建的对象
DROP TRIGGER [dbo].[tr_VTestTable_insert]
DROP TRIGGER [dbo].[tr_VTestTable_update]
*/

--  增加默认值约束
ALTER TABLE [dbo].[TestTable] 
ADD CONSTRAINT [DF_TestTable_value] DEFAULT (0) FOR [value]
GO

--  更新表中原有为 NULL 的值
UPDATE [dbo].[TestTable] SET [value] = 0
GO

--修改视图
ALTER VIEW [dbo].[VTestTable]    
AS    
SELECT [Identifier],[value]
FROM dbo.TestTable
GO

--  插入时不显示插入,[value] 默认为0;
INSERT INTO [VTestTable]([Identifier],[Name])
SELECT NEWID(),'KK'
GO

--  此时允许更新为 NULL !
UPDATE [VTestTable] SET [value]=NULL
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!