List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 19 2005 2:51pm
Subject:Re: Trigger exception handling
View as plain text  
Dan,

See http://dev.mysql.com/doc/mysql/en/declare-handlers.html. Here's a 
wee example...

USE test ;
SET @err = 0 ;
SELECT 'Before running errhandlerdemo:', @err ;
CREATE TABLE IF NOT EXISTS testhandler (i INT, PRIMARY KEY(i)) ;

SET GLOBAL log_bin_trust_routine_creators=TRUE
DROP PROCEDURE IF EXISTS errhandlerdemo ;
DELIMITER |
CREATE PROCEDURE errhandlerdemo()
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
    INSERT INTO testhandler VALUES( NULL) ;
    SET @err=-1 ;
  END ;
|
DELIMITER ;
CALL errhandlerdemo();
DROP TABLE testhandler;
SELECT 'After running errhandlerdemo:', @err;

PB
http://www.artfulsoftware.com

-----


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

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