工作笔记--Mysql触发器使用示例
更新IDC_Device_Info表记录时，记录下修改的时间，存到Device_Last_Modify表中。DROP TRIGGER IF EXISTS deviceLastUpdate;CREATE TRIGGER deviceLastUpdateAFTER UPDATE ON `IDC_Device_Info`FOR EACH ROWBEGINBEGINDECLARE cntOld TINYINT DEFAULT 0;DECLARE serviceIdOld INT DEFAULT 0;DECLARE deviceTypeOld INT DEFAULT 0;SET serviceIdOld = IFNULL(OLD.Service_ID,0);SET deviceTypeOld = IFNULL(OLD.Device_Type,0);SELECT COUNT(*) INTO cntOld FROM Device_Last_Modify WHERE Service_ID = serviceIdOld AND Device_Type = deviceTypeOld;IF cntOld > 0 THENUPDATE Device_Last_Modify SET Last_Modify = UNIX_TIMESTAMP(NOW()) WHERE Service_ID = serviceIdOld AND Device_Type = deviceTypeOld;ELSEINSERT INTO Device_Last_Modify VALUES (serviceIdOld, deviceTypeOld, UNIX_TIMESTAMP(NOW()));END IF;END;BEGINDECLARE cntNew TINYINT DEFAULT 0;DECLARE serviceIdNew INT DEFAULT 0;DECLARE deviceTypeNew INT DEFAULT 0;SET serviceIdNew = IFNULL(NEW.Service_ID,0);SET deviceTypeNew = IFNULL(NEW.Device_Type,0);SELECT COUNT(*) INTO cntNew FROM Device_Last_Modify WHERE Service_ID = serviceIdNew AND Device_Type = deviceTypeNew;IF cntNew > 0 THENUPDATE Device_Last_Modify SET Last_Modify = UNIX_TIMESTAMP(NOW()) WHERE Service_ID = serviceIdNew AND Device_Type = deviceTypeNew;ELSEINSERT INTO Device_Last_Modify VALUES (serviceIdNew, deviceTypeNew, UNIX_TIMESTAMP(NOW()));END IF;END;END;
页:
[1]