0

hy, I want to create a trigger that can update old data, with prerequisites:

  • the new data date is between the start date and the end date

using a trigger query like this:

CREATE DEFINER = `manager`@`%` TRIGGER `insertcs`
BEFORE INSERT
ON `transaksiChangeShift` FOR EACH ROW
BEGIN

  IF NEW.dateEnd IS NULL THEN
    SET NEW.dateEnd = NEW.dateStart;
  END IF;

  UPDATE HRS_1.transaksiChangeShift cs
  SET cs.dateEnd = SUBDATE(NEW.dateStart, INTERVAL 1 DAY)
  WHERE cs.idx = (SELECT
      c1.idx
    FROM (SELECT
        cs.idx,
        cs.NIK,
        cs.dateStart,
        cs.dateEnd,
        cs.idST
      FROM transaksiChangeShift cs
      WHERE cs.idx != (SELECT
          MAX(s.idx)
        FROM transaksiChangeShift s)) c1
      JOIN (SELECT
          s.idx,
          s.NIK,
          s.dateStart,
          s.dateEnd,
          s.idST
        FROM transaksiChangeShift s
        ORDER BY s.idx DESC LIMIT 1) c2
        ON c1.NIK = c2.NIK
        AND c2.dateStart BETWEEN c1.dateStart AND c1.dateEnd);
END



after that I tried my trigger and an error appeared:

Can't update table 'transaksiChangeShift' in stored function/trigger because it is already used by statement
which invoked this stored function/trigger.
rejak
  • 23
  • 5
  • 2
    The message is very clear..you need a different approach perhaps an insert followed by an update in a transaction or sp – P.Salmon Nov 07 '22 at 09:08
  • 1
    If you need to alter the data in currently processed (inserted) row then you must use not UPDATE query but alter the values in NEW (like you do with NEW.dateEnd). If you need to alter the data in another rows of this table then you must use not trigger (such operation is not possible in it) but perform INSERT within stored procedure which implements needed logic. – Akina Nov 07 '22 at 09:24

0 Answers0