Mysql trigger function

Question

I have a table call lp_upload and it contains the plate number of the car and other relevant information:

CREATE TABLE `lp_upload` ( `date` date NULL , `plate` char(10) NULL , `site` int NULL , `dateid` char(20) NULL ) ; 

This table receives information from the traffic camera. however, some letter in the plate is not recognized, and it will be replaced by $. So if the plate is really abc123, but the camera did not recognize c and 1, it will be ac $$ 23, which will go into the table.

im suppose that so when a new plate is introduced, and its 6 letters correspond to an existing plate, it will become one. EX: 123 $ 5678 entered and 12345678 already exist, then 123 $ 5678 will be replaced by 12345678.

so I wrote the matching function first:

 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. You can solve this problem or suggest a better way to do it. Thank you

Answer 1

I suspect the problem you are working with is a few coincidences. For example, if you have abcd01234 and abcde1234 in the database and try to insert abcd$1234 , you will receive an error message.

Now I'm going to suggest that this app should match OCR'd phone numbers from speed cameras or red light cameras to facilitate ticket sales to the car owner. If so, then you want to make a mistake on the side of caution and should not automatically try to choose from several candidates and instead have a real human look at the result and confirm the plate number.

So, acting on this 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 above scenario, abcd$1234 will be inserted into the as-is database instead of automatically matching one of several potential results.