hi
i've got a table with a 2 column primary key: (id, part). i'm using a
common (before) insert and update trigger to make sure that the data
is in a particular format: for each id, there are parts starting from
1. and under no circumstances can part 3 or 4 exist for id 1 when
there is no part 2. so it's (1,1...), (1,2...), (2,1...), (2,2...),
(2,3...). i.e. the parts increase sequentially for each id. (there are
other columns also)

now, when a record (id) is being deleted, i want the highest part to
be deleted first, kinda like LIFO. i'm gonna use a stored procedure to
actually do the deleting but i want to make sure the right trigger is
in place, to ensure data integrity.

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.

i managed to do that for the before insert and update triggers.

related to this: when triggers are being used and access is granted to
just executing stored procedures, is there much of a point to
enforcing the rules further (and a bit redundantly) in the triggers?
as a security issue. should one always assume that security has not
been bypassed or privileges escalated? (in which case they could just
drop the trigger, i know)

thanks
anirudh