Nacho (na*********@gmail.com) writes:
I'm implementing some database formatting and I need that values within
a column have certain limits ... let's say for example, they shouldn't
be <0 or >10000, but in the case I'm inserting values bigger then 10000
I would like that MSSQL "clip" this value to the upper limit (10000 in
this case) and the same with the lower limit (zero in this case).
Is that possible? or SQL just respond me with an error when the values
go beyond those limits and will abort the transaction?
Can someone put some light on this please???
You would need a trigger:
CREATE TRIGGER tri ON tbl FOR INSERT, UPDATE AS
UPDATE tbl
SET col = CASE WHEN t.col < 0 THEN 0
WHEN t.col 10000 THEN 0
ELSE t.col
END
FROM tbl t
JOIN inserted i ON t.keycol = i.keycol
If you have a constraint, you would have to drop that constraint. Or
implement an INSTEAD OF trigger instead.
I would question the wise in destroying data in this way, though. Better
would be to accept the data as-is, and then handle it in the query.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx