i've implemented those changes.

now, to the trigger question: is there any way to prevent a delete
using a before delete trigger and not in an SP? i.e. to enforce rules.
i know an SP and the right grants would solve the issue but i wanted
to know if it was possible without those. also didn't want to use an
(unhandled) exception. i'd like to either prevent the delete or modify
what would be deleted.

[earlier mail]:
here's the trigger i'm using:
CREATE TRIGGER PART_NUM_ORDER_DEL FOR PARTS_INFO ACTIVE
BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE max_num SMALLINT;
BEGIN
SELECT MAX(parts) FROM parts_info
WHERE id = old.id
INTO :max_num ;
IF (max_num IS NULL) THEN
EXCEPTION all_parts_deleted;
ELSE
old.parts = max_num; /* <- this won't work...i need to set the
'parts' (1) that'll be deleted to max_num */
END

1. (in the comment)
2. even if the delete command is issued as
delete from parts_info where id = 2 and parts = 4,
if there is a 5, i want 5 to be deleted and not 4. i.e. specifying
parts = X should be made irrelevant.
[/earlier mail]

the old.parts = max_num is there just to indicate what i'm trying.
in an insert or update, it's possible to get the real values given in
a statement using new.col which can be changed (before).

for delete, u'd only know what's gonna be or was deleted but not the
col vals (from the where clause) that were given, even if they aren't
expressions (not a -architect request).

any trigger based ideas?

still waiting for answer to

> i'd used this in an another situtation but wasn't sure of it's
> benefits. does the speed that the "better PK" index provides justify
> the design? or is it only a better design with not much speed
> benefits? (which is fine with me)

...in my previous mail.

helen, in one of ur mails u wrote: "That's the bad news. The good
news is that you don't need compound
indexes. The optimizer is happy (even happIER) to work with a set of
single-column indexes."
...would that be why?

anirudh

On Wed, 27 Oct 2004 12:15:47 +0530, anirudh dutt <aneroid@...> wrote:
>
> two tables i'm dealing with:
> t1: (
> id integer (PK),
> name varchar,
> parts smallint,
> ...(30 fields describing the object in name)...
> )
>
> t2: (
> id integer (FK-> t1.id),
> part smallint,
> size smallint,
> wt, etc. (+5 fields),
> )
>
> i've got an insert and update trigger ensuring that t2.part <=
> t1.parts for t1.id = t2.id.
>
> each t1.name object can have several parts...and each of those parts
> have their own descriptions (7 fields). t1 has approx 30 fields, one
> of which is the total number of parts (t1.parts) that that obj has.
> so, if id=1 has 5 parts, there will be a row in t2 to describe each of
> it's 5 parts, so total 5 rows for id=1. now, if id=2 has 3 parts, we'd
> have (2,1...), (2,2...), (2,3...), each being the
> (id,part,other_fields...) of a row.
>
> t2.part is being used as a serial counter for each items parts. so
> (id=1,part=1...) and (id=2,part=1...) have no relation to each other.
> they are just the 1st parts of items id=1 and 2. also, (id=1, part=2)
> combo can not occur more than once, hence unique.
>
> >
> > Except that you are not using a parent-child structure here. You have only
> > children, with no parents, and redundancy. Additionally, choosing to use a
> > field that is both unstable and meaningful as part of the primary key
> > defeats the rule of atomicity.
>
> t1 would be the parent, t2 is the child. except for t2.(id,part) there
> is no unique combo. all t1.id's will have atleast a part=1 and each
> items parts are counted from 1 since they are independent of other
> objects' parts.
>
> >
> > Then you have given yourself an integrity problem. This structure needs
> > normalising into parents and children. Depending on what constitutes a
> > "part" (is is something that is, or should be, in a "parts" table?) there
> > might be a many:many relationship to resolve, using an intersection table
> > of (parent) id, sequence number and part id.
> >
> it's a one:many relation. as described in t1 and t2, it's normalized.
> if i use a sequence number to use as a unique id (like student ids), i
> would have a proper pk. i'd need a 3rd table...
>
> so newt2: (
> seq_no smallint pk,
> (5 fields of original t2)
> )
>
> and t3: (
> seq FK -> newt2.seq_no,
> id FK -> t1.id,
> part smallint
> )
>
> ...that maps seq. num in t2 to t1.id and part. is that what u had in mind?
>
> i'd used this in an another situtation but wasn't sure of it's
> benefits. does the speed that the "better PK" index provides justify
> the design? or is it only a better design with not much speed
> benefits? (which is fine with me)