In this article, we will look into the procedure to Delete one row from one table and insert that row into another table.
Before writing this article I want to say few words to all of you.
First of all my sincere Thanks to my friend (CHANDER SHARMA), he motivated me a lot. Because of his motivation today am here to write this small article. This is my Debut article. So let’s proceed with this question.
Method Used – By using TRIGGER
We can achieve this requirement with two tables, why because we can delete one row in one table and insert that same row into another table. So we can take tables both with same structure; if we are not taking the tables with same structure we can’t full fill this requirement. So let’s look this requirement into action with very small and easy script.
Now am taking two tables with same structure, one is TEST1 and second is TEST2.
–Creating table TEST1
Create table test1
–Read the table TEST 1
Select * from TEST1
–Create another table TEST2 with same structure of TEST 1 without using CREATE command
Select * into test2
–See the table TEST2 is it created with same structure of TEST1 or not
Select * from test2
–Inserting few rows in table TEST1
Insert into test1
–Read data from table TEST1
Select * from test1
–Create trigger for our required operation
Create trigger delete_trigger
on test1–table name
declare @id int,
–Read the data from DELETED MAGIC TABLE
select @id=id,@name=name from deleted
–Inserting deleted data into table TEST2
insert into test2 values (@id,@name)
–Now delete one row in Table TEST1
Delete from Test1
–Read table TEST 2 now the deleted data is inserted in this table or not
Select * from TEST2
1. Here I created two tables (TEST1, TEST2) for our requirement with same structure. After that I inserted few rows into first table TEST1.
2. Later, I created trigger on table TEST1.
3. After creation of trigger I deleted one row in table TEST1, that deleted row is then inserted into the table 2 that is TEST 2.
Hope you all enjoy this article. Your comments and queries are highly appreciated.
Heart full thanks to CHANDER SHARMA – Founder of PhpRinG Tutorials
VN:F [1.9.22_1171]DELETE one row from one table and insert that row into ANOTHER table,