CODEWITHSUNDEEP
mysqlsqldatabasetriggers
Abdulla Dlshad
Abdulla Dlshad

Reputation: 141

Before insert triggert in mysql

I am trying to use a trigger to insert the updated value to another table. I have two tables (table1 and table2).

I want to copy the new primary key of table1 to a non-primary key field in table2 in hexadecimal value. the primary key is integer type and the other field is varchar.

The code is as follows:

delimiter /

drop trigger if exists a /
create trigger a before insert on table1 for each row 

begin
 insert into table2 set table1ID= hex(new.id);
end /

insert into table1 set name='Ronnie';

But, the problem is when i insert into table1, the primary key will not be added to table2. it will add 0 instead.

These are the tables:

    create table table1 (
    id          integer         not null auto_increment primary key,
    name        varchar(45)     not null
    );

create table table2 (
    id          integer         not null auto_increment primary key,
    table1ID    varchar(45)     not null
    );

Upvotes: 1

Views: 56

Answers (1)

fthiella
fthiella

Reputation: 49049

Your trigger is "before insert", and the new generated id is not available yet. Change it to "after insert":

create trigger a after insert on table1 for each row 
begin
 insert into table2 (warehouseID) values (hex(new.id));
end /

Please see it here.

Upvotes: 1

Related Questions