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

CREATE OR REPLACE TRIGGER produces ER_TRG_DOES_NOT_EXIST

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
    • 10.11
    • Triggers
    • None

    Description

      create table t1 (i int);
      		 
      create table t2 (i int);
      		 
       
      		 
      --echo # This works OK
      		 
      create or replace trigger tr after insert on t1 for each row set @a=1;
      		 
      --echo # This also works OK
      		 
      create or replace trigger tr after insert on t1 for each row set @a=1;
      		 
      --echo # This fails
      		 
      create or replace trigger tr after insert on t2 for each row set @a=1;
      		 
       
      		 
      drop table t1, t2;


      MariaDB [test]> create or replace trigger tr after insert on t2 for each row set @a=1;
      		 
      ERROR 1360 (HY000): Trigger does not exist

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-7286 TRIGGER: CREATE OR REPLACE, CREATE IF NOT EXISTS

          • Major - Major loss of function.
          • Closed

          Activity

            monty Michael Widenius added a comment -

            I talked with Sergei and we agreed that create or replace should work even if the original trigger was for another table.

            This is best to be done in 10.6 where we can use the atomic ddl code to make this atomic.

            In case of 'create or replace trigger' for a trigger that was originally for another table,
            the code should work as follows:

            • Take a ddl lock for the trigger name (Already done in 10.5 as part of MDEV-25606).
            • Execute 'drop trigger' for the original trigger. However, do not close the ddl log.
            • Execute create trigger for the new table.
            • If something goes wrong, use the ddl_log_revert() to restore the original trigger.
            monty Michael Widenius added a comment - I talked with Sergei and we agreed that create or replace should work even if the original trigger was for another table. This is best to be done in 10.6 where we can use the atomic ddl code to make this atomic. In case of 'create or replace trigger' for a trigger that was originally for another table, the code should work as follows: Take a ddl lock for the trigger name (Already done in 10.5 as part of MDEV-25606 ). Execute 'drop trigger' for the original trigger. However, do not close the ddl log. Execute create trigger for the new table. If something goes wrong, use the ddl_log_revert() to restore the original trigger.

            People

              sanja Oleksandr Byelkin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.