Moving Record

[Arg81]

Hi Guys,

I assume when moving a record from one table to another, I need to create a stored procedure than first calls an insert, and then delete?

I.E. I'm working on a computer audit. A PC has just been scrapped. I want to move it from the Main Audit table to the scrapped table (columns are the same).

In Access, I did this in the form off a macro running two queries, first an append, and then a delete (to remove it from the main table).

I have 3 tables that it could possibly move to - Scrap, Stolen or Sold. I need to create a procedure that can move any selected record to either one of those tables.

Any help much appriciated.

cheers,
Luke

 

[Schenz]

Wouldn't it be easier to have one table with boolean columns for Scrap, Stolen, or Sold?

 

[tarunkapoor]

I dont think i understood your problem very well. But from what i understood, how abt this.

Make a parameterized stored proc which takes the table name as input. The SQL queries in the proc would depend on the input parameter (table name)

 

[Arg81]

Schenz, probably the best idea. However, I just didn't want all the rows that were irrelevant within one table, and as time goes on more computers will be brought and more will be scrapped - there would be a lot of redundant data in a sense in that one table, however I can't just remove the row as we need to keep a record of what has been sold / scrapped for external audit purposes.

For the time being I may just do as you suggested.

Luke

 

[Schenz]

I just had a thought, but have not used it personally. SQL Server has triggers. These cause certain type of queries to be triggered based on a certain event. This may be what you want. When you update one of the boolean fields then a trigger is called to "move" that record to another table and "remove" it from the current table.

I would recommend reading up on Triggers in the SQL Server Online Books. This may be the solution you are looking for.

 

[TechGnome]

Avoid triggers.... that'll jsut cost you later.... trust me.... and they aren't 100% reliable...

I liked the idea of a parameter to the SP.... but not the name of the table... butrather a number: 1, 2, 3 depending on where it needs to go....
then you can do this:

If @DestTable = 1
  BEGIN
    Insert into .....
  END

If @DestTable = 2
  BEGIN
    Insert into .....
  END

If @DestTable = 3
  BEGIN
    Insert into .....
  END

Delete from ....

More efficient, and if you expand to more tables, less work to update the SP.

Tg

 

[Schenz]

Hey Tech,

I knew there had to be a downside to triggers, but as I never used them before I didn't know what they would be.

Could you post some of the problems you have had with them so I know what to look for if I am ever forced by other management to use them?

 

[TechGnome]

1) Cross contamination of data
2) There's no way to debug the buggers, unlike a regular query that you can run Query Analyzer and see the results
3) If you use IDENTIY fields on the tables, subtract two years from your life expectancy because you just added another layer to the problem
4) No way to debug it - at least with a regular query you can run Query Analyzer and see the results
5) Managing triggers can be messy, especialy if you need to add fields to the table.
6) Have I mentioned that there's no way to debug them?
7) If you ever need to update the data using a query outside the application.... all those triggers are going to fire....
8) Business logic (which this is) should ALWAYS be separated from the data. That's not to say that the Insert & Delete cannot be in the same stored procedure, it jsut needs to be as far from the data store as it reasonable can be.

Tg

ps: Oh, yeah, and they are a PITA to debug.... if something goes wrong, there's no way to really know what it was, you just know that it didn't work.