After Insert Trigger in MySQL is invoked automatically whenever an insert event occurs on the table. In this article, we are going to learn how to create an after insert trigger with its syntax and example.
Syntax
The following is the syntax to create an AFTER INSERT trigger in MySQL:
CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name FOR EACH ROW
trigger_body ; The AFTER INSERT trigger syntax parameter can be explained as below:
- First, we will specify the name of the trigger that we want to create. It should be unique within the schema.
- Second, we will specify the trigger action time, which should be AFTER INSERT clause to invoke the trigger.
- Third, we will specify the name of a table to which the trigger is associated. It must be written after the ON keyword. If we did not specify the table name, a trigger would not exist.
- Finally, we will specify the trigger body that contains one or more statements for execution when the trigger is activated.
If we want to execute multiple statements, we will use the BEGIN END block that contains a set of SQL queries to define the logic for the trigger. See the below syntax:
DELIMITER $$
CREATE TRIGGER trigger_name AFTER INSERT
ON table_name FOR EACH ROW
BEGIN
variable declarations
trigger code
END$$
DELIMITER ; Restrictions
- We can access the NEW values but cannot change them in an AFTER INSERT trigger.
- We cannot access the OLD If we try to access the OLD values, we will get an error because there is no OLD on the INSERT trigger.
- We cannot create the AFTER INSERT trigger on a VIEW.
AFTER INSERT Trigger Example
Let us understand how to create an AFTER INSERT trigger using the CREATE TRIGGER statement in MySQL with an example.
Suppose we have created a table named “student_info” as follows:
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)
)Next, we will insert some records into this table and then execute the SELECT statement to see the table data as follows:
Again, we will create a new table named “student_detail” as follows:
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 the MySQL 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:
Leave a Reply