Tuesday, July 30, 2013

MySql IF ELSE EXISTS Update Trigger

DELIMITER $$

DROP TRIGGER `rfc`.`insert_send_query_update`$$

CREATE TRIGGER `rfc`.`insert_send_query_update` AFTER UPDATE on `rfc`.`send_query`
FOR EACH ROW BEGIN

 DECLARE P1,P2 VARCHAR(50);

 IF (EXISTS (SELECT send_query_Date_Rcv1 FROM report_one WHERE fileSerial = OLD.fileSerial AND (send_query_Date_Rcv1 IS NULL OR send_query_Date_Rcv1 =''))) THEN
    
   UPDATE report_one SET send_query_Date_Rcv1 = (NEW.received_date),Send_Query_SLA1 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;

 ELSE IF (EXISTS (SELECT send_query_Date_Rcv2 FROM report_one WHERE fileSerial = OLD.fileSerial AND (send_query_Date_Rcv2 IS NULL OR send_query_Date_Rcv2 =''))) THEN
  
   UPDATE report_one SET send_query_Date_Rcv2 = (NEW.received_date),Send_Query_SLA2 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;

 ELSE IF (EXISTS (SELECT send_query_Date_Rcv3 FROM report_one WHERE fileSerial = OLD.fileSerial AND (send_query_Date_Rcv3 IS NULL OR send_query_Date_Rcv3 =''))) THEN

     UPDATE report_one SET send_query_Date_Rcv3 = (NEW.received_date),Send_Query_SLA3 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;
 ELSE
     UPDATE report_one SET send_query_Date_Rcv4 = (NEW.received_date),Send_Query_SLA4 = NEW.Sq_SlaDate WHERE fileSerial = OLD.fileSerial;
 END IF;
 END IF;
 END IF;


END;$$

DELIMITER ;

No comments:

Post a Comment