Move a record from one table to another

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

Hi guys,

I'm wondering which is the best approach in my situation.
I have a table with information about the orders and a field which indicates the status of the order(the type of the field is smallint). I use a stored procedure to change the status. When the status of the order becomes, for example 10 (it indicates that the order is completed), I want to move this row to the another table and delete it from the table of orders. Which is better:

SQL

INSERT INTO <TABLE> SELECT * FROM

and then

SQL

DELETE FROM ...

Or

TRIGGER (INSTEAD OF UPDATE) - it will monitor the status, but I think that it's not a good idea to have business logic into the trigger

If you have other suggestions, please share with me. Thanks in advance!

Best regards,
xclus

Posted 1-Sep-12 11:49am

xclus

Add a Solution

Comments

Hernan K. Cabrera 1-Sep-12 17:58pm

Hi,

Whichever will do but if you think that Business Logic is not good to be in trigger, I would suggest this.

in SP:

if varStatus = 10 then
insert into <TABLE> select from ... where <condition>
delete from ... where <condition>
else
update <TABLE>
set status = 10
endif

Regards,

Enan

Kenneth Haugland 1-Sep-12 18:01pm

You should post this as a solution insted of a comment :)

3 solutions

Solution 1

Hi,

Whichever will do but if you think that Business Logic is not good to be in trigger, I would suggest this.

in SP:

SQL

if varStatus = 10 then
  insert into TABLE select from ... where CONDITION
  delete from ... where CONDITION
else
  update TABLE
  set status = 10
  where CONDITION
endif

Regards,

Enan
:)

Posted 1-Sep-12 12:04pm

Hernan K. Cabrera

Updated 1-Sep-12 12:07pm

v2

Solution 2

Hi,

[Remove]
I suggest you to go for the trigger, because if you write your own Logic for this scenario then you have to taken care of all possible exception scenarios.

You can use Condition in your trigger to move your information from one place to another. you also need transaction to check if your process is executed as single process.
[/Remove]

[Edit]
After positing this solution i read that you have stored procedure to update status,In that case just use that moving row logic in your StoredProcedure but don't forget to use Transaction.
[/Edit]

Thanks
-Amit Gajjar

Posted 1-Sep-12 15:51pm

AmitGajjar

Updated 1-Sep-12 15:55pm

v2

Add a Solution

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

Wendelius · Accepted Answer · 2012-09-01T20:26:00

I think using a trigger (not an instead of trigger) or writing a stored procedure would do the job.

However, I wouldn't move the rows from a table to another in the first place.

In my opinion moving the rows based on the data introduces extra logic to the application, which typically isn't necessary. For example what happens when:
- the status changes forth and back
- you want to list all the orders regardless of the status
- a new status is introduced etc.

If you're worried about the performance, it can be tackled by using proper indexes and well written SQL statements. Also if you like, you can write views on the table to limit the results based on a predefined conditions like the status.

Move a record from one table to another

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Move a record from one table to another

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us