Trigger

[Prasawet]

I use MySQL 5.1 for Win and develop client app.

I want to create BEFORE UPDATE trigger with this conditions:
-If the value wich is coming is, let say less then 0 then can not be accepted for store in the field on that row.
-to generate some err code or to initialize some condition to produce me info on the client side, by the other words-to gime mi info about it.

Thank's!

[Atli]

Hi.

That sounds like fun, but I fail to see a question.
What exactly is it that you are having problems with?

We are more than happy to help you out, but you are going to have to try this yourself first. We won't do the work for you.

I suggest you check out the manual page on Trigger syntax and go from there.

Let us know if you have any specific problems we can help you get through.

[Prasawet]

Fair enough, perhaps my Q. was misty so here is again:
(before update)

Now I use this:

BEGIN
IF NEW.thnum =0 THEN
SET NEW.thnum=null;
END IF; 
END

and this generate err in my client app because field thnum can not be null. By other words, my intetion is to get some err to warn user that 0 is forbiden and this is the only way I find to do it.
I want, instead "SET NEW.thnum=null;" to find the way to IGNORE update but I can't find it.
MySql 5.xxx don't support warnings (raise error) by now so I must use workarround.

[Atli]

Ahh ok I see. So the problem is getting an error message from you trigger to your client application.

Technically, that isn't possible. As you say, MySQL 5.x doesn't support any sort of RaisError functionality like many of the other SQL servers do.

But to get around that, you could always do something you know will cause an error, and make that error include your message.

For example, if I did this:

CREATE TRIGGER myTable_BeforeInsert
BEFORE INSERT ON myTable
FOR EACH ROW
BEGIN
    IF NEW.value <= 0 THEN
        CALL `error`.`Value can not be zero or less.error`();
    END IF;
END;
 

It would produce this error:

ERROR 1305 (42000): PROCEDURE error.Value can not be zero or less.error does not exist

This includes my message between the "error." and ".error" strings, which can easily be extracted and used.

It's not pretty, but it gets the job done :)