MySQL Trick for auto-key on trigger

Dear All,

Once again, MySQL make me headache to handle un-linked key that consist at temporary tables and this two table is manipulate by record-form model.

This is a scenario :

We’ve 2 table — simple table that want to inserting the key for linked between that tables, that the key from master table is generated from trigger.

for example :

create table tbl_a
(
  uid varchar(40) default '-',
  name varchar(50),
  constraint pk_tbl_a primary key (uid)
);

create table tbl_b
(
  uid varchar(40),
  qty double precision default 0,
  description varchar(100),
  constraint fk_tbl_b foreign key (uid)
    references tbl_a (uid)
    on update cascade on delete cascade
);

now, we create the trigger for this table a.

create trigger trig_tbl_a before insert on tbl_a for each row
set new.uid = uuid();

but, how to insert the tbl_b.uid ? if we use simple insert statement … ?

After we long discussed with our best friends … Now, we have one good idea.

This the resolving the problem :

drop and re-create the trigger at table a :

drop trigger trig_tbl_a;
create trigger trig_tbl_a before insert on tbl_a for each row
begin
  set new.uid = uuid();
  create temporary table if not exists t_uid(uid varchar(40));
  delete from t_uid;
  insert into t_uid values (new.uid);
end;

then, create trigger for table b:

create trigger trig_tbl_b before insert on tbl_b for each row
set new.uid = (select uid from t_uid);

after that, we can relate the data on table b using the uid key.

try this :

mysql> insert into tbl_a (name) values ('Manz');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> insert into tbl_b (qty, description) values (10, 'Movie ordered with DVD original version');
Query OK, 1 row affected (0.03 sec)

mysql> select * from tbl_a;
+--------------------------------------+------+
| uid                                  | name |
+--------------------------------------+------+
| 4b5a8c27-0321-11de-88cd-184e3e4ece1f | Manz |
+--------------------------------------+------+
1 row in set (0.02 sec)

mysql> select * from tbl_b;
+--------------------------------------+------+-----------------------------------------+
| uid                                  | qty  | description                             |
+--------------------------------------+------+-----------------------------------------+
| 4b5a8c27-0321-11de-88cd-184e3e4ece1f |   10 | Movie ordered with DVD original version |
+--------------------------------------+------+-----------------------------------------+
1 row in set (0.00 sec)

Now, we can use the code to update my uid. And result is my headache is gone….

Cheers,

Man’z