List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 7 2005 4:10pm
Subject:Re: Generating an Error in a Trigger
View as plain text  
Rob,

 >How do I generate an error condition within a Trigger?

 >This might sound a strange request to some but I'm trying to do input
 >validation on an insert/update query using a trigger. If the 
validation fails
 >I need to set an error condition such that MySQL will not 
Insert/Ammend the
 >data. Whilst I could force 'sensible' defaults in the event of validation
 >failure I'd prefer to dump the entire record as being 'out of bounds'. 
The
 >manual states that when an error occurs within a trigger the table 
data isn't
 >modified which is exactly the behaviour I want. Unfortunately the manual
 >doesn't state how to generate an error (safely).

Much of what is allowed within BEGIN ...END blocks in sprocs and sfuncs 
is also allowed in such blocks in triggers, so you can write something 
like ...

FOR EACH ROW BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '<some_value>' SET @err=<some_value>;
    -- validation statements ...
    SET @err=-1 ;
END

PB

-----

Rob Hall wrote:

>How do I generate an error condition within a Trigger? 
>
>This might sound a strange request to some but I'm trying to do input 
>validation on an insert/update query using a trigger. If the validation fails 
>I need to set an error condition such that MySQL will not Insert/Ammend the 
>data. Whilst I could force 'sensible' defaults in the event of validation 
>failure I'd prefer to dump the entire record as being 'out of bounds'. The 
>manual states that when an error occurs within a trigger the table data isn't 
>modified which is exactly the behaviour I want. Unfortunately the manual 
>doesn't state how to generate an error (safely). 
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 10/6/2005

Thread
Generating an Error in a TriggerRob Hall7 Oct
  • Re: Generating an Error in a TriggerPeter Brawley7 Oct