触发器的作用:通过往一张临时表插入数据,来更新另外一张表的数据,如果另外一张表存在该数据则更新,如果不存在,则插入到另外一张表。
drop trigger if exists `insertContactTMP`;
DELIMITER ;;
CREATE TRIGGER `insertContactTMP` AFTER INSERT ON `contact_tmp_tbl` FOR EACH ROW BEGIN
SET @x = (select receiver_id from contact_tbl where match_sign=new.match_sign and c_group_id=new.c_group_id);
IF ISNULL(@x) THEN
SET @maxId = (select max(receiver_id) from contact_tbl);
IF ISNULL(@maxId) THEN
SET @maxId=0;
END IF;
SET @x = @maxId + 1;
insert into contact_tbl values(@x,new.c_group_id,new.receiver_code,new.match_sign,new.receiver_name,new.company_name,new.c_telephone,new.mobilephone,new.fax_number1,new.fax_number2,new.fax_number3,new.fax_number4,new.c_email1,new.c_email2,new.c_email3,new.fax_email_flag,new.send_line,new.update_time,new.business,new.business_name,new.trustee);
ELSE
update contact_tbl set receiver_name=new.receiver_name,company_name=new.company_name,c_telephone=new.c_telephone,mobilephone=new.mobilephone,fax_number1=new.fax_number1,fax_number2=new.fax_number2,fax_number3=new.fax_number3,new.fax_number4=new.fax_number4,c_email1=new.c_email1,c_email2=new.c_email2,c_email3=new.c_email3,fax_email_flag=new.fax_email_flag,send_line=new.send_line,update_time=new.update_time,business=new.business,business_name=new.business_name,trustee=new.trustee
where receiver_id=@x;
END IF;
END
;;
DELIMITER ;
次触发器是个鸡肋,数据量并发大的时候,不能用,影响效率。