SQL Server INSTEAD OF INSERT 触发器失败

Question

我有两个表:

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
)

还有一个触发器:

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')

第二条语句抛出一个错误:

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

我希望触发器对 rejectedUsers 执行插入，有什么问题吗？

Answer 1

这是运行时错误，不是编译器错误。在运行时将类型与基表进行比较，而不管您可能在 instead of 触发器中编写的任何辅助逻辑如何。插入的伪表是基于基表创建的，而不是基于您传递给任意插入语句的变量类型。

我认为您无法通过简单地在它前面敲击而不是触发器来提供类型透明性。如果你想让人们编写临时查询，他们可以在其中抛出任何数据类型，你将需要首先在容纳垃圾的中间表中使用这些查询。或者做大多数人做的事情:让接口(interface)强制执行数据类型。如果您使用接受@age 参数作为 INT 的存储过程，则它们无法靠近带有“abc”的表......年龄也没有什么值(value)，因为它可能在明天或后天发生变化。为什么不接受生日呢？

最后，请不要将列或变量定义为没有长度的varchar/nvarchar。在某些情况下，这些是 1 个字符，在其他情况下是 30 个字符。要明确并告诉每个人你的真正意思。