The CREATE TRIGGER statement is used to create a new trigger. A trigger is a database object associated with a table that activates 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 |
Note |
|---|---|
| DEFINER | Optional. Specifies the definer (creator) of the trigger. If omitted, the default is the executor. CREATE TRIGGER The user who executed the statement. You can specify 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 | Trigger activation time: BEFORE(Before the operation) or AFTER(After the operation). The basic check on column values is performed before the trigger is activated, so you cannot use BEFORE The trigger converts values that do not match the column type into valid values. |
| trigger_event | 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, and REPLACE statements.- UPDATE: Activated whenever a row is modified.- DELETE: Activated whenever a row is deleted from the table, for example, by using the DELETE and REPLACE statement. DROP TABLE and TRUNCATE TABLE The statement will not activate this trigger. |
| table_name | The table name associated with the trigger must be a non-temporary table. Triggers cannot be associated with temporary tables or views. |
| trigger_order | Optional. Specifies the order of triggers with the same trigger event and operation time. FOLLOWS indicates that it is activated after the existing trigger, PRECEDES A trigger with a higher order number is activated before one with a lower order number. You can define multiple triggers for a table with the same event and action time. By default, triggers are activated in the order they are created. |
| trigger_body | The statements that are executed when the trigger is activated. You can use BEGIN ... END Compound statements execute multiple statements. In the body of a trigger, you can use aliases. OLD and NEW to reference columns in the primary table. OLD.column_name refers to the columns before the column that is to be updated or deleted in the current row, NEW.column_name refers to the columns of the new row to be inserted or the current row to be updated. Triggers cannot use NEW.col_name and OLD.column_name to reference the generated columns. |
The CREATE TRIGGER statement requires the TRIGGER privilege on the table associated with the trigger. If a DEFINER clause is present, the required privileges depend on the value of the user clause.
When checking trigger privileges, the DEFINER user is involved:
The user who uses the
CREATE TRIGGERstatement to create a trigger must have theTRIGGERprivilege.When activation is triggered, privileges are checked against the
DEFINERuser. This user must have the following privileges:The
TRIGGERprivilege on the primary table.If you use
OLD.col_nameorNEW.col_namefrom the trigger body to reference table columns, you must have theSELECTprivilege on the primary table.If a table column is assigned a value by using the
SET NEW.col_name = valuestatement in the trigger body, you must have theUPDATEprivilege on the primary 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, rather than the user whose operation triggered the trigger.
Examples
Create a BEFORE INSERT trigger to set default values before data is inserted.
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 the 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 detailed examples on creating triggers, see Trigger.