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

问题描述

我们使用了一个 Web 服务，该服务决定将字段的最大长度从 255 更改.我们这边有一个旧的供应商表，该表的上限仍为 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.

有没有办法使用触发器来截断过长的数据，或者在设计出更雄辩的解决方案之前，我可以使用其他替代方法吗?我们在表修改方面相当有限(即我们不能)，因为它是一个供应商表，而且我们不控制我们正在使用的 Web 服务，所以我们也不能要求他们修复它.任何帮助将不胜感激.

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/en-us/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屋！