Trigger exception handling

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.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Trigger exception handling

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: Trigger exception handling

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=3D-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@earthlink.net]=20
Sent: Friday, August 19, 2005 10:52 AM
To: Burke, Dan
Cc: mysql@lists.mysql.com
Subject: Re: Trigger exception handling


DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=3D23000;
INSERT INTO testhandler VALUES( NULL) ;



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=20
>and intended solely for the person or organization=20
>to whom it is addressed. It may contain privileged=20
>and confidential information and if you are not the=20
>intended recipient, you must not copy, distribute or=20
>take any action in reliance on it. If you have=20
>received this e-mail in error, please notify the=20
>sender as soon as possible and delete the e-mail=20
>message and any attachment(s).
>
>This message has been scanned for viruses=20
>by TechTeam's email gateway.
>
>
>
> =20
>


--=20
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=20
by TechTeam's email gateway.



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

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org

RE: Trigger exception handling

--=_alternative 006FBA6485257068_=
Content-Type: text/plain; charset="US-ASCII"

"Burke, Dan" 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.


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
--=_alternative 006FBA6485257068_=--

RE: Trigger exception handling

------_=_NextPart_001_01C5A9B5.E1099AB1
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable
X-NAIMIME-Disclaimer: 1
X-NAIMIME-Modified: 1

=20

Thank you for your response. I understand that stored procedures and
triggers are very much new to MySQL. In fact, this has been an eagerly
awaited feature by my management as a means to remove our dependence on
that budget eater (Oracle) (and myself for personal projects). I was
hoping that I just wasn't finding the right place in the documentation,
or that someone else had already encountered this requirement in MySQL
5.0.

=20

I will take that as confirmation that what we need is not (yet?) in
MySQL. With that in mind, I can see if I'm able to come up with a
workaround or if I need to shelve these particular lines of code
(comment them out in the MySQL version for now), and revisit it with
each beta release.

=20

On a side note, what they've done so far is great! This is something,
even in it's current functionality, that I see scores of use for. For
hopefully obvious reasons, I don't use Oracle for personal projects, so
triggers are a feature I've longed for in MySQL.

=20

Dan.

=20

________________________________

From: SGreen@unimin.com [mailto:SGreen@unimin.com]=20
Sent: Thursday, August 25, 2005 4:16 PM
To: Burke, Dan
Cc: mysql@lists.mysql.com
Subject: RE: Trigger exception handling

=20


> 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.
>=20
=20

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.=20

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.=20

Best Wishes,=20

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=20

________________________________

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



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

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


------_=_NextPart_001_01C5A9B5.E1099AB1--