Geek asks and answers

Help with SQL Server Trigger to truncate bad data before inserting

We are using a web service that has decided to change the maximum field length from 255. We have an outdated vendor table at our end, which is still limited to 255. We hope to use a trigger to solve this problem temporarily until we can implement more a business-friendly solution in our next iteration.

Here is 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 paste on myTable, I get an error:

String or binary data will be truncated. The application was discontinued.

I tried to experiment with SET ANSI_WARNINGS OFF, which allowed the request to work, but then just did not insert any data into the description column.

, , ? (.. ), , -, , . .

+3
3

, .

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

" , INSTEAD OF. ."

"" , , - , . , - , INSTEAD OF INSERT INSERT . -.

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
+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
+2

SQL 2008 R2, :

Declare @table table ( fielda varchar(10) )

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

, , varchar (255).

I also strongly suggest that you use the Insert statement with an explicit list of fields. Although your insert is syntactically correct, you really should use an explicit list of fields (as in my example). The problem is that you are not specifying a list of fields that you spare SQL and a table definition for field order. When you do , use the list of fields, you can change the order of the fields in the table (or add new fields in the middle) and not worry about your insert statements.

+1
source

More articles:


All Articles