Mysql 简明教程
MySQL - CREATE TRIGGER
触发器通常被定义为对事件的响应。例如，当我们悬停鼠标指针在某个网站的下拉菜单上时，将会显示一系列导航该网站的选项。此处，鼠标指针的悬停是一个事件，而下拉菜单中显示的选项是触发器执行的结果。该概念也在 MySQL 中引入。
Triggers are generally defined as responses to an event. For instance, when we hover a mouse-pointer on a drop-down menu of a website, a various set of options to navigate through this website are then displayed. Here, the hovering of the mouse-pointer is an event while the display of options in the drop-down menu is a result of trigger execution. This concept is also introduced in MySQL.
MySQL 中的触发器类似于过程，是一些存储程序。它们可以在与事件关联的表、架构、视图和数据库上创建，并且每当事件发生时便会调用相应的触发器。
Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked.
在实际上，触发器编写为对以下任一事件响应而执行：
Triggers are, in fact, written to be executed in response to any of the following events −
-
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
-
A database definition (DDL) statement (CREATE, ALTER, or DROP).
-
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Creating Trigger in MySQL
你可以使用 CREATE TRIGGER 语句创建触发器。
You can create a trigger using the CREATE TRIGGER Statement.
Syntax
以下是 MySQL CREATE TRIGGER 语句的语法。
Following is the syntax of the MySQL CREATE TRIGGER Statement.
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
...
END;其中，
Where,
-
trigger_name is the name of the trigger you need to create
-
trigger_time is the time of trigger activation
-
trigger_event can be INSERT, UPDATE, or DELETE. This event causes the trigger to be invoked.
-
table_name is the name of the table to which the trigger is associated with.
Example
假设我们创建了一个名为 student 的表，如下所示：
Assume we have created a table with name student as shown below −
CREATE TABLE STUDENT(
Name varchar(35),
Age INT,
Score INT
);以下查询创建了一个触发器，如果输入的值小于 0，它将将得分值设置为 0。
Following query creates a trigger, this will set the score value 0 if you enter a value that is less than 0 as score.
DELIMITER //
CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW
BEGIN
IF NEW.Score < 0 THEN SET NEW.Score = 0;
END IF;
END //
DELIMITER ;Verification
如果你尝试在 student 表中插入记录并且使用小于 0 的值作为年龄，它将自动设置为 0。
If you try to insert records in the student table and if you use a value that is less than 0 as age it will be automatically set to 0.
INSERT INTO STUDENT VALUES
('Jeevan', 22, 8),
('Raghav', 26, -3),
('Pooja', 21, -9),
('Devi', 30, 9);创建的 STUDENT 表将具有以下记录 -
The STUDENT table created will have the following records −
如我们所见，表中没有插入负值，因为它们都已替换为零。
As we can see, there are no negative values inserted in the table as they are all replaced with zeroes.