帮助 SQL Server 触发器在插入之前截断坏数据

发布于 2024-11-09 17:14:26 字数 722 浏览 0 评论 0原文

我们使用一个 Web 服务，该服务决定将字段的最大长度从 255 更改为 255。我们这边有一个旧的供应商表，其上限仍然为 255。我们希望使用触发器来暂时解决这个问题，直到我们可以实现在我们的下一次迭代中，将提供更加商业友好的解决方案。

这就是我的开始：

CREATE TRIGGER [mySchema].[TruncDescription] 
ON  [mySchema].[myTable] 
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT INTO [mySchema].[myTable]
SELECT SubType, type, substring(description, 1, 255)
FROM inserted
END

但是，当我尝试在 myTable 上插入时，我收到错误：

字符串或二进制数据将是被截断。该声明已终止。

我尝试使用 SET ANSI_WARNINGS OFF 进行试验，这允许查询工作，但只是没有将任何数据插入到描述列中。

有什么方法可以使用触发器来截断太长的数据，或者在设计出更雄辩的解决方案之前我可以使用其他替代方案吗？我们对表的修改相当有限（即我们不能），因为它是供应商表，并且我们无法控制我们正在使用的 Web 服务，因此我们也不能要求他们修复它。任何帮助将不胜感激。

原文

We consume a web service that decided to alter the max length of a field from 255. We have a legacy vendor table on our end that is still capped at 255. We are hoping to use a trigger to address this issue temporarily until we can implement a more business-friendly solution in our next iteration.

Here's what I started with:

CREATE TRIGGER [mySchema].[TruncDescription] 
ON  [mySchema].[myTable] 
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT INTO [mySchema].[myTable]
SELECT SubType, type, substring(description, 1, 255)
FROM inserted
END

However, when I try to insert on myTable, I get the error:

String or binary data would be
truncated. The statement has been
terminated.

I tried experimenting with SET ANSI_WARNINGS OFF which allowed the query to work but then simply didn't insert any data into the description column.

Is there any way to use a trigger to truncate the too-long data or is there another alternative that I can use until a more eloquent solution can be designed? We are fairly limited in table modifications (i.e. we can't) because it's a vendor table, and we don't control the web service we're consuming so we can't ask them to fix it either. Any help would be appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三生一梦 2024-11-16 17:14:26

该错误无法避免，因为错误是在填充插入的表时发生的。

从文档中：
http://msdn.microsoft.com/en-us/library/ms191300.aspx

“inserted 和deleted 表的格式与定义INSTEAD OF 触发器的表的格式相同。inserted 和deleted 表中的每一列都直接映射到基表中的列。”

我能想到的唯一真正“聪明”的想法是利用架构和登录使用的默认架构。如果您可以获得 Web 服务用于引用另一个表的登录名，则可以增加该表上的列大小并使用 INSTEAD OF INSERT 触发器对供应商表执行 INSERT。其变体是在不同的数据库中创建表并设置 Web 服务登录的默认数据库。

CREATE TRIGGER [myDB].[mySchema].[TruncDescription] 
ON  [myDB].[mySchema].[myTable] 
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT INTO [VendorDB].[VendorSchema].[VendorTable]
SELECT SubType, type, substring(description, 1, 255)
FROM inserted
END

The error cannot be avoided because the error is happening when the inserted table is populated.

From the documentation:
http://msdn.microsoft.com/en-us/library/ms191300.aspx

"The format of the inserted and deleted tables is the same as the format of the table on which the INSTEAD OF trigger is defined. Each column in the inserted and deleted tables maps directly to a column in the base table."

The only really "clever" idea I can think of is to take advantage of schemas and the default schema used by a login. If you can get the login that the web service is using to reference another table, you can increase the column size on that table and use the INSTEAD OF INSERT trigger to perform the INSERT into the vendor table. A variation of this is to create the table in a different database and set the default database for the web service login.

CREATE TRIGGER [myDB].[mySchema].[TruncDescription] 
ON  [myDB].[mySchema].[myTable] 
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;

INSERT INTO [VendorDB].[VendorSchema].[VendorTable]
SELECT SubType, type, substring(description, 1, 255)
FROM inserted
END

回复收藏 0 原文

久随 2024-11-16 17:14:26

通过这个设置，一切对我来说都正常。
不是说显而易见的事情，但你确定测试时描述字段中有数据吗？他们也有可能更改您正在插入的其他字段之一，并且其中之一可能引发错误？

CREATE TABLE [dbo].[DataPlay](
    [Data] [nvarchar](255) NULL
) ON [PRIMARY]
GO

和这样的触发器

Create TRIGGER updT ON  DataPlay 
Instead of Insert 
AS 
BEGIN
    SET NOCOUNT ON;     
INSERT INTO [tempdb].[dbo].[DataPlay]
           ([Data])
           (Select substring(Data, 1, 255) from inserted)
END
GO

然后插入

Declare @d as nvarchar(max)
Select @d = REPLICATE('a', 500)
SET ANSI_WARNINGS OFF
INSERT INTO [tempdb].[dbo].[DataPlay]
           ([Data])
     VALUES
           (@d)
GO

With this setup everything works OK for me.
Not to state the obvious but are you sure there is data in the description field when you are testing? It is possible they change one of the other fields you are inserting as well and maybe one of those is throwing the error?

CREATE TABLE [dbo].[DataPlay](
    [Data] [nvarchar](255) NULL
) ON [PRIMARY]
GO

and a trigger like this

Create TRIGGER updT ON  DataPlay 
Instead of Insert 
AS 
BEGIN
    SET NOCOUNT ON;     
INSERT INTO [tempdb].[dbo].[DataPlay]
           ([Data])
           (Select substring(Data, 1, 255) from inserted)
END
GO

then inserting with

Declare @d as nvarchar(max)
Select @d = REPLICATE('a', 500)
SET ANSI_WARNINGS OFF
INSERT INTO [tempdb].[dbo].[DataPlay]
           ([Data])
     VALUES
           (@d)
GO

回复收藏 0 原文

烟若柳尘 2024-11-16 17:14:26

我无法使用以下方法在 SQL 2008 R2 上重现此问题：

Declare @table table ( fielda varchar(10) )

Insert  Into @table ( fielda )
Values  ( Substring('12345678901234567890', 1, 10) )

请确保您的字段确实定义为 varchar(255)。

我还强烈建议您使用带有显式字段列表的 Insert 语句。虽然您的插入在语法上是正确的，但您确实应该使用显式字段列表（如我的示例中所示）。问题是，当您不指定字段列表时，您将受到 SQL 和字段顺序表定义的支配。当您确实使用字段列表时，您可以更改表中字段的顺序（或在中间添加新字段），而不必关心插入语句。

I am unable to reproduce this issue on SQL 2008 R2 using:

Declare @table table ( fielda varchar(10) )

Insert  Into @table ( fielda )
Values  ( Substring('12345678901234567890', 1, 10) )

Please make sure that your field is really defined as varchar(255).

I also strongly suggest you use an Insert statement with an explicit field list. While your Insert is syntactically correct, you really should be using an explicit field list (like in my sample). The problem is when you don't specify a field list you are at the mercy of SQL and the table definition for the field order. When you do use a field list you can change the order of the fields in the table (or add new fields in the middle) and not care about your insert statements.

回复收藏 0 原文

~没有更多了~