Skeema Premium includes the ability to manage triggers. When first upgrading from the Community edition CLI to the Premium edition, be sure to run skeema pull one time to bring in any triggers to the filesystem.
By default, skeema init and skeema pull place trigger definitions into the same .sql file as their corresponding table, after the table’s definition. Note that the order of triggers in the .sql file is relevant, since there can be multiple triggers on the same table/event/timing combination. Skeema will respect the order within the .sql file for all trigger creation and diff logic.
Take care when using external online schema change tools on tables that already have triggers. For example, pt-online-schema-change has a --preserve-triggers option that can help with this, but it has some important technical implications.
Options
The ignore-trigger regex option may be used to force Skeema to ignore particular triggers (e.g. those managed by pt-online-schema-change) or even all triggers.
If a table is ignored by ignore-table, any triggers associated with that table are automatically ignored as well.
If you wish to restrict or ban triggers in your environment, see lint-has-trigger.
Several options relate to DEFINER user clauses. These options affect triggers, as well as other types of stored objects. See definer management for more information.
Privileges
In order to manage triggers, Skeema’s database user needs the TRIGGER privilege on all relevant databases. See the privileges section of the requirements documentation.
If your database server has binary logging enabled, SUPER privileges may be necessary for Skeema’s database user in order to manipulate triggers, unless you have enabled the global server variable log_bin_trust_function_creators. This requirement also affects functions, but not stored procedures.
If you wish to manage triggers that have a different DEFINER than Skeema’s database user, be sure to read about DEFINER stripping in the definer management documentation.
Manipulating triggers
Locking and atomic trigger replacement
In Skeema v1.11+, whenever a skeema push operation involves execution of multiple trigger DDL statements in the same schema, Skeema will automatically use LOCK TABLES briefly on all parent tables of the modified triggers. This is to ensure that no writes can occur in between the various trigger-related DDL statements. The tables will be unlocked immediately upon completion or failure of all trigger-related DDL. Generally this lock only needs to be held for a split-second.
With this automatic locking behavior, Skeema uses a very low lock_wait_timeout for the session. This prevents disruption from a long metadata lock wait, in case a long-running transaction is already affecting any of the parent tables. In this case, you should re-try the skeema push after long-running transactions have completed.
As always, skeema diff can be used to preview this locking behavior without actually running anything.
Use of ddl-wrapper disables this locking behavior, since locks are per-session and Skeema cannot share a session with the external wrapper tool.
If the multiple trigger DDL statements all only consist of DROP TRIGGER statements (with no CREATEs mixed in), locking is skipped, since it is rarely beneficial when only removing triggers.
Modifying an existing trigger
When modifying an existing trigger, the behavior depends on the database server vendor (MySQL vs MariaDB) as well as the Skeema version:
- MySQL does not support
CREATE OR REPLACEfor atomic modifications to existing triggers, so Skeema must emit aDROPfollowed by a re-CREATE. This means there is normally some risk of writes occurring during the brief moment between the two statements, when the trigger does not exist.- Skeema v1.11+ uses table locking to prevent these writes (see above), automatically making the operation safe in MySQL.
- In prior versions of Skeema, modification of triggers in MySQL is simply considered an unsafe action, requiring use of the allow-unsafe option to proceed.
- MariaDB supports
CREATE OR REPLACEto atomically alter an existing trigger, as long as the trigger remains associated with the same table as before.- Skeema will use this whenever possible, allowing the trigger to be modified atomically, which is safe even without locking.
- Skeema v1.11+ will still use table locking in MariaDB when creating or replacing multiple triggers in the same operation, to ensure data consistency during complex multi-trigger refactors.
By default, skeema diff and skeema push do not examine the creation-time sql_mode or db_collation associated with a trigger. To add these comparisons, use the compare-metadata option.
Dropping a trigger
Dropping a trigger is considered a destructive action, requiring use of the allow-unsafe option. This helps to prevent accidental/unintended trigger drops. Although triggers can be re-created quickly in this situation, there is no way to reapply them to any writes that occurred while the trigger was missing.