"MySQL" MySQL trigger use example

MySQL triggers

• Point in time: Before/after
• Trigger Event: Update/delete/insert
• Point-in-time + trigger event: The trigger timing of a complete trigger;
• A triggering time can only be up to 1 trigger: If Before-insert can have up to 1 triggers, if more than one is required, there are some SQL Statement within 1 trigger;

Old and new

• Insert: Only the New keyword can be used;
• Both update:new and old keywords are available;
• Delete: Only the old keyword can be used;

Complete statement

CREATE TABLE ' capacity_pm ' (' id ' int (one) not NULL auto_increment COMMENT ' self-increment primary key ', ' pool_id ' char () CHARACTER SET UTF8 Default NULL COMMENT ' resource pool id ', ' cluster_lv1 ' varchar (255) CHARACTER SET UTF8 DEFAULT null COMMENT ' Cluster class ', ' Cluster_lv2 ' V Archar (255) CHARACTER SET UTF8 default NULL COMMENT ' cluster Level 2 class ', ' Update_at ' datetime DEFAULT current_timestamp COMMENT ' Update or  Creation time ', ' templete_id ' varchar (255) CHARACTER set UTF8 not NULL COMMENT ' template id ', ' templete_name ' varchar (255) CHARACTER Set UTF8 DEFAULT NULL COMMENT ' template name ', ' templete_cpu_core ' int (ten) unsigned zerofill not NULL COMMENT ' template CPU Cores ', ' templete_m Em_size ' double not null COMMENT ' template memory size ', ' templete_disk_size ' double not null COMMENT ' template disk size ', ' host_total ' int (one) u nsigned zerofill default NULL COMMENT ' Total number of hosts ', ' host_used ' int (one) unsigned zerofill default null COMMENT ' host allocated quantity ', ' CPU _core_total ' int (one) unsigned zerofill default null COMMENT ' Total CPU cores ', ' cpu_core_free ' int (one) default null, ' Cpu_core_us Ed ' int (one) DEFAULT NULL COMMENT ' CPU allocated quantity ', ' Cpu_core_util ' double (10,3) default null COMMENT ' CPU core usage accounted for ', ' mem_total ' double default null COMM ENT ' Memory total space ', ' mem_free ' double default null, ' mem_used ' double default null, ' Mem_util ' double default null COMMENT ' memory  Use% ', ' disk_total ' double default null, ' Disk_free ' double default null, ' disk_used ' double default null, ' Disk_util '  Double DEFAULT NULL COMMENT ' disk use accounted for ', PRIMARY key (' id '), UNIQUE key ' Idx_templete_all ' (' pool_id ', ' templete_id ') using BTREE COMMENT ' module ID do full index ') engine=innodb auto_increment=101 DEFAULT Charset=utf8 collate=utf8_bin; CREATE TRIGGER ' Pm_before_insert_trigger ' before insert on ' capacity_pm ' for each ROW beginset New.cpu_core_total=new.hos T_total * New.templete_cpu_core;set new.cpu_core_used=new.host_used * New.templete_cpu_core;set new.cpu_core_free= New.cpu_core_total-new.cpu_core_used;set New.cpu_core_util=new.cpu_core_used/new.cpu_core_total;end; CREATE TRIGGER ' Pm_before_update_trigger ' before update on ' capacity_pm ' FOR each ROW beginset new.cpu_core_total=new.host_total * Old.templete_cpu_core;set new.cpu_core_used=new.host_used * Old.templete_cpu_core;set New.cpu_core_free=new.cpu_core_total-new.cpu_core_used;set New.cpu_core_util=new.cpu_ Core_used/new.cpu_core_total;end;

To create a statement for a trigger

CREATE TRIGGER `pm_before_insert_trigger` BEFORE INSERT ON `capacity_pm` FOR EACH ROW beginset new.cpu_core_total=new.host_total * new.templete_cpu_core;set new.cpu_core_used=new.host_used * new.templete_cpu_core;set new.cpu_core_free=new.cpu_core_total - new.cpu_core_used;set new.cpu_core_util=new.cpu_core_used / new.cpu_core_total;end;CREATE TRIGGER `pm_before_update_trigger` BEFORE UPDATE ON `capacity_pm` FOR EACH ROW beginset new.cpu_core_total=new.host_total * old.templete_cpu_core;set new.cpu_core_used=new.host_used * old.templete_cpu_core;set new.cpu_core_free=new.cpu_core_total - new.cpu_core_used;set new.cpu_core_util=new.cpu_core_used / new.cpu_core_total;end;

Reference

Mysql5.6-trigger official website

"MySQL" MySQL trigger use example