0

i want to move my current data to another table when the update value is "jakarta"



IF NEW.alamat = "jakarta" THEN
    INSERT INTO detail_mhs_copy1
    SELECT * FROM detail_mhs WHERE id_mhs = NEW.id_mhs;
    
    DELETE FROM detail_mhs WHERE id_mhs = OLD.id_mhs;
END IF;

but with this trigger its make error like this
1442 - Can't update table 'detail_mhs' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

any solutions?

Rizal Iman Muttaqin
  • 33
  • 4
  • 1
    Non-solvable in trigger format. You may copy the rows to the "another table" and mark current record as one which must be deleted (by setting some special value in some column). The deletion itself must be performed by event procedure. – Akina Apr 13 '21 at 05:10
  • 1
    Alternatively you may prohibit direct data access and use stored procedure only. This allows to realize any complex logic in SP. – Akina Apr 13 '21 at 05:18
  • Another problem is i dont have acces to changes the action update – Rizal Iman Muttaqin Apr 13 '21 at 05:23
  • If so then trigger + event seems to be the only option. – Akina Apr 13 '21 at 05:25
  • What, exactly, do you need done? Perhaps 2 things: (1) copy OLD row to the other table and (2) allow the current table to be updated. Or did you want the current row not to be changed? Or maybe delete the current row? – Rick James May 12 '21 at 23:33

1 Answers1

0

Please try below trigger and change col1 & col2 with your column's name and change primary key with column name which have primary key and not updating.

DELIMITER $$

CREATE TRIGGER detail_mhs_trig AFTER UPDATE ON detail_mhs FOR EACH ROW BEGIN IF NEW.alamat = "jakarta" THEN INSERT INTO detail_mhs_copy1(col1,col2, alamat) VALUES(old.col1, old.col2, new.alamat); END IF; END$$

DELIMITER ;

ROHIT KHURANA
  • 903
  • 7
  • 13
  • I think its not solved my problem for deleteing current row in detail_mhs – Rizal Iman Muttaqin Apr 13 '21 at 05:21
  • 1
    you can't achieve this in single trigger which means, trigger can't change same table. Please have a look on below link and find solution https://stackoverflow.com/questions/22342853/update-table-column-after-insert-new-record-using-mysql-triggers – ROHIT KHURANA Apr 13 '21 at 08:15