Create After Update Trigger in SQL
After Trigger in SQL is fired before update the column in the table.
Understand with Example
The Tutorial illustrate an example from 'Create After Update Trigger in
SQL'. In order to understand this example, we create a table Stu_Table and
Stu_Log. To create these tables we use create table statement
that construct table Stu_Table and Stu_Log with specific field name and
data type respectively.
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_Update
The below Query create a Trigger 'stu_update' on
table stu_table.
delimiter $$
CREATE TRIGGER stu_update
AFTER UPDATE ON stu_table FOR EACH ROW
BEGIN
INSERT into stu_log(user_id, description)
VALUES (user(), CONCAT('Update Student Record
(',old.stu_id,' ',old.stu_name,' ',old.stu_class,
') to (',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 |
+--------+----------+-----------+
|
Update Stu_Table
update stu_table set Stu_Class = stu_class+1;
|
Stu_Table
+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1 | Komal | 11 |
| 2 | Ajay | 11 |
| 3 | Santosh | 11 |
| 4 | Rakesh | 11 |
| 5 | Bhau | 11 |
+--------+----------+-----------+
|
Stu_Log
+----------------+--------------------------------------------------------+
| user_id | description |
+----------------+--------------------------------------------------------+
| root@localhost | Update Student Record (1 Komal 11) to (1 Komal 12) |
| root@localhost | Update Student Record (2 Ajay 11) to (2 Ajay 12) |
| root@localhost | Update Student Record (3 Santosh 11) to (3 Santosh 12) |
| root@localhost | Update Student Record (4 Rakesh 11) to (4 Rakesh 12) |
| root@localhost | Update Student Record (5 Bhau 11) to (5 Bhau 12) |
+----------------+--------------------------------------------------------+
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
