CREATE TRIGGER FOR { STARTUP | SHUTDOWN }

To create a system trigger on start up/shut down the following statement should be executed:

CREATE TRIGGER [IF NOT EXISTS] trigger_name AFTER STARTUP trigger_body

CREATE TRIGGER [IF NOT EXISTS] trigger_name BEFORE SHUTDOWN trigger_body

To drop a system trigger, the regular DROP TRIGGER statement is used.
The SUPER privilege is required to create or drop a system trigger.
A system trigger is created in the schema that is current at the moment the CREATE TRIGGER statement executed.

Metadata of system trigger is stored at the table mysql.event. To store system trigger metadata this table is extended with the following columns:
kind, when, ddl_type
For real event the column kind has the value 'SCHEDULE', for on startup and on shutdown the values are 'STARTUP', 'SHUTDOWN' correspondingly.
The table mysql.event has the primary on columns (`db`,`name`). In result, it is not possible to create an event and a system trigger with name in the same database. Attempt to do it will fail with the error ER_TRG_EVENT_CONFLICTS_NAME

Implementation of the statements

SHOW TRIGGERS

SHOW CREATE TRIGGER

are modified to handle system triggers.

During server start up on startup/on shutdown system triggers are loaded by opening the table mysql.event and fetching every row for which the values in the column kind matches the values 'STARTUP', 'SHUTDOWN'. Based on retrieved data, instances of the class Sys_trigger are created and stored in lists accessed by two-dimensional array (sys_triggers) indexed by trigger time (BEFORE, AFTER) and kind (STARTUP, SHUTDOWN). This array is singleton by its nature. Access to the array is protected by the global mutex. After startup process finished, the AFTER STARTUP triggers loaded during server initiailization are executed. The same is for ON SHUTDOWN triggers: on receiving shutdown request, every trigger stored in the array sys_triggers for BEFORE/SHUTDOWN combination are executed.

The utility program mariadb-dump is patched to support dumping of system triggers.

In case server is started on the data dictionary with broken structure of the table mysql.event (for example, missed some columns required for storing trigger's metadata), running of server will be failed with error complaining on wrong structure of the table mysql.event.

To fix the issue, start server with the option --skip-grant-tables.

The another case is attempt to start up server on the data dictionary created by previous version of MariaDB server (without support of system triggers). Starting the server on previous version of the table mysql.event result in success running the server but with disabled feature for system triggers.