mysql trigger function

datatrigger, function, mysql, triggers

Solution

I suspect that the problem you're running into is multiple matches. For example, if you have `abcd01234` and `abcde1234` in the database and attempt to insert `abcd$1234`, you'll get an error.

Now, I'm going to assume that this application is supposed to match OCR'd license plates from speed cameras or red-light cameras in order to facilitate ticketing of the vehicle owner. If that's the case, then you want to err on the side of caution and not have the system automatically try to pick from multiple candidates and instead have a real human look at the result and confirm the plate number.

So, operating on that assumption:

DELIMITER //
CREATE TRIGGER `attempt_match_existing_plate`
  BEFORE INSERT
  ON `lp_upload`
FOR EACH ROW BEGIN
  DECLARE exist_plate CHAR(10);
  DECLARE rowcount INT;
  SELECT COUNT(*), plate INTO rowcount, exist_plate FROM lp_upload WHERE platematch(NEW.plate, plate) = 1;
  IF (1 = rowcount) AND (exist_plate IS NOT NULL) THEN
    SET NEW.plate = exist_plate;
  END IF;
END
//
DELIMITER ;

DELIMITER //
CREATE DEFINER = CURRENT_USER
  FUNCTION `platematch`(`plate_new` char(10), `plate_exist` char(10))
  RETURNS INT
BEGIN
   DECLARE myindex INT DEFAULT 0;
   DECLARE match_count INT DEFAULT 0;
   DECLARE maxlength INT;
   SET maxlength = length(plate_new);
   for_loop: LOOP
      SET myindex = myindex + 1;
      IF maxlength < myindex THEN 
         RETURN 0;
      END IF;
      IF SUBSTRING(plate_new, myindex, 1) = SUBSTRING(plate_exist, myindex, 1)
      THEN 
         SET match_count = match_count +1;
      END IF;
      IF match_count >= 6 THEN 
         RETURN 1;
      END IF;
      IF  SUBSTRING(plate_new, myindex, 1) != SUBSTRING(plate_exist, myindex, 1)
      AND SUBSTRING(plate_new, myindex, 1) != '$'
      AND SUBSTRING(plate_exist, myindex, 1) != '$'
      THEN
         RETURN 0; 
      END IF;
   END LOOP for_loop;
   RETURN 0;
END
//

DELIMITER ;

In the scenario I described above, `abcd$1234` will be inserted into the database as-is instead of just being matched to one of multiple potential results automatically.

Problem

I have a table call lp_upload and it contain plate number of car and other related information: ``` CREATE TABLE `lp_upload` ( `date` date NULL , `plate` char(10) NULL , `site` int NULL , `dateid` char(20) NULL ) ; ``` this table is getting information from a traffic camera. however, sometime letter in the plate is not recognized and it will be replace by $. So if a plate is really abc123, but the camera didnt recognized c and 1, it will be ac$$23 that get enter into the table. im suppose to make it so when a new plate is entered and 6 of its letters match an existing plate, it will become that plate. EX: 123$5678 is entered and 12345678 already exist, then 123$5678 will be replace by 12345678. so i first wrote a match function: ``` CREATE DEFINER = CURRENT_USER FUNCTION `matchingfun`(`str1` char(10),`str2` char(10)) RETURNS int BEGIN DECLARE myindex int DEFAULT 0; DECLARE count int DEFAULT 0; DECLARE maxlength int; SET maxlength = length(str1); for_loop: LOOP SET myindex = myindex + 1; IF maxlength < myindex then RETURN 0; END IF; IF SUBSTRING(str1,myindex,1)= SUBSTRING(str2,myindex,1)then SET count = count +1; END IF; IF count > 6 then RETURN 1; END IF; IF SUBSTRING(str1,myindex,1)!= SUBSTRING(str2,myindex,1) and SUBSTRING(str1,myindex,1)!= '$' and SUBSTRING(str2,myindex,1)!= '$'then RETRUN 0; END IF; END LOOP for_loop; RETURN 0; END ``` and I added a trigger function to the table ``` CREATE TRIGGER `trigger1` AFTER INSERT ON `lpr_opt_upload` BEGIN declare old_site_id int; declare old_text char(10); select lpr_text into old_text from lpr_opt_upload where matchingfun(new.lpr_text, lpr_text) = 1; if(old_text is not null) then set new.lpr_text = old_text; end if; END ``` when i run this, the database crashes. can you help fix this problem or suggest a better way to do this. thanks.

Original source