Routines and triggers

Stored procedures and functions

Procedures and functions live under Functions in the sidebar. Right-click one and choose Open routines editor to see and edit its definition.

The editor shows the routine's DDL as the server reports it. Save to replace the definition.

Writing routines in the SQL editor

You can also create routines from a normal SQL editor tab. The statement splitter is DELIMITER-aware, so a body full of semicolons executes as one statement rather than being chopped into fragments:

DELIMITER $$

CREATE PROCEDURE monthly_totals(IN year_in INT)
BEGIN
  SELECT MONTH(created_at) AS month, SUM(total) AS total
  FROM orders
  WHERE YEAR(created_at) = year_in
  GROUP BY MONTH(created_at);
END$$

DELIMITER ;

Without that handling, the ; after each inner statement would end the CREATE PROCEDURE early and the server would reject it — which is the usual reason a routine that works in the console fails in a GUI client.

Triggers

Right-click a database and choose Triggers to open the trigger editor for that database. It lists the triggers defined there and shows each one's DDL.

Triggers can be inspected, edited and dropped. As with views, a dropped trigger takes nothing else with it.

Triggers and bulk operations

A trigger fires per row. During a large import or data transfer that can turn a fast operation into a slow one, and it can also duplicate work the transfer is already doing. Data transfer has an Include triggers option precisely so you can decide.

Where routines show up elsewhere

• Data transfer can copy views, procedures and functions to another database — including to a different engine, where the dialect may need adjusting afterwards.
• Dump SQL file on a database includes routine definitions in the dump.