trigger for before delete a record

Question

1.00/5 (1 vote)

See more:

I'm using sql server 2005 and have 2 tables in my database.
How can I write a trigger that can INSERT a record into table 2 BEFORE delete in table 1?
How can I do this?
I need this for VB.Net.
I've already done a connection with Sql server.

Posted 25-Nov-10 21:51pm

Neeil

Updated 25-Nov-10 21:54pm

JF2015

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2010-11-25T22:00:00

Solution 1

Well, you need to write a trigger[^], keep in place in database.

Once thisw is done, using your connection to SQL Server, execute the query/stored procedure that deletes something from table1. Internally, trigger will handle this event and insert a record in table2 - after which delete would be executed in table1.

Posted 25-Nov-10 22:00pm

Sandeep Mewara

Comments

Dalek Dave 26-Nov-10 4:21am

Good Answer.

Vivek Johari · Accepted Answer · 2010-11-26T05:48:00

Hi Neeil,
You can use trigger to solve your problem. Suppose you have a table named table1 whose structure is given below

table1
--------
id name age
1 xyz 26
2 abc 25

and you want to insert the deleted row into another table says table2(which has the same table structure as table1) before you delete from table1. So you can use the following trigger

SQL

create trigger trig_delete on table1 for delete
as
begin
insert into table2
select deleted.id,deleted.name, deleted.age from deleted
end

it will insert the deleted row into table "table2" while deleting the data from table "table1"

Hope this will solve your problem........ :-)