How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server

Question

I have a form in a C# application that I can't change with a data table bound to a query. The user enters a mark for a student's task or 'A' for absent.

However, the person who designed this had the underlying table as all varchars, when really it would be nice to have decimals for marks and char for 'A'. To this end, I thought I would make an instead of update trigger that moves 'A' values to an absences table and puts marks in the existing marks table. I can then change the data type in my marks table to decimal.

This is my trigger code:

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

The trigger works for decimal values, however I get "Error converting data type varchar to numeric" when I attempt to update the table with 'A'. The absences table has char as the type and the value should just be inserted into that, with nothing in the actual marks table, thus not violating the decimal type constraint. Why is this happening?

In addition, is there a better way to handle this scenario?

Answer 1

The presence of an INSTEAD OF INSERT trigger does not prevent SQL Server from validating the data-types of the INSERT statement against the data-types of the target columns.

The error message:

Msg 245, Level 16, State 1, Line 70
Conversion failed when converting the varchar value 'A' to data type numeric.

indicates that SQL Server knows it can't even try to insert 'A' into the numeric field. The trigger hasn't even ran at that point.

Having said that, you can use a trigger to put the absentee info into a another table, you just can't change the column type to a numeric value.

Here is an example:

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;

The two SELECT statements above produce this output:

Even though this does not allow you to actually change the data-type of the column, it does allow you to prevent entering incorrect data into that column. This will provide time for you to modify the code in the program to only enter numeric values into the NumData table, while preventing new "Absentee" rows being inserted into it. Once you get the code changed in the client program, you'd ALTER the column to be numeric, and remove the trigger.

The below code still fails since SQL Server cannot coalesce alphabetic values into numeric values:

/* 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');

This will work, which is likely how the client program is inserting rows:

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

Answer 2

If you can't change the form and its bound DataTable, then won't it still be looking in assessmentData.weekReviewWk1 for the "A"s to display that info? And possibly other parts of the app code? How will the app (with code you can't change) know to look in the other location for the "A"s?

Assuming that this isn't an issue, then depending on the circumstances, a potential alternative to @Max's fine solution would to be come at it from the opposite direction: instead of moving the "A"s to another location, why not move the grades to another location? You could simply create a new column in this table, maybe call it RealGradezYo, make it DECIMAL(5, 2), and then you can create an AFTER INSERT, UPDATE Trigger (no need to mess with an INSTEAD OF Trigger) to do something like:

UPDATE ad
SET    ad.RealGradezYo = CONVERT(NUMERIC(5,2), ad.weekReviewWk1)
       ad.weekReviewWk1 = NULL
FROM   assessmentData ad
INNER JOIN inserted ins
        ON ins.studentID = ad.studentID
WHERE  TRY_CONVERT(NUMERIC(5,2), ad.Grade) IS NOT NULL;

---

P.S. I would highly recommend getting out of the habit of writing an UPDATE statement that includes additional tables in the form of UPDATE tbl_to_update SET ... FROM joined_table WHERE join_condition. The potential problem is if you somehow get that JOIN condition wrong, or forget it entirely. I have seen it happen and its not always easy to track down. And, I just find it to be more readable. Hence the following query from the question:

UPDATE assessmentData
    SET assessmentData.weekReviewWk1 = NULL
    FROM inserted
    WHERE assessmentData.studentID = inserted.studentID

would be better off written as:

UPDATE ad
SET    ad.weekReviewWk1 = NULL
FROM   assessmentData ad
INNER JOIN inserted ins
WHERE  ins.studentID = ad.studentID;