Hi, All!

I have a question.
How can I prevent inserting a record into a table from a trigger?

I have a table:

create table T1 (
ID integer not null,
SomeText varchar(20),
UpdateTime TimeStamp,
constraint pkID primary key (ID)
);

I don't want to allow a record to be inserted into the table,
if there is a record in the table already, that has the same ID
and value in the filed 'UpdateTime' is greater than value of
the record that is going to be inserted.

For example, there are records in the T1:

ID SomeText UpdateTime
1 'aaa' '01.01.2000'
2 'bbb' '02.01.2000'
3 'ccc' '03.01.2000'

and follow records are supposed to be inserted into it:

1 'ddd' '31.12.1999'
2 'eee' '03.01.2000'
4 'fff' '01.01.2000'

as the result, T1 must contain:

1 'aaa' '01.01.2000' <- NOT UPDATED !!!
2 'eee' '03.01.2000' <- updated
3 'ccc' '03.01.2000'
4 'fff' '01.01.2000' <- inserted

There is a trigger, that fires, when the records are inserted:

create trigger tr_1 for T1 active before insert position 0
as
declare variable UpdTme TimeStamp;
begin

select UpdateTime
from T1
where ID = New.ID
into :UpdTme;

if (:UpdTme is null) then begin /* [1] */
/*
There is NO record with the same ID in the table,
do nothing - record is inserted
*/
end
else begin

if (:UpdTme < New.UpdateTime) then begin /* [2] */

/*
There IS a record with the same ID in the table,
and it has 'UpdateTime' less than the new record has.
So, do deleting of the old record and enable new
record to insert
*/

delete
from T1
where ID = New.ID;

end
else begin

/*
There IS a record with the same ID in the table,
and it has 'UpdateTime' greater (or equal) than the
new record has.
So, it is necessary to prevent inserting the new
record here.

--==[ HOW TO DO THAT? ]==--

How to kill the new record in silence mode?
This operation MUST NOT influence on other changes, made
in the same transaction.
*/

end /* [2] */

end /* [1] */


end


I don't know what way the records will be inserted.
It can be SQL statement executed in IBConsole,
it can be:
'insert into T1
select ID, SomeText, UpdateTime from T2'
statement, or BatchMove operation.

Any suggestions?

Thank you.

Sorry my english.

Best regards, Vadfim Panov
vp_email@...