MS SQL Server中需要輸入混合數據類型的使用者如何處理

我在 C# 應用程序中有一個表單，我無法使用綁定到查詢的數據表進行更改。使用者輸入學生任務的標記或“A”表示缺席。

但是，設計這個的人將底層表作為所有 varchars，實際上最好為標記使用小數，為“A”使用字元。為此，我想我會做一個而不是更新觸發器，將“A”值移動到缺勤表並將標記放入現有標記表中。然後我可以將標記表中的數據類型更改為十進制。

這是我的觸發程式碼：

INSTEAD OF UPDATE
AS 
BEGIN

SET NOCOUNT ON;

IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = '' OR (SELECT weekReviewWk1 FROM inserted) = NULL)
BEGIN

   UPDATE assessmentData
       SET assessmentData.weekReviewWk1 = NULL
       FROM inserted
       WHERE assessmentData.studentID = inserted.studentID
   UPDATE assessmentAbsences
       SET weekReviewWk1 = NULL
       FROM inserted
       WHERE assessmentAbsences.studentID = inserted.studentID
   RETURN
END 

IF (TRY_CONVERT(varchar,(SELECT weekReviewWk1 FROM inserted)) = 'A')
BEGIN
   UPDATE assessmentAbsences
       SET assessmentAbsences.weekReviewWk1 = inserted.weekReviewWk1
       FROM inserted
       WHERE assessmentAbsences.studentID = inserted.studentID
   UPDATE assessmentData
       SET weekReviewWk1 = NULL
       FROM inserted
       WHERE assessmentData.studentID = inserted.studentID
   RETURN
END

BEGIN

   UPDATE assessmentData
       SET weekReviewWk1 = TRY_CONVERT(decimal(4,1), (SELECT weekReviewWk1 FROM inserted))
       FROM inserted
       WHERE assessmentData.studentID = inserted.studentID
   UPDATE assessmentAbsences
       SET weekReviewWk1 = NULL
       FROM inserted
       WHERE assessmentAbsences.studentID = inserted.studentID
END
END

觸發器適用於十進制值，但是當我嘗試使用“A”更新表時出現“將數據類型 varchar 轉換為數字時出錯”。缺勤表具有 char 作為類型，值應該插入其中，實際標記表中沒有任何內容，因此不違反小數類型約束。為什麼會這樣？

另外，有沒有更好的方法來處理這種情況？

觸發器的存在INSTEAD OF INSERT不會阻止 SQL ServerINSERT針對目標列的數據類型驗證語句的數據類型。

錯誤資訊：

消息 245，級別 16，狀態 1，第 70

行將 varchar 值“A”轉換為數字數據類型時轉換失敗。

表示 SQL Server 知道它甚至不能嘗試將“A”插入數字欄位。那時觸發器甚至還沒有執行。

話雖如此，您可以使用觸發器將缺勤資訊放入另一個表中，您只是不能將列類型更改為數值。

這是一個例子：

USE tempdb;

/* drop the test tables, if they already exist */
IF OBJECT_ID('dbo.VarData') IS NOT NULL
DROP TABLE dbo.VarData;
IF OBJECT_ID('dbo.NumData') IS NOT NULL
DROP TABLE dbo.NumData;
GO

CREATE TABLE dbo.NumData
(
   NumDataID INT NOT NULL
       CONSTRAINT PK_NumData
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , Grade VARCHAR(30) NULL /* STILL a varchar value */
) ON [PRIMARY];

CREATE TABLE dbo.VarData
(
   VarDataID INT NOT NULL
       CONSTRAINT PK_VarData
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , NumDataID INT NOT NULL
       CONSTRAINT FK_VarData_NumDataID
       FOREIGN KEY
       REFERENCES dbo.NumData(NumDataID)
   , AbsentInd VARCHAR(1) NOT NULL
) ON [PRIMARY];
GO

/* drop-and-create the trigger */
IF OBJECT_ID('NumData_Insert',N'TR') IS NOT NULL
DROP TRIGGER NumData_Insert;
GO
CREATE TRIGGER NumData_Insert
ON dbo.NumData
INSTEAD OF INSERT
AS
BEGIN
   SET NOCOUNT ON;

   CREATE TABLE #NumDataInsert
   (
       NumDataID INT NOT NULL
   );
   /* only insert numeric values into the main table */
   INSERT INTO dbo.NumData (Grade)
   SELECT i.Grade
   FROM inserted i
   WHERE TRY_CONVERT(NUMERIC(4,2), i.Grade) IS NOT NULL;
   /* insert a NULL row into the main table, to obtain the NumDataID
       which allows us to match the Absentee rows to the associated row
       in the main table
    */
   INSERT INTO dbo.NumData (Grade)
   OUTPUT inserted.NumDataID INTO #NumDataInsert
   SELECT NULL
   FROM inserted i
   WHERE TRY_CONVERT(NUMERIC(4,2), i.Grade) IS NULL;

   /* insert any Absentee rows into the VarData table */
   INSERT INTO dbo.VarData (NumDataID, AbsentInd)
   SELECT ndi.NumDataID, 'Y'
   FROM #NumDataInsert ndi;

   SET NOCOUNT OFF;
END
GO

INSERT INTO dbo.NumData (Grade)
VALUES (97.2)
   , (42.0);

INSERT INTO dbo.NumData (Grade)
VALUES ('A');

SELECT *
FROM dbo.NumData;
SELECT *
FROM dbo.VarData;

上面的兩個SELECT語句產生這個輸出：

儘管這不允許您實際更改列的數據類型，但它確實允許您防止在該列中輸入不正確的數據。這將為您提供時間來修改程序中的程式碼，以便僅在表中輸入數值NumData，同時防止將新的“缺席”行插入其中。一旦您在客戶端程序中更改了程式碼，您就會ALTER將該列設置為numeric，並刪除觸發器。

下面的程式碼仍然失敗，因為 SQL Server 無法將字母值合併為數值：

/* these still fail, since 'A' cannot be converted to match the 
  datatype of the value, 97.2, which is numeric */
INSERT INTO dbo.NumData (Grade)
VALUES ('A')
   , (97.2);

INSERT INTO dbo.NumData (Grade)
VALUES (97.2)
   , ('A');

這將起作用，這可能是客戶端程序插入行的方式：

INSERT INTO dbo.NumData (Grade)
VALUES ('97.2')
   , ('A');

引用自：https://dba.stackexchange.com/questions/128496