Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-38009

Flaws with CREATE TRIGGER with ORed events including UPDATE OF

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Trivial
    • Resolution: Unresolved
    • 12.2(EOL), 12.3, 12.0.1
    • 12.3
    • Server
    • None

    Description

      I have MariaDB 12.2.0.
      The manual for CREATE TRIGGER does not mention multiple ORed events
      https://mariadb.com/docs/server/server-usage/triggers-events/triggers/create-trigger
      but MDEV-10164 is closed.
      I say

      CREATE TABLE t1 (s1 INT, s2 INT);
      CREATE TRIGGER t1 BEFORE UPDATE OF s1 OR INSERT ON t1 FOR EACH ROW SET @a = @a + 1;
      CREATE TRIGGER t1 BEFORE INSERT OR UPDATE OF s1 ON t1 FOR EACH ROW SET @a = @a + 1;
      SELECT event_manipulation FROM information_schema.triggers WHERE trigger_name = 't1';
      

      The first CREATE TRIGGER statement fails.
      The second CREATE TRIGGER statement succeeds. This is inconsistent behaviour.
      The SELECT returns "INSERT,UPDATE". This is incomplete information.

      By the way, I intend to mention this feature (and any flaws) in a blog post soon.

      Update 2025-11-11: After comments below I think it's clear that the first complaint is valid, the second is not, but "INSERT,UPDATE" rather than "INSERT OR UPDATE" may be worth a look. The blog post that I mentioned is https://ocelot.ca/blog/blog/2025/11/11/mariadb-12-triggers/ and if the MariaDB docs person is free to copy anything in it that doesn't seem atrociously dumb.

      Attachments

        Issue Links

          Activity

            People

              shulga Dmitry Shulga
              Peter Gulutzan Peter Gulutzan
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.