SEO

#1 (**permalink**) 02-28-2008, 10:29 AM

Lets say I have a table:

create table test(
ID int unsigned default 0);

If someone was to update the table with the following statment:
UPDATE test SET ID = ID - 1

And the ID in the field was 0, this would cause a large numeric value
because it's an unsigned int,
is there a way to prevent this from happening, in the form of some trigger
or so.

My goal is not to have these values in the table, if the result is going to
cause invaild data, I want to somehow
prevent this.
Thanks
-Paul C

#2 (**permalink**) 02-28-2008, 10:29 AM

..oO(nopam_pcartier@atlashosting.com)

>Lets say I have a table:
>
>create table test(
> ID int unsigned default 0);
>
>
>
>If someone was to update the table with the following statment:
>UPDATE test SET ID = ID - 1
>
>And the ID in the field was 0, this would cause a large numeric value
>because it's an unsigned int,
>is there a way to prevent this from happening, in the form of some trigger
>or so.

Your application should catch that, not the DB. Don't let your users
submit such values.

Micha

#3 (**permalink**) 02-28-2008, 10:29 AM

On Oct 31, 1:38 pm, nopam_pcart...@atlashosting.com wrote:
> Lets say I have a table:
>
> create table test(
> ID int unsigned default 0);
>
> If someone was to update the table with the following statment:
> UPDATE test SET ID = ID - 1
>
> And the ID in the field was 0, this would cause a large numeric value
> because it's an unsigned int,
> is there a way to prevent this from happening, in the form of some trigger
> or so.
>
> My goal is not to have these values in the table, if the result is going to
> cause invaild data, I want to somehow
> prevent this.
> Thanks
> -Paul C

Michael Fesser is correct.

However, I am wondering what would happen if you set ID to NULL
instead of 0. If someone then tried this, UPDATE test SET ID = ID - 1,
would that not generate an invalid data type error from the DB?

#4 (**permalink**) 02-28-2008, 10:29 AM

I created a trigger, and that seemed to work, here it is below:

CREATE TRIGGER t_UpdateMemberStats BEFORE UPDATE ON member_stats
FOR EACH ROW BEGIN
IF NEW.news_comments < 0 THEN SET NEW.news_comments = 0; END IF;
IF NEW.forum_comments < 0 THEN SET NEW.forum_comments = 0; END IF;
END;

This seem to prevent negative values

-Paul

#5 (**permalink**) 02-28-2008, 10:29 AM

On Oct 31, 8:22 pm, Michael Fesser <neti...@gmx.de> wrote:
>
> Your application should catch that, not the DB. Don't let your users
> submit such values.
>

Indeed it should, but I would never rely on the UI alone to enforce
data integrity. Unfortunately I don't think MySQL has check
constraints. Triggers are probably the next best thing.

#6 (**permalink**) 02-28-2008, 10:29 AM

Trigger was the way to go, works perfect!

Thanks for the input.

CREATE TRIGGER t_UpdateMemberStats BEFORE UPDATE ON members.member_stats
FOR EACH ROW BEGIN
IF NEW.news_comments < 0 THEN SET NEW.news_comments = 0; END IF;
IF NEW.photo_comments < 0 THEN SET NEW.photo_comments = 0; END IF;
END;

-Paul

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Preventing Negative values in table.