Reputation: 615
BLUF: I want to use a before insert
trigger to prevent invalid data from being inserted, and instead store that data in another table with an error message for reviewing later.
I have a table in which my Raspberry Pi weather station is recording weather data. Sometimes it has a hardware failure for various reasons, and when that happens it returns -1000 for that particular data point.
I have tried to write a before insert
trigger that would prevent these records from being written to the table with other valid data. This part seemed to work. But I would like to also write these erroneous records to a different table. While testing, bad data is rejected as expected, but no records are being written to the error log table.
Here is the trigger I have written:
DROP TRIGGER IF EXISTS wx_beforeInsert;
SET @ERROR_MESSAGE = '';
DELIMITER $$
CREATE TRIGGER `wx_beforeInsert` BEFORE INSERT ON WEATHER_MEASUREMENT
FOR EACH ROW BEGIN
IF (NEW.AMBIENT_TEMPERATURE = '-1000') THEN
SET @ERROR_MESSAGE = "Ambient tempurature invalid. Record rejected.";
ELSEIF (NEW.GROUND_TEMPERATURE = '-1000') THEN
SET @ERROR_MESSAGE = "Ground tempurature invalid. Record rejected.";
ELSEIF (NEW.AIR_QUALITY = '-1000') THEN
SET @ERROR_MESSAGE = "Air quality invalid. Record rejected.";
ELSEIF (NEW.AIR_PRESSURE = '-1000') THEN
SET @ERROR_MESSAGE = "Air pressure invalid. Record rejected.";
ELSEIF (NEW.HUMIDITY = '-1000') THEN
SET @ERROR_MESSAGE = "Humidity invalid. Record rejected.";
END IF;
IF (@ERROR_MESSAGE <> '') THEN
INSERT INTO `weather`.`WEATHER_MEASUREMENT_ERRORS`
(`AMBIENT_TEMPERATURE`,
`GROUND_TEMPERATURE`,
`AIR_QUALITY`,
`AIR_PRESSURE`,
`HUMIDITY`,
`WIND_DIRECTION`,
`WIND_SPEED`,
`WIND_GUST_SPEED`,
`RAINFALL`,
`CREATED`,
`ERROR_MESSAGE`)
VALUES
(NEW.AMBIENT_TEMPERATURE,
NEW.GROUND_TEMPERATURE,
NEW.AIR_QUALITY,
NEW.AIR_PRESSURE,
NEW.HUMIDITY,
NEW.WIND_DIRECTION,
NEW.WIND_SPEED,
NEW.WIND_GUST_SPEED,
NEW.RAINFALL,
NEW.CREATED,
@ERROR_MESSAGE);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @ERROR_MESSAGE;
END IF;
END;
$$
Here is the script needed to create the supporting tables for anyone who would like to try to help:
CREATE DATABASE IF NOT EXISTS `weather` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `weather`;
-- Create the WEATHER_MEASUREMENT table if it doesn't exist
CREATE TABLE IF NOT EXISTS `WEATHER_MEASUREMENT` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`REMOTE_ID` bigint(20) DEFAULT NULL,
`AMBIENT_TEMPERATURE` decimal(6,2) NOT NULL,
`GROUND_TEMPERATURE` decimal(6,2) NOT NULL,
`AIR_QUALITY` decimal(6,2) NOT NULL,
`AIR_PRESSURE` decimal(6,2) NOT NULL,
`HUMIDITY` decimal(6,2) NOT NULL,
`WIND_DIRECTION` decimal(6,2) DEFAULT NULL,
`WIND_SPEED` decimal(6,2) NOT NULL,
`WIND_GUST_SPEED` decimal(6,2) NOT NULL,
`RAINFALL` decimal(6,4) DEFAULT NULL,
`CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4126 DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `WEATHER_MEASUREMENT_ERRORS` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`REMOTE_ID` bigint(20) DEFAULT NULL,
`AMBIENT_TEMPERATURE` decimal(6,2) NOT NULL,
`GROUND_TEMPERATURE` decimal(6,2) NOT NULL,
`AIR_QUALITY` decimal(6,2) NOT NULL,
`AIR_PRESSURE` decimal(6,2) NOT NULL,
`HUMIDITY` decimal(6,2) NOT NULL,
`WIND_DIRECTION` decimal(6,2) DEFAULT NULL,
`WIND_SPEED` decimal(6,2) NOT NULL,
`WIND_GUST_SPEED` decimal(6,2) NOT NULL,
`RAINFALL` decimal(6,4) DEFAULT NULL,
`CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ERROR_MESSAGE` varchar(50) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4126 DEFAULT CHARSET=latin1;
Upvotes: 1
Views: 117
Reputation: 4924
I think your insert in WEATHER_MEASUREMENT_ERRORS ia rolled back, too. Try to make WEATHER_MEASUREMENT_ERRORS nontransactional(ROLLBACK doesn't apply to nontransactional tables) by making it an MyISAM-Table.
Upvotes: 1