INSTEAD OF INSERT trigger not working as expected in SQL Server

Data and Databases
1

I’m having trouble with an INSTEAD OF INSERT trigger in SQL Server. I’ve got two tables: employees for valid entries and invalid_employees for rejected ones. Here’s a simplified version of my setup:

CREATE TABLE employees (
    emp_id int PRIMARY KEY IDENTITY,
    first_name varchar(30),
    last_name varchar(30),
    years_of_service int
)

CREATE TABLE invalid_employees (
    entry_id int PRIMARY KEY IDENTITY,
    first_name nvarchar(30),
    last_name nvarchar(30),
    years_of_service nvarchar(30)
)

CREATE TRIGGER validate_service_years
ON employees
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO employees 
    SELECT first_name, last_name, years_of_service 
    FROM inserted 
    WHERE ISNUMERIC(years_of_service) = 1;

    INSERT INTO invalid_employees 
    SELECT first_name, last_name, years_of_service 
    FROM inserted 
    WHERE ISNUMERIC(years_of_service) = 0;
END

When I try to insert a record with non-numeric years_of_service, I get a conversion error instead of it being added to the invalid_employees table. What am I doing wrong? How can I make this trigger work correctly?

2

hey maya! i’m curious, have u tried using TRY_CONVERT or TRY_CAST in ur trigger? those functions might help handle non-numeric values without throwing errors. also, why not use a check constraint on the employees table? it could simplify things. what other approaches have u considered for validation?

3

yo maya, i think ur problem is in the employees table. years_of_service is an int, so sql’s tryin to convert non-numeric stuff automatically. maybe change it to varchar in both tables? then ur trigger should work fine. lemme know if that helps!

4

The issue in your trigger stems from a data type mismatch between the employees table and the inserted table. In SQL Server, the inserted table mimics the structure of the target table, so years_of_service remains an int. This causes the conversion error when trying to insert non-numeric values.

To resolve this, modify your trigger to handle the conversion explicitly:

CREATE TRIGGER validate_service_years
ON employees
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO employees 
    SELECT first_name, last_name, TRY_CONVERT(int, years_of_service)
    FROM inserted 
    WHERE TRY_CONVERT(int, years_of_service) IS NOT NULL;

    INSERT INTO invalid_employees 
    SELECT first_name, last_name, CONVERT(nvarchar(30), years_of_service)
    FROM inserted 
    WHERE TRY_CONVERT(int, years_of_service) IS NULL;
END

This approach utilizes TRY_CONVERT to safely attempt the conversion, ensuring that valid entries are inserted into the employees table while invalid ones are captured in the invalid_employees table.