GilaMonster (1/31/2012)
jcb (1/31/2012)
Can You rely on a trigger to safe truncate the data to the column?No, because types are checked before triggers fire.
Yes, if you use a view 😉 ...
CREATE TABLE dbo.Test
(
String varchar(245) NOT NULL
);
GO
CREATE VIEW dbo.TestView
WITH SCHEMABINDING AS
SELECT
String = CONVERT(varchar(8000), String)
FROM dbo.Test AS t;
GO
CREATE TRIGGER [trg TestView IOI]
ON dbo.TestView
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.Test (String)
SELECT
LEFT(String, 245)
FROM INSERTED;
END
-- Success
INSERT dbo.TestView (String) VALUES(REPLICATE('X', 250));
GO
SELECT
t.String,
DataSize = DATALENGTH(t.String)
FROM dbo.Test AS t
GO
DROP VIEW dbo.TestView;
DROP TABLE dbo.Test;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi