MySQL触发器怎么写？

废话不多说，这篇文章主要讲，从0，到写两个简单的触发器，3分钟学会！

工具：Navicat Premium（黄色的三叶草图标）

触发器1：

BEGIN    
IF new.state  in ('2','3')  then  
  INSERT INTO `userservice`.`ehome_us_green_examine` 
(modifydate,modifyuser,brancCode,stateDescription,state,
userID,fromSystemkey,applydate,makeHospitalName,illnessName,
userName,rightsholderName,tranno) 
VALUES ( sysdate(), 'manage', old.brancCode, old.stateDescription, new.state, 
'', old.fromSystemkey, old.applydate,old.makeHospitalName,old.illnessName,
old.userName,old.rightsholderName, old.tranno);
END IF;  
END

这个触发器比较简单，

1、BEGIN到END,之间是对数据库的操作

2、IF到END IF之间是判断，你想的对，就是如果。。。的意思

3、new.state  in ('2','3') 这句的意思是：该表的字段state为（2或者3）的时候  

4、执行下面的插入语句，new为新值，old是原来的值（如old.userName就是原来的这个字段的值）

5、整个触发器翻译成白话就是，如果该表的state变成2或者3的时候，执行下面的插入语句

备注：那么触发器是发生在状态变化之前还是之后呢？选Before或者After,顾名思义！

触发器2：

BEGIN    
declare phoneno varchar(20) character set utf8;
declare agencynameq varchar(100) character set utf8;
declare agencynameaa varchar(500) character set utf8;
declare phonenumber4 varchar(20) character set utf8;
declare phonenumber6 varchar(20) character set utf8;
IF new.state  in ('2','3')  then  

set agencynameq = (SELECT AgencyName   FROM ehome_green_agency_location WHERE TransNo =new.tranno);
SELECT phonenumber into phonenumber6 FROM sys_user WHERE id = (SELECT userid FROM sys_user_branch WHERE branch_code = 
(SELECT substring((SELECT AgencyCode FROM ehome_green_agency_location WHERE TransNo = new.tranno),1,6)));

SELECT phonenumber into phonenumber4 FROM sys_user WHERE id = (SELECT userid FROM sys_user_branch WHERE branch_code = 
(SELECT substring((SELECT AgencyCode FROM ehome_green_agency_location WHERE TransNo = new.tranno),1,4)));

SELECT idno into agencynameaa  FROM ehome_green_agency_location WHERE TransNo = new.tranno;

 if agencynameq is NULL  then 
set agencynameq = 'sa';
iNSERT INTO `userservice`.`ehome_us_green_message` (
    receivingPlatform,businesstype,serviceid,transno,wxstate,templateid,contents,description,updateflag,senddate,
    sendtype,sendtimes,createduser,createddate)
VALUES
    ('9','00','ceptrans0002',new.tranno,'0','AuditDX',CONCAT('Name:',agencynameq),'未查到审核人员手机号','4',sysdate(),
        '1','0','sys',sysdate());
      END if;
 if phonenumber6 is NOT NULL then 
     INSERT INTO `userservice`.`ehome_us_green_message` (
    receivingPlatform,businesstype,serviceid,transno,wxstate,templateid,contents,description,updateflag,senddate,
    sendtype,sendtimes,createduser,createddate)
VALUES
    ('3',phonenumber6,'ceptrans0002',new.tranno,'0','AuditDX',CONCAT('Name:',agencynameq) ,'待推送','0',sysdate(),
        '1','0','sys',sysdate());
elseif phonenumber4 is NOT NULL then 
INSERT INTO `userservice`.`ehome_us_green_message` (
    receivingPlatform,businesstype,serviceid,transno,wxstate,templateid,contents,description,updateflag,senddate,
    sendtype,sendtimes,createduser,createddate)
VALUES
    ('3',phonenumber4,'ceptrans0002',new.tranno,'0','AuditDX',CONCAT('Name:',agencynameq) ,'待推送','0',sysdate(),
        '1','0','sys',sysdate());
      END if;
END IF;  
END
 

        这个稍微有一点点复杂，别看他多，其实都是也很简单，sql可以不用看，不用知道干嘛的，知道也没用，你也没表，不用纠结，看用法就行！

1、declare phoneno varchar(20) character set utf8;    声明变量，认识单词的话，直译就行：声明一个变量phoneno为vachar，长度为20，编码类型为utf-8

2、IF new.state  in ('2','3')  then   同触发器1

3、set agencynameq = (SELECT AgencyName   FROM ehome_green_agency_location WHERE TransNo =new.tranno);

给变量agencynameq赋值，赋的值是sql语句查出来的

4、set agencynameq = 'sa'; 当然也可以直接这样赋值

5、CONCAT('Name:',agencynameq)  这里是在拼值，举个例子

如果agencynameq = 张三，那么这个字段里面的值就是：Name：张三

CONCAT作用就是拼接字符串的