Details

    Description

      Add support for TRIGGER events that can fire for one or many of
      INSERT, UPDATE and DELETE

      The current syntax for triggers are:

      CREATE [OR REPLACE]
          [DEFINER = { user | CURRENT_USER }]
          TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
          ON tbl_name FOR EACH ROW trigger_stmt
      

      The task is to replace trigger_event with:

       { event [ OR ... ] }
      

      Oracle and PostgreSQL both support this extension to CREATE TRIGGER.

      PostgreSQL solves the issue witth information schema by duplicating the trigger for as many times as there are events.The primary key of the view triggers is because of this trigger_catalog, trigger_schema, event_object_table, trigger_name, event.

      To distinguish which event fired the trigger, Oracle supports event flags:

      CREATE TRIGGER tr1
      BEFORE DELETE OR INSERT OR UPDATE ON t1
      FOR EACH ROW
      BEGIN
        IF INSERTING THEN   ...
        ELSIF DELETING THEN ...
        ELSIF UPDATING THEN ...
        ENDIF;
      END;
      

      We should also support this.

      Attachments

        Issue Links

          Activity

            how to show that in INFORMATION_SCHEMA.TRIGGERS?

            serg Sergei Golubchik added a comment - how to show that in INFORMATION_SCHEMA.TRIGGERS ?

            I've attached PoC in patch.diff which seem to work (still needs to add INSERTING / DELETING / UPDATING) keywords to parser

            anikitin Andrii Nikitin (Inactive) added a comment - I've attached PoC in patch.diff which seem to work (still needs to add INSERTING / DELETING / UPDATING) keywords to parser
            shulga Dmitry Shulga added a comment -

            The branch for review is bb-11.8-MDEV-10164
            (actually the branch is re-based on top of the 12.0 branch)

            shulga Dmitry Shulga added a comment - The branch for review is bb-11.8- MDEV-10164 (actually the branch is re-based on top of the 12.0 branch)

            People

              sanja Oleksandr Byelkin
              monty Michael Widenius
              Votes:
              3 Vote for this issue
              Watchers:
              7 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.