Create After Insert Trigger in SQL
Create a Trigger is a special kind of stored procedure, that fires when we perform modification on the specific data on the specified table.
Understand with Example
The Tutorial illustrate a example from 'Create After Insert Trigger in SQL'. In this Example, we create a table 'Stu_Table' and 'Stu_Log' using create table statement.
Create Table Stu_Table
Create Table Stu_Table( Stu_Id int, Stu_Name varchar(15),Stu_Class int); |
Create Table Stu_Log
create table stu_log( user_id VARCHAR(15), description VARCHAR(100)); |
Here is the video tutorial of: "How to create insert after trigger in MySQL?"
Create Trigger Stu_Insert
The Query create a trigger stu_insert on table 'stu_table'. Whenever we perform insert values into a table 'stu_table', an insert trigger is fired and performed for each rows in the table. Once the trigger operation is performed ,this will run the query inside the begin-end statement. The insert into add the records or rows value to the table stu_log.
delimiter $$ CREATE TRIGGER stu_insert AFTER insert ON stu_table FOR EACH ROW BEGIN INSERT into stu_log(user_id, description) VALUES (user(), CONCAT('Insert Student record ', new.stu_id,' ',new.stu_name,' ',new.stu_class)); END$$ delimiter ; |
Insert Data Into Stu_Table
insert into stu_table values(1, 'Komal',10); insert into stu_table values(2, 'Ajay',10); insert into stu_table values(3, 'Santosh',10); insert into stu_table values(4, 'Rakesh',10); insert into stu_table values(5, 'Bhau',10); |
Stu_Table
+--------+----------+-----------+ | Stu_Id | Stu_Name | Stu_Class | +--------+----------+-----------+ | 1 | Komal | 10 | | 2 | Ajay | 10 | | 3 | Santosh | 10 | | 4 | Rakesh | 10 | | 5 | Bhau | 10 | +--------+----------+-----------+ |
Stu_Log
+----------------+------------------------------------+ | user_id | description | +----------------+------------------------------------+ | root@localhost | Insert Student record 1 Komal 10 | | root@localhost | Insert Student record 2 Ajay 10 | | root@localhost | Insert Student record 3 Santosh 10 | | root@localhost | Insert Student record 4 Rakesh 10 | | root@localhost | Insert Student record 5 Bhau 10 | +----------------+------------------------------------+ |