At 11:09 PM 17/01/2005 +0100, you wrote:

>Hi,
>
>I have small problem. Doing stats for the site have a table stat:
>id int,
>entity varchar(10),
>parentid int,
>action varchar(10),
>uniqueid char(32),
>unixtime int
>
>Well, I'm inserting a row with values from actual request.
>So I have rows:
>'article', 30, 'ret3453..', 'open', 1105998410
>'article', 30, 'ret3453..', 'open', 1105998434
>'article', 30, 'ret3453..', 'increment', 1105998470
>
>Problem: I need to select all only greatest (unique) unixtimes and its
>action
>for actual entity, its parentid and uniqueid.
>These values gives an composite unique key.
>
>I've got idea I could run a trigger before every insert,
>which would delete actual record with this key values
>and then command would insert new record with actual unixtime.
>
>Can I ask someone to help me?
>I'm a firetrigger greenhorn.

Ok, first, don't be tempted to write triggers that do things to rows in the
same table (as recommended by Adam) until you get VERY experienced in using
triggers. Then, once you understand the implications, you will know that
this is a very risky practice that should be avoided.

This structure has the signs of being inherited from a Paradox table, which
makes it *not* a good basis for designing key structures for Firebird. In
choosing keys, there is "theory" and then there is "practice". For
practical purposes, dispense with the notion that parentid and uniqueid
form the "key" to this table. If uniqueid is unique on its own, then
uniqueid alone is the candidate key and parentid alone is the foreign key
to the parent table.

Theoretical candidate keys are very often NOT what you will implement as
your physical keys, for the compelling reason of atomicity. If your
apparent key is meaningful in some way beyond merely being unique - and
could be changed by a user or an application or a trigger *because* it has
to mean something different - then it is not an atomic key. (Atomicity is
the first principle of integrity in relational database design). Create a
surrogate primary key in this case, using a generator and a trigger for
this (see the FAQ on the Firebird website).

Enforce the uniqueness across parentid and uniqueid using a UNIQUE
constraint. Also create a UNIQUE DESC index on unixtime. If parentid
needs to have referential integrity with the parent table, create a formal
FOREIGN KEY constraint on parentid - though take care that this doesn't buy
you a performance degradation, which can potentially occur if parentid has
a low distribution of possible values in your table.

Maintain the information you want by updating one or both parentid and
uniqueid in all cases where the sought combination already exists and
inserting ONLY if it doesn't. All update operations in Firebird are
row-level, so deleting the old row and inserting a new one is precisely
what the database engine does for you anyway, in an update operation. But
it does so in a much more healthy way than you could do by a
delete-and-insert strategy of your own.

Always keep in mind that, in Firebird, you are not doing anything
physically to the table yourself, as you did in Paradox. You are
requesting the engine to try to do what you ask. If you ask it to do
something that doesn't fit with the rules (constraints) that you defined,
or that conflicts with something being done elsewhere, then it will refuse
to obey your request and will return an exception instead.

The unique constraint will take care of the uniqueness. The foreign key
constraint will take care of the integrity of the relationship between the
rows in this table and those of the parent table. The descending index
will give the engine the fastest way to get the most recent unixtimes. The
index created to enforce the UNIQUE constraint will expose *either* the
pair as a composite unit *or* the parentid alone, for searching and sorting
in your selects.

./heLen