CODEWITHSUNDEEP
mysqlsql
Joshua
Joshua

Reputation: 615

How to insert a record in MySQL table if a condition exists in a trigger

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

Answers (1)

Turo
Turo

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

Related Questions