Skip to main content

Create MySQL non-table objects visually

In addition to databases and tables, MySQL supports non-table objects such as views, stored procedures, functions, triggers, and events. NineData SQL Console provides visual editors for creating and editing these objects.

Overview​

Non-table objects such as views, stored procedures, functions, triggers, and events are core MySQL objects. Creating them manually often requires complex SQL, which can slow down developers, DBAs, and business users who make controlled database changes.

NineData provides visual editors for these objects. Configure object properties in the console, review the generated SQL, and submit the change without writing the full statement from scratch.

Before you begin​

  • The data source type is MySQL.

  • In organization mode, you have permission to use SQL Console.

  • In organization mode, you have read and write permissions for the target data source.

    tip

    If you do not have these permissions, contact your organization administrator to configure permissions or bind roles.

  • In the commercial versions (DevOps Pro, DevOps Enterprise), make sure your monthly or yearly subscription has not expired. An expired subscription may prevent you from using the Database DevOps service. Check the remaining quota and expiration date in the NineData Console header.

    check_quota

Procedure​

Create a view​

  1. Open SQL Console and go to the target data source.

  2. In the database list in SQL Console, right-click the target database and click Create Object > Create View.

    tip
    • NineData creates the view in this database.
    • Alternatively, expand the target database, right-click the Views directory, and then click Create View.

  3. On the Create View tab, configure the view. NineData generates Create Statement at the bottom of the page based on your configuration. Use the table to configure each parameter.

    ParameterDescription
    View NameCustom name for the view, default is new_view.
    DatabaseSelect the database in which to create the view, default is the database right-clicked in step 2.
    DefinerSpecify the definer of the current view. If empty, the default definer is the current user.
    SQL SecurityChoose the permission to be used when the view is called.
    • Default: Not specified.
    • Definer: Use the permissions of the current view definer for querying.
    • Invoker: Use the permissions of the view caller for querying.
    Check OptionAdd the Check Option. Default means no option is added. For more information, see official documentation.
    ALGORITHMAdd the Algorithm option. Default means no option is added. For more information, see official documentation.
    REPLACECheck this option to replace an existing view with the same name in the current database.

  4. In the editor below Create Statement, enter the SQL statement that the view executes.

  5. Click Save, confirm the generated SQL statement in the Submit window, and then click OK.

Create a stored procedure​

  1. Open SQL Console and go to the target data source.

  2. In the SQL Console database list, right-click the target database, and click Create Object > Create Procedure.

    tip
    • NineData creates the stored procedure in this database.
    • Alternatively, expand the target database, right-click the Procedures directory, and then click Create Procedure.

  3. On the Create Procedure tab, configure the stored procedure. NineData generates Create Statement at the bottom of the page based on your configuration. Use the table to configure each parameter.

    ParameterDescription
    NameCustom name for the stored procedure, default is new_procedure.
    DatabaseSelect the database in which to create the stored procedure, default is the database right-clicked in step 2.
    DefinerSpecify the definer of the current stored procedure. If empty, the default definer is the current user.
    DeterministicSpecify whether the execution result of the stored procedure is deterministic.
    • Default: Not specified.
    • DETERMINISTIC: Deterministic, meaning it returns the same result for the same input parameters.
    • NOT DETERMINISTIC: Not deterministic, meaning it may return different results for the same input parameters, such as in random number generation or timestamp operations.
    SQL PropertyChoose the execution mode of the stored procedure:
    • Default: Not specified.
    • CONTAINS SQL: The stored procedure contains SQL statements but does not read or modify data.
    • NO SQL: The stored procedure does not contain SQL statements.
    • READS SQL DATA: The stored procedure contains SQL statements that read data but do not modify it.
    • MODIFIES SQL DATA: The stored procedure contains SQL statements that modify data.
    SQL SecurityChoose the permissions to be used when the stored procedure is called.
    • Default: Not specified.
    • Definer: Use the permissions of the current stored procedure definer.
    • Invoker: Use the permissions of the stored procedure caller.
    CommentComment content for the stored procedure.
    ParametersSubstitute parameters for the stored procedure.

  4. Below BEGIN in the Create Statement editor, enter the actual content of the stored procedure.

  5. Click Save, confirm the generated SQL statement in the Submit window, and then click OK.

Create a function​

  1. Open SQL Console and go to the target data source.

  2. In the SQL Console database list, right-click the target database, and click Create Object > Create Function.

    tip
    • NineData creates the function in this database.
    • Alternatively, expand the target database, right-click the Functions directory, and then click Create Function.

  3. On the Create Function tab, configure the function. NineData generates Create Statement at the bottom of the page based on your configuration. Use the table to configure each parameter.

    ParameterDescription
    Function NameCustom name for the function, default is new_function.
    DatabaseSelect the database in which to create the function, default is the database right-clicked in step 2.
    DefinerSpecify the definer of the current function. If empty, the default definer is the current user.
    DeterministicSpecify whether the execution result of the function is deterministic.
    • DETERMINISTIC: Deterministic, meaning it returns the same result for the same input parameters.
    • NOT DETERMINISTIC: Not deterministic, meaning it may return different results for the same input parameters, such as in random number generation or timestamp operations.
    ReturnsChoose the data type that the function returns.
    SQL SecurityChoose the permissions to be used when the function is called.
    • Default: Not specified.
    • Definer: Use the permissions of the current function definer.
    • Invoker: Use the permissions of the function caller.
    CommentComment content for the function.
    ParametersSubstitute parameters for the function.

  4. Below BEGIN in the Create Statement editor, enter the actual content of the function.

  5. Click Save, confirm the generated SQL statement in the Submit window, and then click OK.

Create a trigger​

  1. Open SQL Console and go to the target data source.

  2. In the SQL Console database list, right-click the target database, and click Create Object > Create Trigger.

    tip
    • NineData creates the trigger in this database.
    • Alternatively, expand the target database, right-click the Triggers directory, and then click Create Trigger.

  3. On the Create Trigger tab, configure the trigger. NineData generates Create Statement at the bottom of the page based on your configuration. Use the table to configure each parameter.

    ParameterDescription
    Trigger NameCustom name for the trigger, default is new_trigger.
    Database/TableSelect the database and the target table to monitor, default is the database right-clicked in step 2.
    DefinerSpecify the definer of the current trigger. If empty, the default definer is the current user.
    Trigger OrderIf triggers already exist in the target table, choose the priority:
    • Default: No specific priority. Triggers run in creation order.
    • FOLLOWS: Give precedence to existing triggers. Select the existing triggers on the right.
    • PROCEDES: Give precedence to the current trigger. Select the existing triggers on the right.
    Trigger TimeChoose when the trigger runs:
    • BEFORE: Run the trigger before the monitored event occurs. This is mainly used to prevent invalid changes or prepare data.
    • AFTER: Run the trigger after the monitored event occurs.
    Trigger EventChoose the operation that triggers the action:
    • INSERT: Insert operation.
    • UPDATE: Update operation.
    • DELETE: Delete operation.

  4. In the white area of the Create Statement editor, enter the actual content of the trigger.

  5. Click Save, confirm the generated SQL statement in the Submit window, and then click OK.

Create an event​

  1. Open SQL Console and go to the target data source.

  2. In the SQL Console database list, right-click the target database, and click Create Object > Create Event.

    tip
    • NineData creates the event in this database.
    • Alternatively, expand the target database, right-click the Events directory, and then click Create Event.

  3. On the Create Event tab, configure the event. NineData generates Create Statement at the bottom of the page based on your configuration. Use the table to configure each parameter.

    ParameterDescription
    Event NameCustom name for the event, default is new_event.
    DatabaseSelect the database in which to create the event, default is the database right-clicked in step 2.
    DefinerSpecify the definer of the current event. If empty, the default definer is the current user.
    Delete after ExecControls whether the event is deleted after it completes:
    • Default: Not specified.
    • ON COMPLETION PRESERVE: Preserve the event after it completes.
    • ON COMPLETION NOT PRESERVE: Delete the event after it completes.
    Event StatusChoose the status of the event:
    • Default: Not specified.
    • ENABLE: Enable the event.
    • DISABLE: Store the event but do not activate it.
    • DISABLE ON SLAVE: The event is not active on the slave.
    Excuted TimeSpecify the execution frequency and time of the event:
    • One Time: Execute only once, and specify the specific execution time.
    • Recurring: Recurring execution, and specify the execution cycle, as well as the start and end time of the cycle.
    CommentComment content for the event.

  4. In the white area of the Create Statement editor, enter the actual content of the event.

  5. Click Save, confirm the generated SQL statement in the Submit window, and then click OK.

Manage non-table objects​

Perform visual creation, editing, renaming, copying, and deletion operations on MySQL non-table objects in SQL Console.

  1. Open SQL Console and go to the target data source.
  2. Right-click the name of the non-table object under the target database and choose the corresponding operation.

Result​

NineData creates or updates the selected MySQL non-table object and displays the generated SQL for review before the change is submitted.

Next steps​

  • Review the generated SQL before applying the change in production.
  • Open the object in SQL Console to confirm that the definition matches your intended design.