• 在使用MySQL的触发器的过程中遇到一个比较蛋疼的问题。
    报错信息如下:

于是查看下数据库中的triggers触发器的定义语句:

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: items_comment_insert
               Event: INSERT
               Table: hz_items_comment
           Statement: BEGIN 
UPDATE hz_items SET comment_count = comment_count + 1 WHERE id = new.item_id;
END
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: items_comment_delete
               Event: DELETE
               Table: hz_items_comment
           Statement: BEGIN 
    UPDATE hz_items SET comment_count = comment_count - 1 WHERE id = old.item_id;
  END
              Timing: AFTER
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)

在上面的定义语句中我们看到了Definer触发器的定义者,那是因为在svn测试服务器的连接用户为luo_account用户。
解决方案

使用shell登录Linux终端,删除之前定义的触发器语句,并执行新的语句。

查看当前连接的用户:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.28, for Linux (x86_64) using  EditLine wrapper

Connection id:        1339
Current database:    www_welltrend
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;

具体执行语句:

DROP TRIGGER items_comment_insert;
DROP TRIGGER items_comment_delete;

DELIMITER $
CREATE TRIGGER items_comment_insert BEFORE INSERT ON hz_items_comment
FOR EACH ROW BEGIN 
UPDATE hz_items SET comment_count = comment_count + 1 WHERE id = new.item_id;
END;
$
DELIMITER ;


DELIMITER $
 CREATE TRIGGER items_comment_delete AFTER DELETE ON hz_items_comment
  FOR EACH ROW BEGIN 
    UPDATE hz_items SET comment_count = comment_count - 1 WHERE id = old.item_id;
  END;
$
DELIMITER ;

定义语句的时候注意加上用户和域

DEFINER="root"@"localhost"

DROP TRIGGER items_comment_insert;
DROP TRIGGER items_comment_delete;
DELIMITER $
CREATE DEFINER="root"@"localhost" TRIGGER items_comment_insert BEFORE INSERT ON hz_items_comment
FOR EACH ROW BEGIN
UPDATE hz_items SET comment_count = comment_count + 1 WHERE id = new.item_id;
END;
$
DELIMITER ;
DELIMITER $
 CREATE DEFINER="root"@"localhost" TRIGGER items_comment_delete AFTER DELETE ON hz_items_comment
  FOR EACH ROW BEGIN 
    UPDATE hz_items SET comment_count = comment_count - 1 WHERE id = old.item_id;
  END;
$
DELIMITER ;

最后编辑: 于 3年前

标签

评论列表(0)

    暂无评论