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

Create Delete Trigger in SQL

Create Delete Trigger in SQL

Create Delete Trigger in SQL

Tutorials