DELIMITER | CREATE TRIGGER hose_log BEFORE UPDATE ON HOSE FOR EACH ROW BEGIN UPDATE HOSELOG SET B1 = IF(ISNULL(NEW.B1),B1,IF(NEW.B1=OLD.B1,B1,IF(NEW.B1>OLD.B1,2,1))), cB1 = IF(ISNULL(NEW.B1),cB1,IF(NEW.B1=OLD.B1,cB1,NOW())), B2 = IF(ISNULL(NEW.B2),B2,IF(NEW.B2=OLD.B2,B2,IF(NEW.B2>OLD.B2,2,1))), cB2 = IF(ISNULL(NEW.B2),cB2,IF(NEW.B2=OLD.B2,cB2,NOW())), B3 = IF(ISNULL(NEW.B3),B3,IF(NEW.B3=OLD.B3,B3,IF(NEW.B3>OLD.B3,2,1))), cB3 = IF(ISNULL(NEW.B3),cB3,IF(NEW.B3=OLD.B3,cB3,NOW())), S1 = IF(ISNULL(NEW.S1),S1,IF(NEW.S1=OLD.S1,S1,IF(NEW.S1>OLD.S1,2,1))), cS1 = IF(ISNULL(NEW.S1),cS1,IF(NEW.S1=OLD.S1,cS1,NOW())), S2 = IF(ISNULL(NEW.S2),S2,IF(NEW.S2=OLD.S2,S2,IF(NEW.S2>OLD.S2,2,1))), cS2 = IF(ISNULL(NEW.S2),cS2,IF(NEW.S2=OLD.S2,cS2,NOW())), S3 = IF(ISNULL(NEW.S3),S3,IF(NEW.S3=OLD.S3,S3,IF(NEW.S3>OLD.S3,2,1))), cS3 = IF(ISNULL(NEW.S3),cS3,IF(NEW.S3=OLD.S3,cS3,NOW())), B1Vol = IF(ISNULL(NEW.B1Vol),B1Vol,IF(NEW.B1Vol=OLD.B1Vol,B1Vol,IF(NEW.B1Vol>OLD.B1Vol,2,1))), cB1Vol = IF(ISNULL(NEW.B1Vol),cB1Vol,IF(NEW.B1Vol=OLD.B1Vol,cB1Vol,NOW())), B2Vol = IF(ISNULL(NEW.B2Vol),B2Vol,IF(NEW.B2Vol=OLD.B2Vol,B2Vol,IF(NEW.B2Vol>OLD.B2Vol,2,1))), cB2Vol = IF(ISNULL(NEW.B2Vol),cB2Vol,IF(NEW.B2Vol=OLD.B2Vol,cB2Vol,NOW())), B3Vol = IF(ISNULL(NEW.B3Vol),B3Vol,IF(NEW.B3Vol=OLD.B3Vol,B3Vol,IF(NEW.B3Vol>OLD.B3Vol,2,1))), cB3Vol = IF(ISNULL(NEW.B3Vol),cB3Vol,IF(NEW.B3Vol=OLD.B3Vol,cB3Vol,NOW())), S1Vol = IF(ISNULL(NEW.S1Vol),S1Vol,IF(NEW.S1Vol=OLD.S1Vol,S1Vol,IF(NEW.S1Vol>OLD.S1Vol,2,1))), cS1Vol = IF(ISNULL(NEW.S1Vol),cS1Vol,IF(NEW.S1Vol=OLD.S1Vol,cS1Vol,NOW())), S2Vol = IF(ISNULL(NEW.S2Vol),S2Vol,IF(NEW.S2Vol=OLD.S2Vol,S2Vol,IF(NEW.S2Vol>OLD.S2Vol,2,1))), cS2Vol = IF(ISNULL(NEW.S2Vol),cS2Vol,IF(NEW.S2Vol=OLD.S2Vol,cS2Vol,NOW())), S3Vol = IF(ISNULL(NEW.S3Vol),S3Vol,IF(NEW.S3Vol=OLD.S3Vol,S3Vol,IF(NEW.S3Vol>OLD.S3Vol,2,1))) , cS3Vol = IF(ISNULL(NEW.S3Vol),cS3Vol,IF(NEW.S3Vol=OLD.S3Vol,cS3Vol,NOW())), Last = IF(ISNULL(NEW.Last),Last,IF(NEW.Last=OLD.Last,Last,IF(NEW.Last>OLD.Last,2,1))), cLast = IF(ISNULL(NEW.Last),cLast,IF(NEW.Last=OLD.Last,cLast,NOW())), LastVol = IF(ISNULL(NEW.LastVol),LastVol,IF(NEW.LastVol=OLD.LastVol,LastVol,IF(NEW.LastVol>OLD.LastVol,2,1))), cLastVol = IF(ISNULL(NEW.LastVol),cLastVol,IF(NEW.LastVol=OLD.LastVol,cLastVol,NOW())), MatchVol = IF(ISNULL(NEW.MatchVol),MatchVol,IF(NEW.MatchVol=OLD.MatchVol,MatchVol,IF(NEW.MatchVol>OLD.MatchVol,2,1))), cMatchVol = IF(ISNULL(NEW.MatchVol),cMatchVol,IF(NEW.MatchVol=OLD.MatchVol,cMatchVol,NOW())), CurrentRoom = IF(ISNULL(NEW.CurrentRoom),CurrentRoom,IF(NEW.CurrentRoom=OLD.CurrentRoom,CurrentRoom,IF(NEW.CurrentRoom>OLD.CurrentRoom,2,1))), cCurrentRoom = IF(ISNULL(NEW.CurrentRoom),cCurrentRoom,IF(NEW.CurrentRoom=OLD.CurrentRoom,cCurrentRoom,NOW())) WHERE StockNo=OLD.StockNo; END | CREATE TRIGGER hnx_log BEFORE UPDATE ON HNX FOR EACH ROW BEGIN IF NEW.3GIA=1 THEN UPDATE HNXLOG SET B1 = IF(ISNULL(NEW.B1),0,IF(NEW.B1=OLD.B1,0,IF(NEW.B1>OLD.B1,2,1))), B2 = IF(ISNULL(NEW.B2),0,IF(NEW.B2=OLD.B2,0,IF(NEW.B2>OLD.B2,2,1))), B3 = IF(ISNULL(NEW.B3),0,IF(NEW.B3=OLD.B3,0,IF(NEW.B3>OLD.B3,2,1))), S1 = IF(ISNULL(NEW.S1),0,IF(NEW.S1=OLD.S1,0,IF(NEW.S1>OLD.S1,2,1))), S2 = IF(ISNULL(NEW.S2),0,IF(NEW.S2=OLD.S2,0,IF(NEW.S2>OLD.S2,2,1))), S3 = IF(ISNULL(NEW.S3),0,IF(NEW.S3=OLD.S3,0,IF(NEW.S3>OLD.S3,2,1))), B1Vol = IF(ISNULL(NEW.B1Vol),0,IF(NEW.B1Vol=OLD.B1Vol,0,IF(NEW.B1Vol>OLD.B1Vol,2,1))), B2Vol = IF(ISNULL(NEW.B2Vol),0,IF(NEW.B2Vol=OLD.B2Vol,0,IF(NEW.B2Vol>OLD.B2Vol,2,1))), B3Vol = IF(ISNULL(NEW.B3Vol),0,IF(NEW.B3Vol=OLD.B3Vol,0,IF(NEW.B3Vol>OLD.B3Vol,2,1))), S1Vol = IF(ISNULL(NEW.S1Vol),0,IF(NEW.S1Vol=OLD.S1Vol,0,IF(NEW.S1Vol>OLD.S1Vol,2,1))), S2Vol = IF(ISNULL(NEW.S2Vol),0,IF(NEW.S2Vol=OLD.S2Vol,0,IF(NEW.S2Vol>OLD.S2Vol,2,1))), S3Vol = IF(ISNULL(NEW.S3Vol),0,IF(NEW.S3Vol=OLD.S3Vol,0,IF(NEW.S3Vol>OLD.S3Vol,2,1))) WHERE StockNo=OLD.StockNo; ELSE UPDATE HNXLOG SET TotalBid = IF(ISNULL(NEW.TotalBid),0,IF(NEW.TotalBid=OLD.TotalBid,0,IF(NEW.TotalBid>OLD.TotalBid,2,1))), TotalOffer = IF(ISNULL(NEW.TotalOffer),0,IF(NEW.TotalOffer=OLD.TotalOffer,0,IF(NEW.TotalOffer>OLD.TotalOffer,2,1))), MatchVol = IF(ISNULL(NEW.MatchVol),0,IF(NEW.MatchVol=OLD.MatchVol,0,IF(NEW.MatchVol>OLD.MatchVol,2,1))), Last = IF(ISNULL(NEW.Last),0,IF(NEW.Last=OLD.Last,0,IF(NEW.Last>OLD.Last,2,1))), LastVol = IF(ISNULL(NEW.LastVol),0,IF(NEW.LastVol=OLD.LastVol,0,IF(NEW.LastVol>OLD.LastVol,2,1))) WHERE StockNo=OLD.StockNo; END IF; END | CREATE TRIGGER upcom_log BEFORE UPDATE ON UPCOM FOR EACH ROW BEGIN UPDATE UPCOMLOG SET TotalBid = IF(ISNULL(NEW.TotalBid),0,IF(NEW.TotalBid=OLD.TotalBid,0,IF(NEW.TotalBid>OLD.TotalBid,2,1))), TotalOffer = IF(ISNULL(NEW.TotalOffer),0,IF(NEW.TotalOffer=OLD.TotalOffer,0,IF(NEW.TotalOffer>OLD.TotalOffer,2,1))), MatchVol = IF(ISNULL(NEW.MatchVol),0,IF(NEW.MatchVol=OLD.MatchVol,0,IF(NEW.MatchVol>OLD.MatchVol,2,1))), Last = IF(ISNULL(NEW.Last),0,IF(NEW.Last=OLD.Last,0,IF(NEW.Last>OLD.Last,2,1))), LastVol = IF(ISNULL(NEW.LastVol),0,IF(NEW.LastVol=OLD.LastVol,0,IF(NEW.LastVol>OLD.LastVol,2,1))), B1 = IF(ISNULL(NEW.B1),0,IF(NEW.B1=OLD.B1,0,IF(NEW.B1>OLD.B1,2,1))), B2 = IF(ISNULL(NEW.B2),0,IF(NEW.B2=OLD.B2,0,IF(NEW.B2>OLD.B2,2,1))), B3 = IF(ISNULL(NEW.B3),0,IF(NEW.B3=OLD.B3,0,IF(NEW.B3>OLD.B3,2,1))), S1 = IF(ISNULL(NEW.S1),0,IF(NEW.S1=OLD.S1,0,IF(NEW.S1>OLD.S1,2,1))), S2 = IF(ISNULL(NEW.S2),0,IF(NEW.S2=OLD.S2,0,IF(NEW.S2>OLD.S2,2,1))), S3 = IF(ISNULL(NEW.S3),0,IF(NEW.S3=OLD.S3,0,IF(NEW.S3>OLD.S3,2,1))), B1Vol = IF(ISNULL(NEW.B1Vol),0,IF(NEW.B1Vol=OLD.B1Vol,0,IF(NEW.B1Vol>OLD.B1Vol,2,1))), B2Vol = IF(ISNULL(NEW.B2Vol),0,IF(NEW.B2Vol=OLD.B2Vol,0,IF(NEW.B2Vol>OLD.B2Vol,2,1))), B3Vol = IF(ISNULL(NEW.B3Vol),0,IF(NEW.B3Vol=OLD.B3Vol,0,IF(NEW.B3Vol>OLD.B3Vol,2,1))), S1Vol = IF(ISNULL(NEW.S1Vol),0,IF(NEW.S1Vol=OLD.S1Vol,0,IF(NEW.S1Vol>OLD.S1Vol,2,1))), S2Vol = IF(ISNULL(NEW.S2Vol),0,IF(NEW.S2Vol=OLD.S2Vol,0,IF(NEW.S2Vol>OLD.S2Vol,2,1))), S3Vol = IF(ISNULL(NEW.S3Vol),0,IF(NEW.S3Vol=OLD.S3Vol,0,IF(NEW.S3Vol>OLD.S3Vol,2,1))) WHERE StockNo=OLD.StockNo; END |