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
Delete 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 | +--------+----------+-----------+ |