Increase decimal point rounding error

Question

I have a column [BRKTFRT1] [decimal](7, 4 ) in the @MyTable table.

When I try to insert the value 34.1234555 , it successfully inserts 34.1235 into the column. In fact, I need to prevent this rounding and cause an error if such rounding becomes necessary. How can we add such a restriction?

Note. One idea is to check the length of the input string. But I would like to add it as a CHECK restriction so that developers do not miss this restriction.

Note. I am using SQL Server 2012

Note. I am not trying to "avoid" rounding. I need to tell the caller that they set the wrong value (by raising the error).

 DECLARE @MyTable TABLE (BRKTFRT1 [decimal](7, 4) ) DECLARE @MyStringDecimal VARCHAR(20) SET @MyStringDecimal = '34.1234555' INSERT INTO @MyTable (BRKTFRT1) VALUES (@MyStringDecimal) SELECT BRKTFRT1 FROM @MyTable 

Answer 1

See Set NUMERIC_ROUNDABORT

When SET NUMERIC_ROUNDABORT is turned on, an error occurs after loss of precision in the expression. [...] Loss of accuracy occurs when an attempt is made to save a value with a fixed accuracy in a column or variable with less accuracy