SQL Server INSTEAD OF INSERT trigger failure

Question

I have two tables:

CREATE TABLE users
(
    id int PRIMARY KEY IDENTITY,
    firstname varchar(30),
    lastname varchar(30),
    age int
)

CREATE TABLE rejectedUsers
(
    id int PRIMARY KEY IDENTITY,
    firstname nvarchar,
    lastname nvarchar,
    age nvarchar
)

And a trigger:

CREATE TRIGGER checkNumeric
ON users
INSTEAD OF INSERT
AS
INSERT INTO users SELECT firstname, lastname, age FROM inserted WHERE ISNUMERIC(age) = 1;
INSERT INTO rejectedUsers SELECT firstname, lastname, age FROM inserted WHERE ISNUMERIC(age) = 0;

-- goes fine
INSERT INTO users
VALUES
('Vlad', 'P', 21)

-- fails

INSERT INTO users
VALUES
('Chuck', 'Norris', 'abc')

The second statement throws me an error:

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

I expect the trigger to perform an insert on rejectedUsers, what's wrong?

Answer 1

This is a run-time error, not a compiler error. Types are compared against the base table at run-time, irrespective of any helper logic you may have coded in an instead of trigger. The inserted pseudo-table is created based on the base table, not on the types of the variables you pass to some arbitrary insert statement.

I don't think you're going to be able to provide type transparency by simply slapping an instead of trigger in front of it. If you want to allow people to write ad-hoc queries where they can throw any data types in there, you're going to need to consume those in an intermediate table first that accommodates the junk. Or do what most people do: have the interface enforce the data type. If you use a stored procedure that accepts an @age parameter as an INT, they can't get anywhere near the table with 'abc'... also age is of little value because it may change tomorrow or the next day. Why not accept a birthday instead?

Finally, please don't define columns or variables as varchar/nvarchar without length. In some cases these are 1-character, in others they are 30. Be explicit and tell everyone what you really mean.

Answer 2

Your code here:

INSERT INTO users
VALUES('Chuck', 'Norris', 'abc')

will try to insert abc into the column age which is of type int - is that really an int?? I believe even though you have an INSTEAD OF INSERT trigger in place, that Inserted table that lives inside the trigger will have the same structure as your Users table - and that column age is an INT column and can't deal with abc as a value!

As a general recommendation, I would always explicitly specify which columns you mean in your inserts - so I'd write the above query as:

INSERT INTO users(firstname, lastname, age)
VALUES('Chuck', 'Norris', 71)

and I'd change this:

INSERT INTO users 
    SELECT firstname, lastname, age 
    FROM inserted 
    WHERE ISNUMERIC(age) = 1;

to this:

INSERT INTO users(firstname, lastname, age)
    SELECT firstname, lastname, age 
    FROM inserted 
    WHERE ISNUMERIC(age) = 1;

That way, you won't have any nasty surprises if some table gets modified and suddenly gets a new column that your "generic" INSERT doesn't fill in...

As a side note: I hope you're aware that by specifying this:

CREATE TABLE rejectedUsers
(
    id int PRIMARY KEY IDENTITY,
    firstname nvarchar,
    lastname nvarchar,
    age nvarchar
)

you're effectively creating a table with a lot of nvarchar(1) columns that can hold a whopping maximum of 1 character each! You also should always provide explicit lengths for your varchar and nvarchar columns.....