0

I have a MariaDB 10.5 database with a BEFORE UPDATE trigger that is getting triggered after a BEFORE INSERT trigger which is causing problems for me.

Is there a way I can prevent the BEFORE UPDATE trigger from triggering after the BEFORE INSERT trigger?

Here are both triggers:

CREATE TRIGGER HASH_NAMES_BEFORE_INSERT
BEFORE INSERT ON items
FOR EACH ROW SET NEW.name = SHA2(NEW.name, 256);

CREATE TRIGGER HASH_NAMES_BEFORE_UPDATE
BEFORE UPDATE ON items
FOR EACH ROW SET NEW.name = SHA2(NEW.name, 256);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Abdelfattah Radwan
  • 431
  • 8
  • 20
  • Triggers fire when update or insert events occur so what are you doing? – P.Salmon Jun 02 '22 at 19:42
  • @P.Salmon I know but the update trigger fires when I do an insert. I want to make it so it only fires when I perform an update and not an update/insert. – Abdelfattah Radwan Jun 02 '22 at 20:22
  • Can you show an example of the INSERT that causes the UPDATE trigger to fire? This can happen if you use INSERT...ON DUPLICATE KEY UPDATE, but otherwise not. – Bill Karwin Jun 02 '22 at 22:11
  • @BillKarwin the insert query looks something like this: `INSERT INTO items (name, desc, value) VALUES ("Item 1", "An example item.", 50)` – Abdelfattah Radwan Jun 03 '22 at 08:30
  • That insert will not cause both triggers to fire, there must be something else happening. – P.Salmon Jun 03 '22 at 10:26
  • Why do you think both triggers are firing? – P.Salmon Jun 03 '22 at 10:41
  • 1
    By the way, 10.5 is a MariaDB version, not MySQL. MariaDB is a different product from MySQL, even though MariaDB started as a fork of MySQL in 2010, both products have been changing since then. You should not think of them as compatible anymore. – Bill Karwin Jun 03 '22 at 15:05
  • @P.Salmon actions of both triggers are performed when doing an insert. – Abdelfattah Radwan Jun 03 '22 at 22:58
  • 1
    If you add a write to a debug table and fix the code errors there it's clear that your assertion is incorrect https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=993d88d60c617360e5b2942fc4b2baad – P.Salmon Jun 04 '22 at 06:32
  • @P.Salmon that was it! Thanks a lot for your comment & examples! I suggest that you convert your comment to an answer. – Abdelfattah Radwan Jun 04 '22 at 07:00

0 Answers0