MySQL Internals Manual  /  ...  /  Table Trigger Storage

16.2.2 Table Trigger Storage

Information for a given trigger is stored in plain text files in the database directory that contains the table associated with the trigger:

  • There is one .TRN file per trigger. It indicates the table associated with the trigger. The design decision that every trigger has a dedicated .TRN file is used to facilitate operating system filesystem services to enforce the SQL standard requirement that all triggers in a given schema must be unique.

  • There is one .TRG per table that has triggers. It contains information about all the triggers for the table.

Suppose that we create a table named account and associate with it three triggers named ins_transaction, ins_sum, and upd_check:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
    -> FOR EACH ROW PRECEDES ins_sum
    -> SET
    -> @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
    -> @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;
Query OK, 0 rows affected (0.03 sec)

In this case, there are three .TRN files named after the triggers (ins_transaction.TRN, ins_sum.TRN, upd_check.TRN), and a .TRG file named after the table (account.TRG). table.

A .TRN file is specific to a trigger and contains these fields:

  • TYPE: Indicates what the file represents. For a .TRN file, this value is always TRIGGERNAME, which means that the file name corresponds to the trigger name. For example, a .TRN file named ins_sum.TRN corresponds to a trigger named ins_sum.

  • trigger_table: The table associated with the trigger.

Example .TRN file:

TYPE=TRIGGERNAME
trigger_table=account

A .TRG file is specific to a table and contains these fields:

  • TYPE: Indicates what the file represents. For a .TRN file, this value is always TRIGGERS, which means that the file contains information about the triggers associated with the table that corresponds to the file name. For example, a file named account.TRG contains information for the triggers associated with the account table. trigger definitions and related information.

  • triggers: The CREATE TRIGGER statements for the triggers associated with the table. Triggers that have the same trigger event and action time are listed in activation order.

  • sql_modes: For each trigger, the sql_mode value under which the trigger executes, as a numeric value.

  • definers: For each trigger, the account of the user who created it, in 'user_name'@'host_name' format.

  • client_cs_names: For each trigger, the session value of the character_set_client system variable when the trigger was created.

  • connection_cl_names: For each trigger, the session value of the collation_connection system variable when the trigger was created.

  • db_cl_names: For each trigger, the collation of the database with which the trigger is associated.

  • created: For each trigger, its creation time. This field is present only if triggers have been created or dropped for the table as of MySQL 5.7.2 or later. A value of 0 means that the trigger was created before 5.7.2 and thus that no creation time is known. (Creation time values are not maintained until 5.7.2.)

Example .TRG file:

TYPE=TRIGGERS
triggers='CREATE DEFINER=`me`@`localhost` TRIGGER ins_transaction
BEFORE INSERT ON account\nFOR EACH ROW SET\n@deposits = @deposits
+ IF(NEW.amount>0,NEW.amount,0),\n@withdrawals = @withdrawals +
IF(NEW.amount<0,-NEW.amount,0)' 'CREATE DEFINER=`me`@`localhost`
TRIGGER ins_sum BEFORE INSERT ON account\nFOR EACH ROW SET @sum =
@sum + NEW.amount' 'CREATE DEFINER=`me`@`localhost` TRIGGER upd_check
BEFORE UPDATE ON account\nFOR EACH ROW\nBEGIN\n    IF NEW.amount <
0 THEN\n        SET NEW.amount = 0;\n    ELSEIF NEW.amount > 100
THEN\n        SET NEW.amount = 100;\n    END IF;\nEND'
sql_modes=1073741824 1073741824 1073741824
definers='me@localhost' 'me@localhost' 'me@localhost'
client_cs_names='utf8' 'utf8' 'utf8'
connection_cl_names='utf8_general_ci' 'utf8_general_ci' 'utf8_general_ci'
db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci' 'latin1_swedish_ci'
created=137339041018 137339026087 137339063431

To convert a value on the sql_modes line to more readable form, do this:

mysql> SET sql_mode = 1073741824;
mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

To break a value on the created line into integer and fractional seconds parts and convert them to an ISO-format date and time value, do this:

mysql> SET @ts_int = FLOOR(137339041018/100);
Query OK, 0 rows affected (0.01 sec)

mysql> SET @ts_frac = 137339041018 % 100;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(FROM_UNIXTIME(@ts_int),'.',@ts_frac);
+---------------------------------------------+
| CONCAT(FROM_UNIXTIME(@ts_int),'.',@ts_frac) |
+---------------------------------------------+
| 2013-07-09 12:20:10.18                      |
+---------------------------------------------+
1 row in set (0.00 sec)

The code used to encapsulate file access is:

  • Table_triggers_list::create_trigger()

  • Table_triggers_list::drop_trigger()

  • Table_triggers_list::check_n_load()

  • Table_triggers_list::drop_all_triggers()

  • Table_triggers_list::change_table_name()

  • See the C++ class Table_triggers_list in general.

Using files for triggers is due to historical reasons, and follows the same design as *.frm files for table metadata. This approach has several drawbacks:

  • Each file has yet another text file format, which is necessary to print and parse back correctly. Custom code has to be implemented, which is consuming in terms of resources, and introduces technical risk or code duplication.

  • Tables are replicated, values in columns are checked for data validity, integrity constraints can be defined ... where none of the above is available with a file based implementation.

  • With tables, the default locking, transaction and isolation mechanism used by the server in general can be leveraged, but the same is not available with files.

  • Cluster support for any new metadata operation that operates on files will require a custom solution. E.g. to propagate CREATE TABLE statement across MySQL Cluster mysqld nodes we use a so-called .frm shipping technique. There is no similar solution implemented for triggers at this point, and thus a trigger created in one mysqld node does not automatically become visible on other nodes. Potentially, if data is stored in tables, cluster support may be added as simply as by issuing ALTER TABLE mysql.triggers ENGINE=NDB;

Note

Various drawbacks of filesystem based solution are provided in this chapter only for a sake of example. Other advantages and disadvantages of two approaches may be found in relevant worklog entries and design documents.

Warning

The current implementation of the storage layer for table triggers is considered private to the server, and might change without warnings in future releases.


User Comments
Sign Up Login You must be logged in to post a comment.