Hi,

Try:

CREATE EXCEPTION PREVENT_INSERTING 'The record was not inserted
because...';

And then, in you trigger:

...

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.
*/
EXCEPTION PREVENT_INSERTING;

end /* [2] */

end /* [1] */


Following was taken from the DataDefinition Guide, p.192:

Raising an exception in a trigger
To raise an existing exception in a trigger, use the following syntax:
EXCEPTION name;
where name is the name of an exception that already exists in the
database. Raising an
exception:

-Terminates the trigger, undoing any changes caused (directly or
indirectly) by the trigger.

-Returns the exception message to the application which performed the
action that fired
the trigger. If an isql command fired the trigger, the error message
is displayed on the
screen.
Note If an exception is handled with a WHEN statement, it will behave
differently. For
more information on exception handling, see Chapter 9, "Working with
Stored Procedures."


HTH,
Fabrice

--- In ib-support@y..., vp_email@y... wrote:
> 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@y...