List:General Discussion« Previous MessageNext Message »
From:Dan Burke Date:August 25 2005 7:42pm
Subject:RE: Trigger exception handling
View as plain text  
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.


-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Friday, August 19, 2005 10:52 AM
To: Burke, Dan
Cc: mysql@stripped
Subject: Re: Trigger exception handling

<snip>
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
    INSERT INTO testhandler VALUES( NULL) ;
<snip>


Burke, Dan wrote:

>Hello,
>
>I've been looking into converting our existing Oracle PL/SQL code to
mysql.  A lot of the syntax is pretty straight forward, and really
doesn't require much change from what I've been testing with thus far.
However, I'm trying to handle exceptions, and I cannot seem to find any
documentation that shows me what I'm looking for.
>
>In oracle, we have bits of code like this:
>
>        IF INSTR(:new.ACCOUNT_NUM, ' ') > 0 THEN
>                RAISE AcctNumHasSpace;
>        END IF;
>
>and then
>
>        EXCEPTION
>                WHEN AcctNumHasSpace THEN
>                        RAISE_APPLICATION_ERROR(-20001, 'Cannot insert
space into ACCOUNT_NUM');
>
>But I cannot seem to figure out how to replicate that behavior.  Is it
something that's possible to do as of yet?  Or not really?  Basically in
this situation, we want the insert to fail if there is a space in that
field.
>
>Any advice would be great.
>
>Dan.
>
>
>_________________________________________________
>This e-mail transmission is strictly confidential 
>and intended solely for the person or organization 
>to whom it is addressed. It may contain privileged 
>and confidential information and if you are not the 
>intended recipient, you must not copy, distribute or 
>take any action in reliance on it. If you have 
>received this e-mail in error, please notify the 
>sender as soon as possible and delete the e-mail 
>message and any attachment(s).
>
>This message has been scanned for viruses 
>by TechTeam's email gateway.
>
>
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.13/78 - Release Date:
8/19/2005



_______________________________________________________
This message has been scanned for viruses 
by TechTeam's email gateway.



_________________________________________________
This e-mail transmission is strictly confidential 
and intended solely for the person or organization 
to whom it is addressed. It may contain privileged 
and confidential information and if you are not the 
intended recipient, you must not copy, distribute or 
take any action in reliance on it. If you have 
received this e-mail in error, please notify the 
sender as soon as possible and delete the e-mail 
message and any attachment(s).

This message has been scanned for viruses 
by TechTeam's email gateway.


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