MySQL: Rollback Insert through BEFORE Triggers

marcelocbf
marcelocbf used Ask the Experts™
on
Hi,

I'd like to rollback a Insert statement is my check in the BEFORE INSERT trigger don't satisfy a condition ...

I read somewhere that any exception during the trigger would prevent the insert, but I don't know how throw this exceptions ...

Can someone help me ?

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
raise_application_error(-10001,'Error in Insert');
Commented:
Sorry, I was using a UDF.. U can use a stored procdedure

DROP PROCEDURE IF EXISTS `MyRaiseError`$$

CREATE PROCEDURE `MyRaiseError`(msg VARCHAR(62))
BEGIN
DECLARE Tmsg VARCHAR(80);
SET Tmsg = msg;
IF (CHAR_LENGTH(TRIM(Tmsg)) = 0 OR Tmsg IS NULL) THEN
SET Tmsg = 'ERROR GENERADO';
END IF;
SET Tmsg = CONCAT('@@MyError', Tmsg, '@@MyError');
SET @MyError = CONCAT('INSERT INTO', Tmsg);
PREPARE stmt FROM @MyError;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

Usage:
call MyRaiseError('Here error message!');

Author

Commented:
Sorry for the delay ...
I'll study your solution ... Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today