mysql触发器使用
mysql触发器使用
月下独酌100 发表于1年前
mysql触发器使用
  • 发表于 1年前
  • 阅读 25
  • 收藏 2
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

摘要: mysql触发器使用
a.准备表:
创建中间表
CREATE TABLE `testTriggerCount` (
      id int(32) not null,
      count int(32) not null
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into testTriggerCount(id,count) values(1,1);
create table testabc as select * from jiraissue(jira的核心表)



b. 创建触发器test1,当testabc表有更新的时候,更新testTriggerCount表的id为1的count=count+1,触发器工作
 b1.DROP TRIGGER IF EXISTS `test1`;

 b2.创建触发器
 DELIMITER $$
 CREATE TRIGGER `test1` after
 update ON `testabc` FOR EACH ROW
 BEGIN
 IF NEW.issuestatus <> OLD.issuestatus && OLD.issuetype=5 && NEW.issuestatus='10007' THEN
 update testTriggerCount set count=count+1 where id=1;
     END IF;
  END$$
 DELIMITER ;

 b3.执行下面语句的时候,更新了testTriggerCount表
 update testabc set issuestatus='10007' where id=16095

c.无效的触发器,可以创建,但是当触发器被触发的时候会报错
 c1.创建触发器
 DELIMITER $$
 CREATE TRIGGER `test1` after
 update ON `testabc` FOR EACH ROW
 BEGIN
 IF NEW.issuestatus <> OLD.issuestatus && OLD.issuetype=5 && NEW.issuestatus='10007' THEN
  update testabc set assignee='abc' where id in (select distinct source from issuelink where destination=OLD.id);
     END IF;
  END$$
 DELIMITER ;

 c2. 创建触发器test1
 DELIMITER $$
 CREATE TRIGGER `test1` before
 update ON `testabc` FOR EACH ROW
 BEGIN
 IF NEW.issuestatus <> OLD.issuestatus && OLD.issuetype=5 && NEW.issuestatus='10007' THEN
 update testabc set issuestatus='10007' where  id in (select distinct source from issuelink where destination=OLD.id);
     END IF;
  END$$
 DELIMITER ;

 c3.当创建上面的任何一个触发器之后,
  执行update  testabc set issuestatus='10007' where id=16095

  会报出下面的错误:
   19:52:26    update jira.testabc set issuestatus='10007' where id=16095
   Error Code: 1442.
   Can't update table 'testabc' in stored function/trigger because it is
   already used by statement which invoked this stored function/trigger.


d.下面这种触发器,只更新受影响的列,是可以的
 d1. 触发器1
 DELIMITER $$
 CREATE TRIGGER `test1` before
 update ON `testabc` FOR EACH ROW
 BEGIN
 IF NEW.issuestatus <> OLD.issuestatus && OLD.issuetype=5 && NEW.issuestatus='10007' THEN
  set new.assignee='abc';
     END IF;
  END$$
 DELIMITER ;

 d2. 触发器1
 DELIMITER $$
 CREATE TRIGGER `test1` before
 update ON `testabc` FOR EACH ROW
 BEGIN
 IF NEW.issuestatus <> OLD.issuestatus && OLD.issuetype=5 && NEW.issuestatus='10007' THEN
  set NEW.issuestatus='111';
     END IF;
  END$$
 DELIMITER ;

 d3.操作下面语句,触发触发器生效
update testabc set issuestatus='10007' where id=16095


参考:
https://www.sitepoint.com/how-to-create-mysql-triggers/
http://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already
共有 人打赏支持
粉丝 26
博文 186
码字总数 56475
×
月下独酌100
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: