Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008 TSQL
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:
 
INSERT INTO <TABLE> SELECT * FROM
and then
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
xclus287
Comments
Hernan K. Cabrera at 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
delete from ... where
else
update <TABLE>
set status = 10
endif
 

Regards,
 
Enan
Kenneth Haugland at 1-Sep-12 18:01pm
   
You should post this as a solution insted of a comment :)
Rate this: bad
good
Please Sign up or sign in to vote.

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:
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
Smile | :)
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  

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



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 2 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100