Very Computer

by **Jiang Qingshua** » Thu, 20 Feb 2003 00:18:45

In BEFORE INSERT TRIGGER, is there any method to terminate the insert
other than raise an error and SIGNAL SQLSTATE?

The requirement is to insert a batch of records, if the record is
duplicated then cancel the insert and go on with the next record. The
only method i can find is to cancel the insert by SIGNAL SQLSTATE, but
it will terminate the whole batch of records, which is not correct.

TIA.

by **Knut Stolz** » Thu, 20 Feb 2003 01:05:51

Jiang Qingshuang wrote on Tuesday 18 February 2003 16:18:

Quote:> In BEFORE INSERT TRIGGER, is there any method to terminate the insert
> other than raise an error and SIGNAL SQLSTATE?

> The requirement is to insert a batch of records, if the record is
> duplicated then cancel the insert and go on with the next record. The
> only method i can find is to cancel the insert by SIGNAL SQLSTATE, but
> it will terminate the whole batch of records, which is not correct.

Maybe creating a view over the table and defining an INSTEAD OF trigger on the
view which will implement your logic would be a way to go?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

by **Serge Riela** » Thu, 20 Feb 2003 01:25:34

In DB2 V8.1 for LUW create a VIEW on top of the target table. Then
create an INSTEAD OF trigger.
That gives you full controll.

BEFORE TRIGGERS cannot avoid the insert.

Cheers
Serge

>In BEFORE INSERT TRIGGER, is there any method to terminate the insert
>other than raise an error and SIGNAL SQLSTATE?

>The requirement is to insert a batch of records, if the record is
>duplicated then cancel the insert and go on with the next record. The
>only method i can find is to cancel the insert by SIGNAL SQLSTATE, but
>it will terminate the whole batch of records, which is not correct.

>TIA.

--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto

Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/