"Burke, Dan" <[EMAIL PROTECTED]> wrote on 08/25/2005 03:42:18 PM:
> I don't think this is really what I'm looking for.
>
> What I need is to be able to perform certain validation on the data from
> within the trigger. If that validation fails, then I need the trigger
> to abort with an error. The handling below seems to just handle if
> there's a SQL error. I need to somehow create my own error condition.
>
> I'm almost looking for something like this I guess:
>
> Create trigger ....
> ......
> DECLARE EXIT HANDLER FOR SQLSTATE 'CustomError' SET @err=-500;
> ......
> If INSTR(new.ACCOUNT_NUM, ' ') > 0
> Then
> Throw error CustomError;
> End if;
> ...
>
>
> insert into accounts (ACCOUNT_NUM) values ('123 456');
> ... insert should fail in this instance, irrespective of any other
> constraints like not-null/unique fields, etc.
>
> There's other fields/tables that validation is performed on from within
> the current PL/SQL triggers, this is just the most basic example. I'm
> getting the feeling I'm pretty rare in this situation.
>
> Thanks,
> Dan.
<snip>
No, what you want to do isn't "rare", it's "new" to MySQL. Stored
procedures and triggers are both new to MySQL 5.0 and may not be mature
enough to do what you are asking of them, yet. It took several point
updates to the beta code for triggers to even be able to work with other
tables. As was mentioned before, there is not yet a "raise error"
equivalent in the procedural SQL of MySQL. This, too, is new for 5.0.
I look at it this way, you are one of the pioneering users in this area.
What you discover and work out will benefit the rest of us and will
demonstrate to the developers just how much they have left to do to make
triggers a mature and useful feature.
Best Wishes,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
