Validation with mysql and qualified errors

Question

in addition to my question about best solution for csv import i search for validation methods where i get qualified errors using msyql only. Short description:

i import data by LOAD DATA INFILE into a temporary table and then i need to validate this data.

Fatest way would be a select with several filters like:

SELECT * from temp_table WHERE col1 not in (1,2) OR col2 REGEXP '[0-9]+' etc.

Works also. But i would like to know fastest way to save the column why the row is found, maybe with case and a counter ort sth. similar?

So that i can then return a qualified error like: "column x can only consist of y"

Answer 1

You could put a trigger on the temp_table before loading the data from the CSV-file.

The trigger would either insert a row into the permanent table (if it passes validation) or into an error table if it doesn't. Something like this should work.

DELIMITER //

CREATE TRIGGER validating_insert
AFTER INSERT ON temp_table FOR EACH ROW
BEGIN
  IF col1 NOT IN (1,2) OR col2 REGEXP '[0-9]+' THEN
    INSERT INTO permanent_table VALUES (NEW.col1, NEW.col2);
  ELSE
    INSERT INTO error_table VALUES (NEW.col1, NEW.col2, 'any reason goes here');
  END IF;
END//