Sample Header Ad - 728x90

Catch contraint error inside trigger on the table being inserted/updated

1 vote
0 answers
93 views
I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452) Finding an answer to that is not easy, I replies I could find were about catching an error on some statement executed by the trigger, while I'm looking to catch an error on the statement that fired the trigger. It could be that the constraint is checked before any trigger is called, but it would make sense if the before trigger was called. For lack of a better idea, I tried this silly test but of course it doesn't work.
CREATE TABLE test1 (
    id1 int PRIMARY KEY,
    val char(10)
) ENGINE =InnoDB;

CREATE TABLE test2 (
    id2 int PRIMARY KEY,
    id1 int,
    val char(10),
    FOREIGN KEY (id1) REFERENCES test1(id1)
) ENGINE =InnoDB;
DELIMITER $$
CREATE TRIGGER test2_before_insert
    BEFORE insert ON test2
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, sqlexception';
    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, sqlwarning';
    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, not found';
    -- Do the work of the trigger.
END $$
CREATE TRIGGER test2_after_insert
    AFTER insert ON test2
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, sqlexception';
    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, sqlwarning';
    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, not found';
    -- Do the work of the trigger.
END $$
DELIMITER ;
INSERT INTO test1 VALUES(1,'A');
INSERT INTO test2 VALUES(1,1,'A');
-- hoping it'll show the custom exception but it shows the normal one.
INSERT INTO test2 VALUES(2,2,'B');
DROP TABLE test2;
DROP TABLE test1;
Asked by David V. (111 rep)
Jun 23, 2020, 09:27 AM