CREATE TRIGGER Syntax


This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.

CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in , "Binary Logging of Stored Programs".

The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time. See later in this section for more information.

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.

trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following:

It is important to understand that the trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger is activated by not only INSERT statements but also LOAD DATA statements because both statements insert rows into a table.

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row.

There cannot be two triggers for a given table that have the same trigger action time and event. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

trigger_body is the statement to execute when the trigger activates. If you want to execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are permissible within stored routines. See , "BEGIN ... END Compound-Statement Syntax". Some statements are not permitted in triggers; see "Restrictions on Stored Programs".

You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

MySQL stores the sql_mode system variable setting that is in effect at the time a trigger is created, and always executes the trigger with this setting in force, regardless of the server SQL mode in effect when the event begins executing.Note

Currently, cascaded foreign key actions do not activate triggers.

The DEFINER clause specifies the MariaDB account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MariaDB account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT-USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

If you specify the DEFINER clause, these rules determine the legal DEFINER user values:

MySQL takes the DEFINER user into account when checking trigger privileges as follows:

For more information about trigger security, see , "Access Control for Stored Programs and Views".

Within a trigger, the CURRENT_USER() function returns the account used to check privileges at trigger activation time. This is the DEFINER user, not the user whose actions caused the trigger to be activated. For information about user auditing within triggers, see , "Auditing MariaDB Account Activity".

If you use LOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described in , "LOCK TABLES and Triggers".

In MariaDB 5.6, you can write triggers containing direct references to tables by name, such as the trigger named testref shown in this example: 

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
 a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 b4 INT DEFAULT 0
);
delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
 FOR EACH ROW BEGIN
 INSERT INTO test2 SET a2 = NEW.a1;
 DELETE FROM test3 WHERE a3 = NEW.a1;
 UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
 END;
|
delimiter ;
INSERT INTO test3 (a3) VALUES
 (NULL), (NULL), (NULL), (NULL), (NULL),
 (NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
 (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Suppose that you insert the following values into table test1 as shown here: 

mysql> INSERT INTO test1 VALUES 
 -> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

As a result, the data in the four tables will be as follows:

Retornar