Databases
StackOverflow2013
Postsdbo
POmysql trigger function
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql trigger function
    primarykey
    data
    text
    <p>I have a table call lp_upload and it contain plate number of car and other related information:</p> <pre><code>CREATE TABLE `lp_upload` ( `date` date NULL , `plate` char(10) NULL , `site` int NULL , `dateid` char(20) NULL ) ; </code></pre> <p>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. </p> <p>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. </p> <p>so i first wrote a match function:</p> <pre><code>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 &lt; myindex then RETURN 0; END IF; IF SUBSTRING(str1,myindex,1)= SUBSTRING(str2,myindex,1)then SET count = count +1; END IF; IF count &gt; 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 </code></pre> <p>and I added a trigger function to the table </p> <pre><code>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 </code></pre> <p>when i run this, the database crashes. can you help fix this problem or suggest a better way to do this. thanks. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShelp
    1. UShelp
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql trigger function
      1. UShelp
      1. VTBountyStart
    2. VO
      singulars
      1. POmysql trigger function
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POmysql trigger function
      1. This table or related slice is empty.
      1. VTUpMod
    1. COtipo at `RETRUN 0;` (but it's not the reason)
      singulars
      1. POmysql trigger function
      1. USSebas
    2. COCan you elaborate on "the database crashes"? What do you mean by "crash"? What's the error code you receive?
      singulars
      1. POmysql trigger function
      1. USJustin ᚅᚔᚈᚄᚒᚔ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload