List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 25 2005 8:16pm
Subject:RE: Trigger exception handling
View as plain text  
"Burke, Dan" <dburke@stripped> 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
Thread
Trigger exception handlingDan Burke18 Aug
  • Re: Trigger exception handlingPeter Brawley19 Aug
RE: Trigger exception handlingDan Burke25 Aug
  • RE: Trigger exception handlingSGreen25 Aug
RE: Trigger exception handlingDan Burke25 Aug