Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* BB Events Insert After Trigger */
- DELIMITER $$
- CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
- FOR EACH ROW
- BEGIN
- UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- IF NEW.`status_code` = 62465 THEN
- INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
- UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1) WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- END IF;
- IF NEW.`status_code` = 62467 THEN
- UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
- UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- END IF;
- END;$$
- DELIMITER ;
- /* BB Journey Update After Trigger */
- DELIMITER $$
- CREATE TRIGGER `journey_update_before` BEFORE UPDATE ON `bb_journey`
- FOR EACH ROW
- BEGIN
- IF NEW.`complete` <> 1 THEN
- SET NEW.`distance` = ROUND(NEW.`end_odometer`-NEW.`start_odometer`,2);
- SET NEW.`duration` = SEC_TO_TIME(NEW.`end_timestamp`-NEW.`start_timestamp`);
- END IF;
- IF NEW.`complete` = 1 THEN
- UPDATE `bb_events` SET `journey_id` = NEW.`id` WHERE `timestamp` BETWEEN NEW.`start_timestamp` AND NEW.`end_timestamp`;
- END IF;
- END;$$
- DELIMITER ;
- DELIMITER $$
- CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
- FOR EACH ROW
- BEGIN
- UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- IF NEW.`status_code` = 62465 THEN
- INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
- UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1) WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- END IF;
- IF NEW.`status_code` = 62467 THEN
- UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
- UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- END IF;
- END $$ -->removing ;
- /* BB Events Insert After Trigger */
- DELIMITER $$
- DROP TRIGGER /*!50032 IF EXISTS */ `event_insert_after` $$
- CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
- FOR EACH ROW
- BEGIN
- UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- IF NEW.`status_code` = 62465 THEN
- INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
- UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1) WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- END IF;
- IF NEW.`status_code` = 62467 THEN
- UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
- UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
- END IF;
- END;
- $$
- /* BB Journey Update After Trigger */
- DELIMITER $$
- DROP TRIGGER /*!50032 IF EXISTS */ `journey_update_before` $$
- CREATE TRIGGER `journey_update_before` BEFORE UPDATE ON `bb_journey`
- FOR EACH ROW
- BEGIN
- IF NEW.`complete` <> 1 THEN
- SET NEW.`distance` = ROUND(NEW.`end_odometer`-NEW.`start_odometer`,2);
- SET NEW.`duration` = SEC_TO_TIME(NEW.`end_timestamp`-NEW.`start_timestamp`);
- END IF;
- IF NEW.`complete` = 1 THEN
- UPDATE `bb_events` SET `journey_id` = NEW.`id` WHERE `timestamp` BETWEEN NEW.`start_timestamp` AND NEW.`end_timestamp`;
- END IF;
- END;
- $$
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement