帮助SQL Server触发器以在插入之前截断错误数据 [英] Help with SQL Server Trigger to truncate bad data before insert

问题描述

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

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.

这就是我开始的目的：

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 上插入时，错误：

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

字符串或二进制数据将被截断
。该语句已被
终止。

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

我尝试了 SET ANSI_WARNINGS OFF 允许查询工作，但随后根本没有在描述列中插入任何数据。

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.

推荐答案

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

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

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

插入和删除表的格式为与定义INSTEAD OF触发器的表的格式相同。插入和删除表中的每一列都直接映射到基表中的列。

"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."

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

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

这篇关于帮助SQL Server触发器以在插入之前截断错误数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！