Create Delete Trigger in SQL
Create Delete Trigger in SQL fire this trigger before the DELETE
operation is executed.
Understand with Example
The Tutorial illustrate an example from 'Create Delete Trigger in SQL'.
To understand this example, we create a table 'Stu_Table' and its back up.
Create Table Stu_Table
Create Table Stu_Table( Stu_Id int, Stu_Name varchar(15),Stu_Class int);
|
Create Table Stu_Table_backup
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_Delete
The create trigger 'stu_delete' perform delete
trigger on table stu_delete. The Delete trigger is fired before we perform
delete operation on records into table 'stu_table' . The delete
trigger show you the deleted records in 'stu_log' from table 'stu_table'.
delimiter $$
CREATE TRIGGER stu_delete
before delete ON stu_table FOR EACH ROW
BEGIN
INSERT into stu_log(user_id, description)
VALUES (user(), CONCAT('Record deleted ',old.stu_id,'
',old.stu_name,' ',old.stu_class));
insert into stu_table_backup values(old.stu_id,old.stu_name,old.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 |
+--------+----------+-----------+
|
Delete Records from Stu_Table
Stu_Log
+----------------+-----------------------------+
| user_id | description |
+----------------+-----------------------------+
| root@localhost | Record deleted 1 Komal 12 |
| root@localhost | Record deleted 2 Ajay 12 |
| root@localhost | Record deleted 3 Santosh 12 |
| root@localhost | Record deleted 4 Rakesh 12 |
| root@localhost | Record deleted 5 Bhau 12 |
+----------------+-----------------------------+
|
Stu_Table_Backup
+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1 | Komal | 10 |
| 2 | Ajay | 10 |
| 3 | Santosh | 10 |
| 4 | Rakesh | 10 |
| 5 | Bhau | 10 |
+--------+----------+-----------+
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
