mysql error 1442 trigger when inserting a new record from table A to table B and vice versa

Viewed 30

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.

0 Answers
Related