Triggers and Events

Things that run without anybody asking them to.

Triggers fire in response to changes to a table. Events fire on a schedule. Both do work nobody explicitly requested, so it's worth knowing where to inspect them.

Triggers #

A table's triggers are listed in its Triggers section in the Structure view, and each can also be selected in the primary sidebar. The trigger body is edited like any other definition.

The Trigger Info section defines when it fires:

Property	Values
Timing	BEFORE, AFTER, or on PostgreSQL INSTEAD OF.
Events	INSERT, UPDATE, DELETE, and on PostgreSQL TRUNCATE. PostgreSQL triggers may fire on several events, shown as Multiple.
Granularity	FOR EACH ROW, and on PostgreSQL also statement-level.
Definer	The user whose privileges apply when the body runs.

Events (MySQL) #

A MySQL event has a definition (the statements it runs) and a schedule.

Field	What it sets
Schedule	Once for a single execution, or Every for a repeating one.
At / In	One-off events: an absolute time, or an interval from now. Now fills in the current time.
Every	The repeat interval, from seconds up through years, including combined units such as Days Hours:Minutes.
Starts, Ends	Optional bounds on a repeating schedule.
Delete when schedule ends	Drops the event once it can no longer fire (`ON COMPLETION NOT PRESERVE`). Worth knowing before you go looking for a finished event.
Status	Enabled, Disabled, or Disabled (On Slave). Disable an event to stop it firing without deleting it.
Definer	The user the event runs as.
Created, Updated, Last Executed	Timestamps, including when it actually last ran.

To set up a nightly job

Create the event. See Creating Databases, Tables, etc.
Set Schedule to Every, with an interval of 1 day and a Starts time at the hour you want.
Write the statements in the definition editor and save.
Confirm the event scheduler is enabled.
The next day, check Last Executed. That's the only real confirmation.