Description:
I have a front end srcipt that dynamically generates the trigger statement, depending on the existance of a varying number of tables/columns for any particular time. The data structure changes frequently, with the addition or deletion of some tables/columns.
This script is very good at dynamically capturing a snapshot of what everything that needs to cascade down to the subtables at the time the trigger is created. Since it is a fluid structure, tables that once existed when the trigger was built, may no longer exist and spring errors, like 1146.
The DECLARE CONTINUE HANDLER for these errors is not being recognized, which would make the trigger much more stable if it worked. The only fully reliable workaround is constantly dropping and recreating the trigger before performing any inserts or updates.
One may wonder why I break the insert/update statements up like that, and while it's irrelevant to the issue, this is the result of server-side scripting evaluating the existance of available fields to update at the time. If we can get the HANDLERS working properly, I intend to handle the exception of any column that may disappear between the time the trigger is created and when it is executed.
This bug report looks very similar to #14197 but mine is for a different OS and I would consider this to be a little more severe than was reported there.
How to repeat:
CREATE TABLE db.parenttable (with three fields: f1,f2,f3);
CREATE TABLE db.subtable2 (with three fields: f1,f2,f3);
CREATE TABLE db.subtable3 (with three fields: f1,f2,f3);
CREATE TRIGGER trg BEFORE INSERT ON parenttable FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR 1146 SET @x2 = 1;
SET @x2 = 0;
INSERT INTO subtable1 SET f1=NEW.f1;
IF @x2 = 0 THEN
UPDATE subtable1 SET f2=NEW.f2 WHERE f1=NEW.f1;
UPDATE subtable1 SET f3=NEW.f3 WHERE f1=NEW.f1;
END IF;
SET @x2 = 0;
INSERT INTO subtable2 SET f1=NEW.f1;
IF @x2 = 0 THEN
UPDATE subtable2 SET f2=NEW.f2 WHERE f1=NEW.f1;
UPDATE subtable SET f3=NEW.f3 WHERE f1=NEW.f1;
END IF;
END;
DROP TABLE db.subtable1
Suggested fix:
Make DECLARE Handlers work for triggers like they work for Stored Procedures.
