使用而不是触发器来转换数据类型(Using instead of trigger to convert data types)

使用而不是触发器来转换数据类型(Using instead of trigger to convert data types)

我有一个带有bigint列的表，我试图让插件工作。 我们一直遇到无法转换为数字的数据进入且插入失败的问题。 这主要是数据中的空格或行返回，即“123”，“1 365”。

由于我无法访问试图插入此错误数据的软件，因此我认为创建一个而不是触发器并使用函数去除所有非数字字符将解决问题。

这是触发器正在做什么的基本思路。

TRIGGER [dbo].[Delivery_Before_TRG] ON [dbo].[Delivery] instead of INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[Delivery] (....,[pin],....) select .... ,[dbo].[udf_GetNumericOnly](inserted.pin) ,.... from inserted; END

这是udf_GetNumberOnly函数。

FUNCTION [dbo].[udf_GetNumericOnly] ( @Value varchar(500) ) RETURNS bigint AS BEGIN Declare @Pos tinyint, @Char char(1) Set @Value = REPLACE(@Value, ' ', '') -- Strip all spaces Set @Pos = LEN(@Value) -- Give some non-zero value While @Pos > 0 Begin Set @Pos = PATINDEX('%[^0-9]%', @Value) ) If @Pos > 0 Begin Set @Char = SUBSTRING(@Value, @Pos, 1) -- Non numeric character Set @Value = REPLACE(@Value, @Char, '') End End Set @Value = RTrim(LTrim(@Value)) Return convert(bigint,@Value) END

我可以运行该函数，它将删除所有通过它的非数字字符，但是，当我尝试在我的表中运行insert语句时，我得到一个消息8114，级别16，状态5，行4错误转换数据类型varchar到bigint。 错误。

从我可以告诉的问题是，在我的触发器获取数据转换之前，sql server检查我尝试插入的字段是否与目标表列数据类型匹配。 我知道这是因为我修改了触发器以将数字直接插入引脚字段并仍然会出现此错误。

另外，我知道这不是函数失败，因为我可以编写一个失败的插入，然后更改该插入以调用该函数，它将起作用。

--This fails INSERT INTO (....,pin,...) VALUES(....,'1a23',....) --This works INSERT INTO (....,pin,...) VALUES(....,udf_GetNumericOnly('1a23'),....)

I have a table with a bigint column that I'm attempting to get an insert working for. We've been having issues where data that can't be converted to a numeric comes in and the insert fails. This is mostly things like spaces or line returns in the data i.e. " 123", "1 365".

Since I don't have access to the software that is attempting to insert this bad data, I thought that creating an instead of trigger and using a function to strip out all non-numeric characters would fix the issue.

This is a basic idea of what the trigger is doing.

TRIGGER [dbo].[Delivery_Before_TRG] ON [dbo].[Delivery] instead of INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO [dbo].[Delivery] (....,[pin],....) select .... ,[dbo].[udf_GetNumericOnly](inserted.pin) ,.... from inserted; END

And this is the udf_GetNumberOnly function.

FUNCTION [dbo].[udf_GetNumericOnly] ( @Value varchar(500) ) RETURNS bigint AS BEGIN Declare @Pos tinyint, @Char char(1) Set @Value = REPLACE(@Value, ' ', '') -- Strip all spaces Set @Pos = LEN(@Value) -- Give some non-zero value While @Pos > 0 Begin Set @Pos = PATINDEX('%[^0-9]%', @Value) ) If @Pos > 0 Begin Set @Char = SUBSTRING(@Value, @Pos, 1) -- Non numeric character Set @Value = REPLACE(@Value, @Char, '') End End Set @Value = RTrim(LTrim(@Value)) Return convert(bigint,@Value) END

I can run the function and it will strip all non-numeric characters for anything that I pass it, however, when I attempt to run an insert statement into my table I get a Msg 8114, Level 16, State 5, Line 4 Error converting data type varchar to bigint. error.

From what I can tell the problem is something to do with sql server checking that the fields I'm attempting to to insert match the destination table column datatypes before my trigger gets a hold of the data to convert it. I know this because I had modified the trigger to insert a number directly into the pin field and would still get this error.

Additionally, I know it isn't the function failing because I can write an insert that will fail and then change that insert to call the function, and it will work.

--This fails INSERT INTO (....,pin,...) VALUES(....,'1a23',....) --This works INSERT INTO (....,pin,...) VALUES(....,udf_GetNumericOnly('1a23'),....)

最满意答案

是的，在解析器确保您编写了有效的sql之后，algebrizer会立即查看查询中的数据类型。 INSTEAD OF INSERT触发器将为将要插入的每一行触发，无效的强制转换将不会触发。

Yeah, the algebrizer looks at the data types in your query right after the parser makes sure you've written valid sql. The INSTEAD OF INSERT trigger fires for each row that would be inserted, which an invalid cast wouldn't be.