Getting an error converting from varchar to numeric

Question

I made a historical table along with a trigger function built in the reference table. Is based on possible name changes for an user and as well recording down the date.

My trigger built:

Target table:

The trigger function pulls the names off the table.

But I'm getting error converting data type. All my data that I'm updating are of VARCHAR type. Where am I missing?

Answer 1

Change the INSERT..VALUES statement to INSERT..SELECT and set aliases for all the columns. Make sure the aliases match the every column in the target table (defaulting all NOT NULL columns), and they are in the same order as they are declared. Note that the SELECT statement uses the patientLastNameChange table, because the MAX() without a GROUP BY ensures only one row is returned. I recommend to use COALESCE to set the MAX() result to 0 if it returns NULL. Then simply add 1 to increment the lastNameChangeID. I think this is more readable.

CREATE TRIGGER patientLastNameChangeTrigger
ON patient
AFTER UPDATE
AS
BEGIN
    DECLARE @oldLastName VARCHAR(255) = (SELECT pt_lastName FROM DELETED);
    DECLARE @newLastName VARCHAR(255) = (SELECT pt_lastName FROM INSERTED);

    IF @oldLastName <> @newLastName
        INSERT INTO dbo.patientLastNameChange (lastnameChangeID, patientID, oldLastName, newLastName, datechanged)
        SELECT
        COALESCE(MAX(plnc.lastnameChangeID),0)+1 AS lastnameChangeID,
        (SELECT patientID FROM INSERTED) AS patientID,
        @oldLastName AS oldLastName,
        @newLastName AS newLastName,
        GETDATE() AS datechanged
        FROM patientLastNameChange plnc;
END;