Home Sql Create Delete Trigger in SQL
Questions:Ask|Latest

 
 

Share on Google+Share on Google+

Create Delete Trigger in SQL

Advertisement
Create Delete Trigger in SQL fire this trigger before the DELETE operation is executed.

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

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: December 31, 2008

Related Tutorials

Ask Questions?    Discuss: Create Delete Trigger in SQL  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com