Posted by Roy Harvey on 08/07/06 12:38

I would only add that a WHERE clause on the UPDATE in the trigger
would save updates when the value is already within the range:

WHERE t.col < 0 OR T.col > 10000

Roy Harvey
Beacon Falls, CT

On Mon, 7 Aug 2006 11:06:31 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>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.

[Back to original message]