Help with SQL Server Trigger to truncate bad data before insert

Question

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.

Answer 1

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