The CREATE TRIGGER statement is used to create a new trigger. A trigger is a database object associated with a table and is activated when a specified event occurs on the table.
Syntax
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
Parameters
Parameter |
Description |
|---|---|
| DEFINER | Optional. Specifies the definer (creator) of the trigger. If omitted, the default is the user who executed the CREATE TRIGGER statement. You can specify the definer as 'user_name'@'host_name' or CURRENT_USER. |
| trigger_name | The name of the trigger, which must be unique within the schema. Triggers in different schemas can have the same name. |
| trigger_time | The activation time of the trigger: BEFORE (before the operation) or AFTER (after the operation). Basic checks on the column values are performed before the trigger is activated, so you cannot use a BEFORE trigger to convert invalid values to valid ones. |
| trigger_event | The event type that activates the trigger: INSERT, UPDATE, or DELETE. - INSERT: Activated whenever a new row is inserted into the table, for example, by using the INSERT, LOAD DATA, or REPLACE statement. - UPDATE: Activated whenever a row is modified. - DELETE: Activated whenever a row is deleted from the table, for example, by using the DELETE or REPLACE statement. The DROP TABLE and TRUNCATE TABLE statements do not activate this trigger. |
| table_name | The name of the table associated with the trigger. The table must be a non-temporary table, and you cannot associate a trigger with a temporary table or view. |
| trigger_order | Optional. Specifies the order of triggers with the same trigger event and operation time. FOLLOWS indicates that the trigger is activated after existing triggers, and PRECEDES indicates that the trigger is activated before existing triggers. You can define multiple triggers for the same trigger event and operation time. By default, the triggers are activated in the order they were created. |
| trigger_body | The statements to be executed when the trigger is activated. You can use the BEGIN ... END compound statement to execute multiple statements. In the trigger body, you can use the aliases OLD and NEW to reference columns in the main table. OLD.column_name refers to the columns before the current row is updated or deleted, and NEW.column_name refers to the columns of the new row to be inserted or the updated current row. Triggers cannot use NEW.col_name or OLD.column_name to reference generated columns. |
The CREATE TRIGGER statement requires the TRIGGER privilege on the table associated with the trigger. If the DEFINER clause is specified, the required privileges depend on the user value.
When checking the trigger privileges, the DEFINER user is involved:
The user who executed the
CREATE TRIGGERstatement must have theTRIGGERprivilege.When the trigger is activated, the privileges are checked for the
DEFINERuser. This user must have the following privileges:The
TRIGGERprivilege on the main table.If you use
OLD.col_nameorNEW.col_namein the trigger body to reference table columns, you must have theSELECTprivilege on the main table.If the table columns are assigned values by using
SET NEW.col_name = valuein the trigger body, you must have theUPDATEprivilege on the main table.Other privileges typically required for the statements executed by the trigger.
In the trigger body, the CURRENT_USER() function returns the account used to check privileges when the trigger is activated, which belongs to the DEFINER user, not the user whose operation caused the trigger to be activated.
Examples
Create a BEFORE INSERT trigger to set default values before inserting data.
obclient> CREATE TABLE employee_audit(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary DECIMAL(10,2),
created_at DATETIME
);
obclient> DELIMITER //
obclient> CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employee_audit
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END //
obclient> DELIMITER ;
obclient> INSERT INTO employee_audit(emp_id, emp_name, salary) VALUES (1, 'Alice', 8000.00);
obclient> SELECT * FROM employee_audit;
The query result is as follows:
+--------+----------+---------+---------------------+
| emp_id | emp_name | salary | created_at |
+--------+----------+---------+---------------------+
| 1 | Alice | 8000.00 | 2025-12-23 15:30:06 |
+--------+----------+---------+---------------------+
1 row in set
Create an AFTER UPDATE trigger to record update logs.
obclient> CREATE TABLE salary_log(
log_id INT AUTO_INCREMENT PRIMARY KEY,
emp_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
update_time DATETIME
);
obclient> DELIMITER //
obclient> CREATE TRIGGER after_salary_update
AFTER UPDATE ON employee_audit
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_log(emp_id, old_salary, new_salary, update_time)
VALUES (NEW.emp_id, OLD.salary, NEW.salary, NOW());
END IF;
END //
obclient> DELIMITER ;
obclient> UPDATE employee_audit SET salary = 9000.00 WHERE emp_id = 1;
obclient> SELECT * FROM salary_log;
The query result is as follows:
+--------+--------+------------+------------+---------------------+
| log_id | emp_id | old_salary | new_salary | update_time |
+--------+--------+------------+------------+---------------------+
| 1 | 1 | 8000.00 | 9000.00 | 2025-12-23 15:30:48 |
+--------+--------+------------+------------+---------------------+
1 row in set
For more information about creating triggers, see Triggers.