Maple Ridge db: new triggers
Posted by mholmes on 26 Apr 2017 in Activity log
I've slightly modified the triggers for the Maple Ridge dbs on AG's instructions, to add block info into the description used for dropdowns:
DROP TRIGGER IF EXISTS `landscapes_mapridgelive`.`prp_desc_insert`;
DELIMITER //
CREATE TRIGGER `landscapes_mapridgelive`.`prp_desc_insert` BEFORE INSERT ON `landscapes_mapridgelive`.`props`
FOR EACH ROW BEGIN
DECLARE next_id INT;
SET next_id = (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE() AND `TABLE_NAME`='props');
SET NEW.`prp_desc` =
concat(
IF (NEW.`prp_township` != '', concat('T:', LPAD(NEW.`prp_township`, 5, '0'), ' '), ''),
IF (NEW.`prp_district` != '', concat('DL:', LPAD(NEW.`prp_district`, 5, '0'), ' '), ''),
IF (NEW.`prp_section` != '', concat('S:', LPAD(NEW.`prp_section`, 5, '0'), ' '), ''),
IF (NEW.`prp_block` != '', concat('B:', LPAD(NEW.`prp_block`, 5, '0'), ' '), ''),
IF (NEW.`prp_quadrant` != '', concat('Q:', NEW.`prp_quadrant`, ' '), ''),
IF (NEW.`prp_plan` != '', concat('PL:', LPAD(NEW.`prp_plan`, 5, '0'), ' '), ''),
IF (NEW.`prp_sketch` != '', concat('SK:', LPAD(NEW.`prp_sketch`, 5, '0'), ' '), ''),
IF (NEW.`prp_lot` != '', concat('L:', LPAD(NEW.`prp_lot`, 5, '0'), ' '), ''),
IF (NEW.`prp_parcel` != '', concat('PC:', LPAD(NEW.`prp_parcel`, 5, '0'), ' '), ''),
IF (NEW.`prp_acreage` != '', concat('A:', NEW.`prp_acreage`, ' '), ''),
IF (NEW.`prp_exception` != '', 'EX', ''),
' (#', next_id, ')'
);
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `landscapes_mapridgelive`.`prp_desc_update`;
DELIMITER //
CREATE TRIGGER `landscapes_mapridgelive`.`prp_desc_update` BEFORE UPDATE ON `landscapes_mapridgelive`.`props`
FOR EACH ROW BEGIN
SET NEW.`prp_desc` =
concat(
IF (NEW.`prp_township` != '', concat('T:', LPAD(NEW.`prp_township`, 5, '0'), ' '), ''),
IF (NEW.`prp_district` != '', concat('DL:', LPAD(NEW.`prp_district`, 5, '0'), ' '), ''),
IF (NEW.`prp_section` != '', concat('S:', LPAD(NEW.`prp_section`, 5, '0'), ' '), ''),
IF (NEW.`prp_block` != '', concat('B:', LPAD(NEW.`prp_block`, 5, '0'), ' '), ''),
IF (NEW.`prp_quadrant` != '', concat('Q:', NEW.`prp_quadrant`, ' '), ''),
IF (NEW.`prp_plan` != '', concat('PL:', LPAD(NEW.`prp_plan`, 5, '0'), ' '), ''),
IF (NEW.`prp_sketch` != '', concat('SK:', LPAD(NEW.`prp_sketch`, 5, '0'), ' '), ''),
IF (NEW.`prp_lot` != '', concat('L:', LPAD(NEW.`prp_lot`, 5, '0'), ' '), ''),
IF (NEW.`prp_parcel` != '', concat('PC:', LPAD(NEW.`prp_parcel`, 5, '0'), ' '), ''),
IF (NEW.`prp_acreage` != '', concat('A:', NEW.`prp_acreage`, ' '), ''),
IF (NEW.`prp_exception` != '', 'EX', ''),
' (#', NEW.`prp_property_id`, ')'
);
END
//
DELIMITER ;
I haven't yet updated all the old descriptions, since most of them don't have block info, but I'll do that when I have enough time to be cautious about it.