How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server
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');