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

"follows" does not show up in "show create trigger" output

    XMLWordPrintable

Details

    Description

      Problem

      The output of "show create trigger" does not include the "follows" keyword defined in the create trigger statement.

      Reproduce

      create database if not exists test;
       
      use test
       
      create table table_a (id int(10) not null,
                            value varchar(20) not null, 
                            created datetime(6),
                            updated datetime(6)
      );
       
      create table table_a_audit(id int(10) not null,
                                 ts datetime(6),
                                 old_value varchar(20),
                                 new_value varchar(20)
      );
       
      delimiter $$
      create or replace trigger table_a_trig_1
      before insert on table_a
      for each row
      begin
        set new.created = current_timestamp(6);
        set new.updated = current_timestamp(6);
      end$$
       
      create or replace trigger table_a_trig_2
      before insert on table_a
      for each row
      follows table_a_trig_1
      begin
        insert into table_a_audit (id, ts, new_value)
          value (new.id, current_timestamp(6), new.value);
      end$$
       
      delimiter ;
       
      insert into table_a (id, value) values (1, "foo");
      commit;
       
      select * from table_a;
       
      select * from table_a_audit;
       
      show create table table_a;
       
      show create trigger table_a_trig_1;
       
      show create trigger table_a_trig_2;
      

      The output of this last statement is incorrect, in build-11611 on bb-10.2-compatibility branch you get the following output

      MariaDB [test]> show create trigger table_a_trig_2;
      +----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
      | Trigger        | sql_mode                                   | SQL Original Statement                                                                                                                                                                                        | character_set_client | collation_connection | Database Collation | Created                |
      +----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
      | table_a_trig_2 | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` trigger table_a_trig_2
      before insert on table_a
      for each row
      begin
        insert into table_a_audit (id, ts, new_value)
          value (new.id, current_timestamp(6), new.value);
      end | latin1               | latin1_swedish_ci    | latin1_swedish_ci  | 2016-10-11 17:29:58.31 |
      +----------------+--------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+------------------------+
      1 row in set (0.00 sec)
      

      Solution

      The :show create trigger" should include the full trigger definition.

      Attachments

        Issue Links

          Activity

            People

              monty Michael Widenius
              alvinr Alvin Richards (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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