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