問題描述
表中有一個列，它應該存儲列列表的校驗和，但我想將其創建為該表的默認值:
There is a column in a table, which should store Checksum for list of columns, but I'd like to create it as a default value for this table:
CREATE TABLE tblVitalyTest (id BIGINT IDENTITY(1,1), startTime DATETIME, value1 INT, value2 INT, value3 INT, value4 DATETIME, BinCheckSum BIGINT)
GO
ALTER TABLE tblVitalyTest ADD DEFAULT (BINARY_CHECKSUM(value1, value2, value3, value4)) FOR [BinCheckSum]
GO
所以，應該這樣使用:
INSERT INTO tblVitalyTest (startTime,value1,value2,value3,value4) VALUES ('2015-05-25',1,1,1,'2015-11-11')
結果應該是這樣的:
id、startTime、value1、value2、value3、value4、BinCheckSum
id, startTime, value1, value2, value3, value4, BinCheckSum
1, "2015-05-25", 1, 1, 1, "2015-11-11", 46173
1, "2015-05-25", 1, 1, 1, "2015-11-11", 46173
但不幸的是，表默認"表創建失敗.
But table "default" table creation fails unfortunately.
有一個查詢可以玩:
DECLARE @a TABLE (id BIGINT IDENTITY(1,1), startTime DATETIME, value1 INT, value2 INT, value3 INT, value4 DATETIME)
INSERT INTO @a (startTime,value1,value2,value3,value4)
VALUES ('2015-05-25 08:00',1,1,1,'2015-11-11'),('2015-05-25 09:00',1,2,1,'2015-11-11')
,('2015-05-25 10:00',null,null,null,'2015-11-11'),('2015-05-25 11:00',2,1,1,'2015-11-11'),('2015-05-25 11:00',null,null,null,'2015-11-12')
SELECT a.*,BINARY_CHECKSUM(a.value1,a.value2,a.value3,a.value4) [BinCheckSum]
FROM @a a
請告訴我有沒有辦法創建一個 Binary_Checksum 作為列的默認值?
Please let me know is there any way to create a Binary_Checksum as a default value for the column?
推薦答案
基本上你不能.但是你可以用觸發器做類似的事情.見下文
Basically you can't. But you can do something similar with a trigger. See below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.tblVitalyTestUpdateInsertTrigger ON dbo.tblVitalyTest
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE
vt
SET
vt.BinCheckSum = BINARY_CHECKSUM(vt.value1, vt.value2, vt.value3,
vt.value4)
FROM
dbo.tblVitalyTest vt
INNER JOIN INSERTED i
ON vt.id = i.id
END
GO
這篇關于TSQL BINARY_CHECKSUM 作為默認值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！