0

I am facing a very strange issue as i have implemented a trigger on tableA which does encrypt data and insert data to another tableB and update column to 4 digit.
The issue is First insert is somehow skipped and it gets corrected from the 2nd insert onward, don't know why the first insert is skipped. Any help would be great.

Os is Linux and MySQL version is 5.7.9

        create table tableA(c1 varchar(10));

    create table tableB(c1 varchar(60)); 

    delimiter //
        CREATE TRIGGER trig1 before INSERT
        ON  tableA FOR EACH ROW
        BEGIN
              IF LENGTH(LTRIM(RTRIM(new.c1))) > 4 then
                BEGIN
                insert into tableB(c1) select AES_ENCRYPT(new.c1,'test') from tableA;
                Set  new.C1 = RIGHT(LTRIM(RTRIM(new.C1)),4) ;       
                 END;
           end if;
        END;
        //
delimiter ;

insert into tableA values('423423544');

select count(*) from tableA;--1
select count(*) from tableB;--0
0

I think it is because you are selecting from tableA. I guess the right way to do this is:

insert into tableB(c1) values (AES_ENCRYPT(new.c1,'test'));

So, the trigger will become like this:

delimiter //
    CREATE TRIGGER trig1 before INSERT
    ON  tableA FOR EACH ROW
    BEGIN
          IF LENGTH(LTRIM(RTRIM(new.c1))) > 4 then
            BEGIN
            insert into tableB(c1) values (AES_ENCRYPT(new.c1,'test'));
            Set  new.C1 = RIGHT(LTRIM(RTRIM(new.C1)),4) ;       
             END;
       end if;
    END;
    //
delimiter ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.