MySQL Basics: Triggers

Don't say much nonsense. See for yourself. After a while there is time to perfect and perfect. ^-^

SQL code

CREATE TABLE ' vardata ' (  
  ' Id ' int () NOT NULL auto_increment,  
  ' name ' varchar (255) DEFAULT null,  
  ' time ' datet IME not NULL DEFAULT ' 0000-00-00 00:00:00 ',  
  ' data ' double (15,5) DEFAULT ' -601.00000 ',  
  PRIMARY KEY (' Id ', ' time '),  
  unique KEY ' unique ' (' name ', ' time ')  
      
CREATE TABLE ' Vardata_compute ' (  
  ' Id ' int () NOT NULL auto_increment,  
  ' name ' varchar (255) DEFAULT null,  
  ' Time ' datetime default NULL,  
  ' data ' double (15,5) default NULL,  
  PRIMARY KEY (' Id ')  

SQL code

DROP TRIGGER IF EXISTS trigger_insert_59data;                
     CREATE TRIGGER trigger_insert_59data After insert in vardata for each ROW BEGIN DECLARE time_59 DateTime;   
     SET time_59 = new.time-interval 1 SECOND; IF MINUTE (new.time) =0 and SECOND (new.time) =0 THEN INSERT into Vardata_compute SET name=new.name,time=time_59,data=    
     New.data;     
End IF;  
      
End;  
DROP TRIGGER IF EXISTS trigger_delete_59data;       
     CREATE TRIGGER trigger_delete_59data After delete in vardata for each ROW BEGIN DECLARE time_59 DateTime;   
     SET time_59 = old.time-interval 1 SECOND; IF MINUTE (old.time) =0 and SECOND (old.time) =0 THEN DELETE from Vardata_compute WHERE time=time_59 and Name=old  
     . Name;     
End IF;  
      
End;  
DROP TRIGGER IF EXISTS trigger_update_59data;       
     CREATE TRIGGER trigger_update_59data after update in vardata for each ROW BEGIN DECLARE time_59 DateTime; SET time_59 = NEW.time-interval 1 SECOND; IF MINUTE (new.time) =0 and SECOND (new.time) =0 THEN UPDATE vardata_compute SET data=new.data WHERE time=ti  
     Me_59 and Name=new.name;
End IF; End;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/