帮助编写SQL Server触发器，在插入数据之前清除错误数据

Question

我们使用一个web服务，该服务决定将字段的最大长度从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 服务，因此我们也无法要求他们修复它。任何帮助都将不胜感激。

- Riggy

Answer 1

错误无法避免，因为错误是在插入的表填充时发生的。根据文档： http://msdn.microsoft.com/en-us/library/ms191300.aspx "插入和删除表的格式与定义INSTEAD OF触发器的表的格式相同。 插入表和删除表中的每个列直接映射到基本表中的一个列。" 我唯一真正“聪明”的想法是利用模式和登录使用的默认模式。 如果您可以使Web服务使用的登录引用另一个表，则可以增加该表上的列大小并使用INSTEAD OF 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

- Darryl Peterson

Answer 2

使用这个设置，我一切正常。 不想说废话，但你确定在测试时描述字段中有数据吗？他们可能会更改您插入的其他字段之一，也许其中一个字段引发了错误？

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

- etoisarobot

Answer 3

我无法在使用SQL 2008 R2时重现此问题：

Declare @table table ( fielda varchar(10) )

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

请确保您的字段确实定义为varchar（255）。

我还强烈建议您使用带有显式字段列表的插入语句。虽然您的插入语句在语法上是正确的，但您确实应该使用显式字段列表（就像我的示例中一样）。问题在于，当您不指定字段列表时，您完全取决于SQL和字段顺序的表定义。当您使用字段列表时，您可以更改表中字段的顺序（或在中间添加新字段），而不必担心插入语句。

- Jim

---

诚然，我只包含了最基本的代码（以及更改模式、表等）。实际插入符合您的建议。我已验证其余字段，除了这个字段外，我们都没问题。@Darryl能够给出为什么会发生这个错误的原因。 - Riggy