Q:

mysql trigger function

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. 

A:

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.

