Working with Stored Programs

This topic describes working with stored programs are stored procedures and functions, triggers and events in dbForge Fusion for MySQL.

Events

Event is a task that runs according to schedule. An event’s action statement may include most SQL statements permitted within stored routines. Fusion for MySQL supports events on MySQL servers starting from MySQL 5.1.11. Fusion for MySQL allows you to create, modify, and drop events.

Creating an Event

  1. On the Fusion menu, select MySQL, click New Database Object and specify the Event type in the dialog box.
  2. Input name of the event.
  3. Click the Create button and, if specified name is unique, the document of this event will appear. Note that views share the namespace with tables.
  4. Select Enable, Recurrent and Drop on Completion options to set event state and schedule.
  5. Optionally, you can alter the name or change database (owner) in which the new event will be created.
  6. Go to the Timing tab and set the event schedule.

  7. Save the document. If there are any errors in the SQL syntax, you will be notified about it.

    -or-

  8. In Database Explorer, right-click the Events node and select New Event.
  9. Input name of the event.
  10. Type SQL statements that will represent the event body.
  11. Select Enable, Recurrent and Drop on Completion options to set event state and schedule.
  12. Optionally, you can alter the name or change database (owner) in which the new event will be created.
  13. Go to the Timing tab and set the event schedule.
  14. Save the document. If there are any errors in the SQL syntax, you will be notified about it.

Editing an Event

Edit event by selecting Edit Event on the Database Explorer node shortcut menu. Note that event can not be saved if the start day of the event already passed. Set it to a correct value (current or future time) to save the event.

Dropping an Event

Drop event by selecting Delete on the Database Explorer node shortcut menu.

Stored Procedures and Functions

Stored procedure (function) is a set of statements that resides on server side and can be executed. Using Fusion for MySQL, you can create, edit, execute and delete stored procedures.

Creating a Stored Procedure

  1. In Database Explorer, navigate to the Procedures node.
  2. Select New Procedure on the shortcut menu.
  3. Provide procedure name in the dialog box opened and click Create.

A new document appears with template in the Text view. Fill it with statements or switch to the Design view to edit procedure properties. Save the document to finish procedure creation.

Editing a Stored Procedure

To edit a stored procedure:

  1. In Database Explorer, navigate to the Procedures node and expand it.

  2. Right-click a procedure and select Open on the shortcut menu.

    • or -

    double-click a procedure.

    You can switch to the Design view to edit some properties of the procedure.

    Editing a procedure in the Text View

    Editing a procedure in the Design View

Executing a Stored Procedure

Right-click a stored procedure in Database Explorer and select Execute.

The stored procedure editor opens if it was not opened before. If the procedure accepts parameters, you will see the Parameters dialog box where you can assign initial values for procedure arguments. Once the stored procedure is executed, you will see a corresponding record in the Output window. The data returned by the stored procedure will appear in Data view of the stored procedure editor.

Stopping Stored Procedure Execution

Click the Stop Execution button on the toolbar.

Deleting a Stored Procedure

Right-click a stored procedure in Database Explorer, and select Delete.

Note

You can treat stored functions exactly the same way as stored procedures.

Triggers

Trigger is a routine executed under certain conditions.

Using Fusion for MySQL, you can create, edit and delete triggers.

Note

Fusion for MySQL supports triggers only with MySQL server 5.0.13 and higher.

Creating a Trigger

  1. In Database Explorer, navigate to the Triggers node.
  2. Select New Trigger on the shortcut menu. A new document appears with template in the Design view.
  3. Provide trigger name. Optionally, you can change schema for the object.
  4. Specify trigger body.
  5. Switch to the Event tab and specify the event to fire the trigger.
  6. Save the document.

Editing a Trigger

  1. In Database Explorer, navigate to the Triggers node and expand it.
  2. Right-click a trigger and select Edit Trigger on the shortcut menu.

The Trigger Editor has two tabs: Main and Event. In the Main tab, you can choose trigger type and fill its body, as well as switch the trigger on or off. In the Event tab you can set up event that should execute the trigger.

Deleting a Trigger

Select Delete on the trigger shortcut menu.