Working with Stored Programs

Last modified: December 27, 2024

This topic describes how to work with stored programs, such as stored procedures, functions, triggers, and events in dbForge Studio 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. dbForge Studio supports events on MySQL servers starting from MySQL 5.1.11. dbForge Studio allows you to create, modify, and drop events.

Creating an Event

1. On the Standard toolbar, click New Database Object and specify the Event type in the dialog.

2. Input name of the event.

3. Click Create 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-

1. In Database Explorer, right-click the Events node and select New Event.

2. Input name of the event.

3. Type SQL statements that will represent the event body.

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.

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 dbForge Studio, 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 dialog box opened and click Create.

A new document appears with template in the Text view. Populate it with statements or switch to 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 from the shortcut menu.

-or-

Double-click a procedure.

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

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 dbForge Studio, you can create, edit and delete triggers.

Note

dbForge Studio 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.