Feeds:
Posts
Comments

Archive for February, 2009

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…. :mrgreen:

Cheers,
😎
Man’z

Advertisements

Read Full Post »

Hai hai … friend … ada kasus dari temen nih, sempet keringetan karena gak ketemu jalan keluarnya, sehingga posting di forum.

Jadi sebagai bahan arsip ajah, saya arsipkan dan siapa tahu ada yang membutuhkannya.

Ini pure di MySQL :

DROP TABLE IF EXISTS `tnama`;
CREATE TABLE `tnama` (
`id` int(11) NOT NULL auto_increment,
`NIK` varchar(10) default NULL,
`Nama` varchar(10) default NULL,
`NoTelp` varchar(10) default NULL,
`Grup` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

— —————————-
— Records
— —————————-
INSERT INTO `tnama` VALUES (‘1’, ‘A’, ‘A’, ‘A’, ‘A’);
INSERT INTO `tnama` VALUES (‘2’, ‘A’, ‘A’, ‘A’, ‘B’);
INSERT INTO `tnama` VALUES (‘3’, ‘A’, ‘A’, ‘A’, ‘C’);
INSERT INTO `tnama` VALUES (‘4’, ‘B’, ‘B’, ‘B’, ‘A’);
INSERT INTO `tnama` VALUES (‘5’, ‘B’, ‘B’, ‘B’, ‘B’);
INSERT INTO `tnama` VALUES (‘6’, ‘C’, ‘C’, ‘C’, ‘A’);
INSERT INTO `tnama` VALUES (‘7’, ‘C’, ‘C’, ‘C’, ‘B’);

hasil dari select biasa adalah :

NIK Nama  NoTelp  Grup 
----------------------
A     A     A       A 
A     A     A       B 
A     A     A       C 
B     B     B       A 
B     B     B       B 
C     C     C       A 
C     C     C       B

Nah sekarang pengen hasilnya seperti ini :

NIK Nama  NoTelp  Grup 
-------------------------
A    A      A     A,B,C 
B    B      B     A,B 
C    C      C     A,B

Gimana tuh … sempet saya juga pusyink memikirkan nya , dan ternyata ketemu dengan menggunakan reserved word yang ada di MySQL :mrgreen:

Statement nya :

select nik, nama, telp, group_concat(grup)
from tnama
group by nik

Hasilnya …. sama dengan yang di-ingin-kan ….

Salam DBA,
😎
Man’z

Read Full Post »