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));
|
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 |
+----------------+------------------------------------+
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
