Re: Maths Formula Question
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 10/01/04
- Next message: MikeG: "Learning VB6"
- Previous message: george collins: "Need direction with reading VB.exe version"
- Next in thread: Ian: "Re: Maths Formula Question"
- Reply: Ian: "Re: Maths Formula Question"
- Maybe reply: bz: "Re: Maths Formula Question"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 02 Oct 2004 01:19:03 +0200
On Wed, 29 Sep 2004 10:49:42 +0100, Ian wrote:
>I would have liked to do what you are recommending about remove the triggers
>but then how would I cater for when a user then reorders an individual
>instruction.
Hi Ian,
Sorry for not getting back to you earlier, but Ive been rather busy. Still
am, in fact. I'll try to sketch the rough outlines of how I would do it.
First some general comments on the part of your script that I looked at.
In the Instructions table, InstructionOrder should be integer and the
primary key should be on (ScenarioID, InstructionOrder). The InstructionID
column with it's identity property is not needed - drop it.
You didn't ask, but I see no reason to use dynamic SQL. Just type out the
column list. Do it once, then use copy and paste later. Your code will
look cleaner, be easier to understand and probably run faster as well.
Now to the main issue: how to get rid of the triggers. First, deny all
users insert, delete and update rights for the Instructions table. They
should be able to change this table only through stored procedures. The
procedures you'll need are:
1. Init temp table.
Function: This will either create or truncate a temp table (name should
start with one # symbol) that has all columns of Instructions except
ScenarioID.
2. Add instruction to temp table.
Parameters: All columns of Instructions, except ScenarioID and
InstructionOrder.
Function: Append one instruction at the end of temp table.
Logic: insert row in temp table with parameters given; set
InstructionOrder to ISNULL(MAX(InstructionOrder), 0) + 1.
3. Drop temp table.
Function: Drops the temp table.
4. Insert instructions into scenario.
Parameters: ScenarioID, InsertLoc
Requirements: InsertLoc >= 0 and <= MAX(InstructionOrder) for scenario.
Function: Instructions in temp table are inserted, just after the
instruction with InstructionOrder = InsertLoc. (Use InsertLoc to insert at
the start of a scenario).
Logic: Increase InstructionOrder by MAX(temp.InstructionOrder) for each
instruction with InstructionOrder > InsertLoc; insert rows from temp table
with InstructionOrder = InsertLoc + temp.InstructionOrder.
5. Delete instructions from scenario.
Parameters: ScenarioID, DelFrom, DelTo
Requirements: DelFrom and DelTo > 0 and <= MAX(InstructionOrder) for
scenario; DelFrom <= DelTo.
Function: Instructions with InstructionOrder >= DelFrom and <= DelTo are
deleted. To delete one instruction, set DeleteFrom equal to DeleteTo.
Logic: Delete instructions indicated by parameters; decrease
InstructionOrder by (DelTo - DelFrom + 1) for each instruction with
InstructionOrder > DelTo.
6. Copy instructions to temp table.
Parameters: ScenarioID, CopyFrom, CopyTo
Requirements: CopyFrom and CopyTo > 0 and <= MAX(InstructionOrder) for
scenario; CopyFrom <= CopyTo.
Function: Instructions with InstructionOrder >= CopyFrom and <= CopyTo are
appended at the end of the temp table.
Logic: Copy instructions indicated by parameters to temp table, with
temp.InstructionOrder equal to (instructions.InstructionOrder - CopyFrom +
ISNULL(MAX(temp.InstructionOrder [before inserting]), 0) + 1).
Using these as the basis, you can go on to make:
7. Copy instructions within scenario.
Parameters: ScenarioID, CopyFrom, CopyTo, InsertLoc
Requirements: CopyFrom and CopyTo > 0 and <= MAX(InstructionOrder) for
scenario; InsertLoc >= 0 and <= MAX(InstructionOrder) for scenario;
CopyFrom <= CopyTo.
Function: A copy of instructions with InstructionOrder >= CopyFrom and <=
CopyTo is inserted, just after the instruction with InstructionOrder =
InsertLoc. (Use InsertLoc to insert at the start of a scenario).
Logic: Use proc 1 to init temp table, proc 6 to put instructions to be
copied to temp table, proc 4 to copy them to the new location and proc 3
to drop the temp table.
8. Move instructions within scenario.
Parameters: ScenarioID, MoveFrom, MoveTo, InsertLoc
Requirements: MoveFrom and MoveTo > 0 and <= MAX(InstructionOrder) for
scenario; InsertLoc >= 0 and <= MAX(InstructionOrder) for scenario;
MoveFrom <= MoveTo; InsertLoc < MoveFrom - 1 or > MoveTo.
Function: The instructions with InstructionOrder >= MoveFrom and <= MoveTo
are moved to just after the instruction with InstructionOrder = InsertLoc.
(Use InsertLoc to move to the start of a scenario).
Logic: If InsertLoc < MoveFrom, use proc 1 to init temp table, proc 6 to
put instructions to be copied to temp table, proc 5 to delete instructions
from old location, proc 4 to copy them to the new location and proc 3 to
drop the temp table.
If InsertLoc > MoveTo, use proc 1 to init temp table, proc 6 to put
instructions to be copied to temp table, proc 4 to copy them to the new
location, proc 5 to delete instructions from old location and proc 3 to
drop the temp table.
9. Copy instructions between scenarios.
Parameters: ScenarioFrom, ScenarioTo, CopyFrom, CopyTo, InsertLoc
Requirements: CopyFrom and CopyTo > 0 and <= MAX(InstructionOrder) for
ScenarioFrom; InsertLoc >= 0 and <= MAX(InstructionOrder) for ScenarioTo;
CopyFrom <= CopyTo; ScenarioFrom <> ScenarioTo.
Function: The instructions with InstructionOrder >= CopyFrom and <= CopyTo
in ScenarioFrom are copied to ScenarioTo, just after the instruction with
InstructionOrder = InsertLoc. (Use InsertLoc to insert at the start of the
scenario).
Logic: Use proc 1 to init temp table, proc 6 to put instructions to be
copied from ScenarioFrom to temp table, proc 4 to copy them to the new
location in ScenarioTo and proc 3 to drop the temp table.
Some final notes:
* Make sure all calls to these proces are encapsulated in a transaction.
* If your application calls proc 2 repeatedly to enter some new
instructions, then proc 4 to store themn in a scenario, make sure that the
connection stays alive. If the connection is dropped, the temp table will
be dropped.
* If performance matters, do extensive testing to find out if the index
associated wiith the primary key on (ScenarioID, InstructionOrder) should
be defined as clustered or nonclustered (and in the latter case, if there
should be another clustered index or no clustered index at all). I have no
idea how much the performance in each case will suffer as a result of the
constant renumbering of InstructionOrders.
I hope this helps. I had already typed a longer answer, but just before
hitting the Send button my newsreader suddenly caused a serious error and
my reply was lost. I hope I did include all essential stuff when I retyped
the message.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: MikeG: "Learning VB6"
- Previous message: george collins: "Need direction with reading VB.exe version"
- Next in thread: Ian: "Re: Maths Formula Question"
- Reply: Ian: "Re: Maths Formula Question"
- Maybe reply: bz: "Re: Maths Formula Question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
