I have an ID in the table, and tr_code ,

 ID tr_code 15 587-20140125-79-15 255 655-20140525-65-255 4587 611-20160815-72-4587 ............................... 35681 715-20170125-45-35681

I need to create a trigger to check if the ID does not match the last digit after - to tr_code, corrected: FOR EXAMPLE

 ID tr_code *381* 587-20140125-79-*384*

He would fix on so

 ID tr_code *381* 587-20140125-79-*381*

Thank you in advance

    1 answer 1

    You change the user data entered before the transaction is completed - in this case it is better to use the BEFORE time. test - the name of your table:

     CREATE TRIGGER `test_checkCode` BEFORE UPDATE ON `test` FOR EACH ROW BEGIN DECLARE code integer default 0; DECLARE newCode varchar(250) default ''; SET code = RIGHT(NEW.`tr_code`, LENGTH(SUBSTRING_INDEX( REVERSE(NEW.`tr_code`), '-', 1 ) ) ); if(NEW.ID != code) THEN SET newCode = CONCAT(SUBSTRING_INDEX( new.tr_code, '-', (LENGTH( new.tr_code ) - LENGTH( REPLACE( new.tr_code, '-', '' ) ) ) ), '-' , NEW.ID ) ; SET NEW.`tr_code` = newCode; END IF; END

    Here I used:

    PS For such moderation, my opinion, it is worth considering the possibility of moving the code to the input level, rather than writing to the database.

    • Thank you so much - Tiko
    • Now you need to create the update with the procedure. - Tiko