I have these two triggers, which trigger A inserts from table A to table B, and vice-versa :
DELIMITER //
CREATE TRIGGER insert_split
BEFORE INSERT ON user FOR EACH ROW
BEGIN
IF new.level_id = '1' THEN
INSERT INTO petugas (nama_petugas, email, username, tgl_lahir, no_hp, created_by, updated_by, created_at, updated_at)
VALUES (new.name, new.email, new.username, new.dob, new.phone, '1', '1', NOW(), NOW());
ELSEIF new.level_id = '2' THEN
INSERT INTO bidan (nama_bidan, email, username, tgl_lahir, no_hp, created_by, updated_by, created_at, updated_at)
VALUES (new.name, new.email, new.username, new.dob, new.phone, '1', '1', NOW(), NOW());
ELSE
INSERT INTO ibu (nama_ibu, email, username, tgl_lahir, no_hp, created_by, updated_by, created_at, updated_at)
VALUES (new.name, new.email, new.username, new.dob, new.phone, '1', '1', NOW(), NOW());
END IF;
END;
DELIMITER ;
and this second trigger
DELIMITER //
CREATE TRIGGER insert_user_ibu
BEFORE INSERT ON ibu FOR EACH ROW
BEGIN
INSERT into user (name, username, email, password, dob, phone, level_id, is_active,created_at, updated_at)
VALUES (new.nama_ibu, new.email, new.email, md5('qwerty'), new.tgl_lahir, new.no_hp, '3', '1', NOW(), NOW());
END; //
But after I create both trigger insert regardless before or after, I execute this :
insert into user(id, name, username, phone, dob, email, password, level_id, is_active, created_at, updated_at) values
('30', 'Janice', 'janice123@yahoo.com', '087769958123', '2001-01-01', 'janice123@yahoo.com', md5('admin123'), '3', '1', NOW(), NOW());
or this
insert into ibu(id, nama_ibu, tempat_lahir, tgl_lahir, gol_dar, pendidikan, pekerjaan, alamat, city_id, province_id, no_hp, email, created_by, updated_by, created_at, updated_at) values
('5', 'Jessica', '1201', '2000-01-01', 'O', 'Tidak Sekolah', 'Tidak Bekerja', 'Testing', '1201', '12', '081211223344', 'jessica123@gmail.com', '1', '1', NOW(), NOW());
it won't work, and show error :
1442 - Can't update table 'user' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
the trigger function means, I want to insert a record to table A, so in table B are inserted too, and vice-versa. But it shows error. Anybody have solution this? So, I can insert a record, from table A is inserted in table B too, and from table B is inserted in table A.