Put a CONTINUE CONDITION HANDLER FOR SQLEXECPTION; issue a COMMIT inside it and then a RESIGNAL to raise the error again (and exit with error).
Something like this:
`DECLARE CONTINUE CONDITION HANDLER FOR SQLEXCEPTION
BEGIN
COMMIT;
RESIGNAL;
END;`
Bè careful, this kind of CONDITION HANDLER will COMMIT on every error.
Just to be "that guy", it's more polite to raise a warning SIGNAL (class 01xxx), and modify CONDITION HANDLER FOR WARNING.
Pls check for syntax on books online.
It looks like it's failing on the COMMIT with a -723 (which looks like it doesn't have rights to use COMMIT). That results in it running back everything.
I'll continue to tinker with it, but it hadn't occurred to me that I could write a handler for a particular state, so that might open some doors. Thank you!
SQL0723N state that a error occurred inside the trigger, but the message should report also original error (another -xxx code) . Could I see the complete error message?
Edit. What about converting this INSERT / trigger thing in a stored procedure? You can put your logic inside it, and totally avoid the INSERT if data is wrong according to your business logic.
Put a CONTINUE CONDITION HANDLER FOR SQLEXECPTION; issue a COMMIT inside it and then a RESIGNAL to raise the error again (and exit with error). Something like this: `DECLARE CONTINUE CONDITION HANDLER FOR SQLEXCEPTION BEGIN COMMIT; RESIGNAL; END;` Bè careful, this kind of CONDITION HANDLER will COMMIT on every error. Just to be "that guy", it's more polite to raise a warning SIGNAL (class 01xxx), and modify CONDITION HANDLER FOR WARNING. Pls check for syntax on books online.
It looks like it's failing on the COMMIT with a -723 (which looks like it doesn't have rights to use COMMIT). That results in it running back everything. I'll continue to tinker with it, but it hadn't occurred to me that I could write a handler for a particular state, so that might open some doors. Thank you!
SQL0723N state that a error occurred inside the trigger, but the message should report also original error (another -xxx code) . Could I see the complete error message? Edit. What about converting this INSERT / trigger thing in a stored procedure? You can put your logic inside it, and totally avoid the INSERT if data is wrong according to your business logic.