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 |

————————–

DROP TRIGGER mydb.hose_log

trigger

========================

BEGIN
DELETE FROM ACL_TICKETS_BACKUP.tbl_log_assign WHERE id=NEW.id;
INSERT INTO ACL_TICKETS_BACKUP.tbl_log_assign SELECT * from tbl_log_assign where id=NEW.id;
END