Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block
IF @TRIGGER_DISABLED = 0 THEN
...trigger body
END IF;
In the mysql environment, you could
- run
SET @TRIGGER_DISABLED = 1;
- do your data maintenance
- run
SET @TRIGGER_DISABLED = 0;
So your trigger for table A should look like this:
BEGIN
IF @TRIGGER_DISABLED = 0 THEN
IF (OLD.status != 1 AND NEW.status = 2) THEN
IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN
DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
END IF;
ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN
IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
END IF;
ELSEIF (NEW.status != 3) THEN
IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN
DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id;
ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN
INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status);
ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN
UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id;
END IF;
END IF;
END IF;
END
So your trigger for table B should look like this:
CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
FOR EACH ROW
BEGIN
IF @TRIGGER_DISABLED = 0 THEN
CALL sp-set-comment_count(NEW.`gid`);
END IF;
END;
If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.
Best Answer
I did some testing on this and I think you'd be fine if you run your process in a single transaction.
In my testing, I only highlighted and executed the
BEGIN TRANSACTION
and theDISABLE TRIGGER
first. I then opened up a new (second) query window and tried to run various DML statements (SELECT
,INSERT
,UPDATE
DELETE
) against the base table. All attempts to access the base table in the second query window waited on the locks held by the window with the explicit transaction. Once I committed (or rolled back) my explicit transaction, the second window was able to access the table.