主页 / dba / 问题 / 11046
Accepted
Mark D
Asked: 2012-01-21 07:00:01 +0800 CST

仅对一张表禁用触发器

  • 772

是否可以暂时禁用触发器但仅针对一个表。

例如，我有一个表 TableA 与插入、更新和删除触发器。我也有一个具有相同触发器的表 B，但它们只影响表 A 中的某些列。

我现在有一个使用两个表的更新查询。我知道表 A 中的更新需要触发触发器，但表 B 中的更新绝对不需要触发触发器。所以我想禁用这些触发器，直到更新完成。

这可能吗？我正在使用 MySQL 5.1

[附录]

这是一个触发器表 A 本质上

BEGIN
    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

以下是表 B 上的触发器

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

这是从表 B 触发的存储过程

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - All Count
   -- OLDAC- Old All Count
   DECLARE AC, OLDAC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   SELECT allCount
     INTO OLDAC
     FROM usergroups
    WHERE ug.`id` = _id;

IF (OLDAC <> AC) THEN 

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END IF;

END $$
mysql trigger

3 个回答

  1. Best Answer

    实际上，如果您在每个触发器中放置一个 if then 块，您可以有效地关闭所有触发器。这是这样一个代码块

    IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

    在mysql环境下，你可以

    • 跑SET @TRIGGER_DISABLED = 1;
    • 进行数据维护
    • 跑SET @TRIGGER_DISABLED = 0;

    因此，您对表 A 的触发器应如下所示：

    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

    因此，您的表 B 触发器应如下所示：

    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;

    如果您希望启动表 A 的触发器但不启动表 B，则仅将代码块添加到表 B 的触发器。

    • 7

  2. 您可以简化这种方法。

    1. 只需将标签添加到第一个“BEGIN”块。
    2. 测试，如果控制变量不为空。
    3. 如果是，请离开触发器。

    因此，您可以避免将原始触发代码包装到“IF”状态中。只有触发头必须以明确定义的方式进行修改 - 这更简单且更可靠。

    例子：

    CREATE TRIGGER [YOUR_TRIGGER_SPEC]
Trigger: BEGIN

      IF @TRIGGER_DISABLED NOT NULL THEN
         LEAVE Trigger;
      END IF;

      [YOUR CODE]
END;

    玩得开心 ：-）

    编辑者 RolandoMySQLDBA 2012-05-01 18:38 EDT

    我实际上在几个月前尝试过，它不稳定。方法如下：

    样品表

    mysql> show create table mytext\G
*************************** 1. row ***************************
       Table: mytext
Create Table: CREATE TABLE `mytext` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` text NOT NULL,
  `txtmd5` char(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `txtmd5` (`txtmd5`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from mytext;
+----+------------------------+----------------------------------+
| id | txt                    | txtmd5                           |
+----+------------------------+----------------------------------+
|  1 | Rolando Edwards        | ab14209a029a8f6d42d7c5a5d7a77623 |
|  2 | Pamela Edwards         | 5dcbd06ea48c690032b1b29a514eb0e2 |
|  3 | Dominique Edwards      | 7487431d74ac2d17a9d63123672a4bdf |
|  4 | Diamond Edwards        | bc8d80541a000ed506048134058e2878 |
|  5 | The Quick Brown Fox    | be94284cfa534c1837e744c061f71e17 |
| 11 | Quick Brown Fox Jumped | 495a52136057b242a80e514d7cbe77c7 |
+----+------------------------+----------------------------------+
6 rows in set (0.00 sec)

mysql>

    这是触发器：

    DROP TRIGGER IF EXISTS mytext_bi;
DELIMITER $$
CREATE TRIGGER mytext_bi BEFORE INSERT ON mytext FOR EACH ROW
TheTrigger:BEGIN
    DECLARE found_count INT;
    SELECT COUNT(1) INTO found_count
    FROM mytext WHERE txtmd5 = MD5(LEFT(new.txt,10));
    IF found_count = 1 THEN
        LEAVE TheTrigger;
    END IF;
    SET new.txtmd5 = MD5(LEFT(new.txt,10));
END $$
DELIMITER ;

    看看我尝试插入“Dominique Edwards”时会发生什么

    mysql> insert into mytext (txt) values ('Dominique Edwards');
Query OK, 1 row affected (0.06 sec)

mysql> select * from mytext;
+----+------------------------+----------------------------------+
| id | txt                    | txtmd5                           |
+----+------------------------+----------------------------------+
|  1 | Rolando Edwards        | ab14209a029a8f6d42d7c5a5d7a77623 |
|  2 | Pamela Edwards         | 5dcbd06ea48c690032b1b29a514eb0e2 |
|  3 | Dominique Edwards      | 7487431d74ac2d17a9d63123672a4bdf |
|  4 | Diamond Edwards        | bc8d80541a000ed506048134058e2878 |
|  5 | The Quick Brown Fox    | be94284cfa534c1837e744c061f71e17 |
| 11 | Quick Brown Fox Jumped | 495a52136057b242a80e514d7cbe77c7 |
| 14 | Dominique Edwards      |                                  |
+----+------------------------+----------------------------------+
7 rows in set (0.00 sec)

mysql>

    AHHH，它无论如何都滑倒了！

    我仍然会为您的答案 +1，这不仅是因为您的努力，还因为我尝试了完全相同的事情（在 附近放置标签BEGIN），但当时它对我不起作用。有什么学习方法。

    没有人应该完全相信 MySQL 存储过程语言（信号处理未在其中正确实现），当您说“玩得开心”时，您强调了这一点。有时，玩得开心意味着试试看！！！. 这才是真正的学习精神。

    我像这样解决它：

    DROP TRIGGER IF EXISTS mytext_bi;
DELIMITER $$
CREATE TRIGGER mytext_bi BEFORE INSERT ON mytext FOR EACH ROW
BEGIN
    DECLARE found_count INT;
    SELECT COUNT(1) INTO found_count
    FROM mytext WHERE txtmd5 = MD5(LEFT(new.txt,10));
    IF found_count = 0 THEN
        SELECT COUNT(1) INTO found_count FROM table_that_does_not_exist;
    END IF;
    SET new.txtmd5 = MD5(LEFT(new.txt,10));
END $$
DELIMITER ;

    存储过程的本质是要么像我在回答中那样通过 IF-THEN 块浏览完整的代码，要么使用信号处理（同样，它不是完全可操作的）

    欢迎来到 DBA StackExchange ！！！再次，+1 作为承诺。

    很抱歉进行了长时间的编辑，但我无法将示例代码放在评论中。

    • 4

  3. 首先：感谢'+1' :-)

    而且-您是对的：禁用触发器不会阻止自己的行动。目前没有“正确”的方式来执行这种“ABORT”-MySQL 实现的信号。

    正如您在回答中所描述的，常见的“解决方法”是引发 SQL 异常。因此，整个操作将被副作用（异常）阻止。

    但是：小心。这种“解决方案”有时会产生更多问题 - 考虑事务触发器和需要的 - 并且希望是可控的 - 回滚。呜呜呜！（这就是为什么我在这种方法中使用引号）。

    此外，调试此类实现是一件非常困难的事情。通过这种行为，开发人员、管理员或维护人员必须能够区分“良好”的 SQL 异常和真正发生的问题。可怕。

    最后同样重要的是：这种限制会影响 MySQL 中的每个存储例程（过程、函数和触发器）！

    事实上：这种缺乏控制是每个 RDBMS 提供者的责任。是的-这就是我所说的“玩得开心”:-)

    对，回到根源 - 问题是：“禁用触发器”（而不是阻止动作）。特别是对于这种特殊要求，该方法是安全可靠的。

    “在 szene 之后”的更多信息：所提供的方法很安全，因为 MySQL 使用 NULL 初始化基于会话的变量，例如 '@TRIGGER_DISABLED' - 这也是一个副作用。

    请记住：这种变量只对当前会话可见！因此，您可以禁用当前会话的触发器。这种实现没有“服务器范围”的影响！

    如果您需要这种东西（禁用整个服务器的触发器），您必须使用服务器范围内的可见和可访问对象，例如服务器变量（uuups - 另一种具有不可预测副作用的“解决方法”）或 - 更清洁的方式 - 表。

    所以，对于我的冗长回复，也“对不起”：玩得开心:-)

    • 2

相关问题