Understanding constraints and binding

[Nacho]

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

Nacho

[Erland Sommarskog]

Re: Understanding constraints and binding

Nacho (nacho.jorge@gm ail.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@sommarsk og.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

[Roy Harvey]

Re: Understanding constraints and binding

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@sommars kog.sewrote:

>Nacho (nacho.jorge@gm ail.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]

Re: Understanding constraints and binding

Roy Harvey (roy_harvey@sne t.net) writes:

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

Good point! Thanks, Roy!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Nacho]

Re: Understanding constraints and binding

Thanks to both of you!!!
It's good point to handle it in the query (I suposse when you read
values after) but then, how it would be? How can I clip values in the
select statement ?



Erland Sommarskog wrote:

Roy Harvey (roy_harvey@sne t.net) writes:

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

>
>
Good point! Thanks, Roy!
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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/prodinf...ons/books.mspx

[Erland Sommarskog]

Re: Understanding constraints and binding

Nacho (nacho.jorge@gm ail.com) writes:

Thanks to both of you!!!
It's good point to handle it in the query (I suposse when you read
values after) but then, how it would be? How can I clip values in the
select statement ?

With a CASE expression very similar to the one I had in my
UPDATE statement in my sample trigger.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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