Whether you want to delete the record is really a question as to
whether you need the audit log, or just the greatest time. There are
ways of selecting the maximum time entry and retaining the old
entries but it really depends on what you are trying to do.

The following trigger does just what you suggested. One good thing is
that if your insert fails for any reason, the delete is automatically
rolled back too. If you implement it this way, then it may be
advisable to consider an index on this composite key, otherwise your
inserts may take forever because each insert would need to do a table
scan.


CREATE TRIGGER "MYTRIGGER" FOR "MYTABLE"
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
DELETE FROM MYTABLE
WHERE ENTITY = NEW.ENTITY
AND PARENTID = NEW.PARENTID
AND UNIQUEID = NEW.UNIQUEID;
END
^



--- In firebird-support@yahoogroups.com, Michal ®eravík
<michalz@o...> 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.
>
> Thanx
> Michal