MySQL AFTER INSERT触发器

在表中发生插入事件时，在MySQL中插入触发器之后，将自动调用该触发器。在本文中，我们将学习如何使用其语法和示例创建一个插入后触发器。

语法

以下是在MySQL中创建 AFTER INSERT 触发器的语法：

CREATE TRIGGER trigger_name 
AFTER INSERT
ON table_name FOR EACH ROW
trigger_body ;

AFTER INSERT触发器语法参数可以解释如下：

如果要执行多个语句，我们将使用包含一组SQL查询的 BEGIN END 块来定义触发器的逻辑。请参见以下语法：

DELIMITER $$ 
CREATE TRIGGER trigger_name AFTER INSERT
ON table_name FOR EACH ROW
BEGIN
   variable declarations
   trigger code
END$$
DELIMITER ;

限制 插入后触发示例

让我们通过示例了解如何使用MySQL中的 CREATE TRIGGER 语句创建AFTER INSERT触发器。

假设我们创建了一个名为“ student_info ”的表，如下所示：

CREATE TABLE student_info (
  stud_id int NOT NULL,
  stud_code varchar(15) DEFAULT NULL,
  stud_name varchar(35) DEFAULT NULL,
  subject varchar(25) DEFAULT NULL,
  marks int DEFAULT NULL,
  phone varchar(15) DEFAULT NULL,
  PRIMARY KEY (stud_id)
)

接下来mysql触发器，我们将一些记录插入到该表中，然后执行SELECT语句以查看表数据，如下所示：

同样，我们将创建一个名为 “ student_detail” 的新表，如下所示：

CREATE TABLE student_detail (
  stud_id int NOT NULL,
  stud_code varchar(15) DEFAULT NULL,
  stud_name varchar(35) DEFAULT NULL,
  subject varchar(25) DEFAULT NULL,
  marks int DEFAULT NULL,
  phone varchar(15) DEFAULT NULL,
  Lasinserted Time,
  PRIMARY KEY (stud_id)
);

Next, we will use a CREATE TRIGGER statement to create an after_insert_details trigger on the student_info table. This trigger will be fired after an insert operation is performed on the table.

mysql> DELIMITER //
mysql> Create Trigger after_insert_details
AFTER INSERT ON student_info FOR EACH ROW
BEGIN
INSERT INTO student_detail VALUES (new.stud_id, new.stud_code, 
new.stud_name, new.subject, new.marks, new.phone, CURTIME());
END //

If the trigger is created successfully, we will get the output as follows:

How to call the AFTER INSERT trigger?

We can use the following statements to invoke the above-created trigger:

mysql> INSERT INTO student_info VALUES 
(10, 110, 'Alexandar', 'Biology', 67, '2347346438');

The table that has been modified after the update query executes is student_detail. We can verify it by using the SELECT statement as follows:

mysql> SELECT * FROM student_detail;

In this output, we can see that on inserting values into the student_info table, the student_detail table will automatically fill the records by invoking a trigger.

How to create AFTER INSERT Trigger in MySQL workbench?

To create an after insert trigger using this tool, we first need to launch theMySQL Workbench and log in using the username and password that we have created earlier. We will get the screen as follows:

Now do the following steps for creating an AFTER INSERT trigger:

1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.

2. Select the database (for example, mystudentdb), double click on it that shows the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.

3. Expand the Tables sub-menu and select the table on which you want to create a trigger. After selecting a table, right-click on the selected table ( for example , mystudentdb), and then click on the Alter Table option. See the below image:

4. Clicking on the Alter Table option gives the screen as below:

5. Now, click on the Trigger tab shown in the red rectangular box of the previous section, then select the Timing/Event AFTER INSERT. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on the trigger based on choosing Timing/Event:

6. Now, complete the trigger code, review them once again, and if no error is found, click on the Apply button.

7. After clicking on the Apply button, click on the Finish button for completion.

8. If we look at the schema menu, we can see AFTER_INSERT_detail trigger under the student_info table as follows:

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!