|
|
Posted by Erland Sommarskog on 08/07/06 11:06
Nacho (nacho.jorge@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|