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.
It's not pretty, but this gets the possible enum values from INFORMATION_SCHEMA.COLUMNS
.
DELIMITER $$
DROP TRIGGER ins_addresses $$
CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN
DECLARE column_list char(200);
DECLARE current_value char(200);
DECLARE counter INT DEFAULT 0;
DECLARE num_enums INT;
/* Format goes from "enum('shop1','shop2')" to "shop1,shop2" */
SELECT REPLACE(SUBSTR(COLUMN_TYPE,6,LENGTH(COLUMN_TYPE)-6),'''','')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'stackex'
AND TABLE_NAME = 'addresses'
AND COLUMN_NAME = 'shop'
INTO column_list;
SET num_enums = LENGTH(column_list)- LENGTH(REPLACE(column_list, ',', ''))+1;
WHILE counter < num_enums DO
SET current_value = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(column_list, ',', counter+1),
LENGTH(SUBSTRING_INDEX(column_list, ',', counter )) + 1), ',', ''));
INSERT INTO addresses(articleID, shop, createtime)
VALUES (NEW.articleID, current_value, CURRENT_TIMESTAMP);
SET counter = counter + 1;
END WHILE;
END$$
DELIMITER ;
Test:
mysql> use stackex;
Database changed
mysql> insert into searcharticles values ( 1, 'foo','bar' );
Query OK, 1 row affected (0.00 sec)
mysql> select * from addresses;
+-----------+-------+------+--------+---------------------+------------+
| articleID | shop | url | status | createTime | updateTime |
+-----------+-------+------+--------+---------------------+------------+
| 1 | shop1 | NULL | 0 | 2012-08-17 09:32:33 | NULL |
| 1 | shop2 | NULL | 0 | 2012-08-17 09:32:33 | NULL |
+-----------+-------+------+--------+---------------------+------------+
2 rows in set (0.00 sec)
mysql>
I actually hate myself for answering this! Change the data model to use a lookup table, shops
.
It'll break horribly if the shop enum names have commas or quotation marks in them (probably).
Best Answer
Use
INSERT IGNORE
and have a unique key on the date. The first insert to run for the day will succeed; the rest will be "ignored".