At 03:29 PM 20/08/2004 +0000, you wrote:

>Hi,
>
> > In a trigger, if you can't solve the problem by handling
> > it, you *want* the exception to end the operation. That's the
>point of
> > triggers.
>but that's what I want to achieve - solve a problem of existing key.
>
>Will this work as a before insert trigger?
>
>/* trigger fire on insert row */
>/* if there is already a record with this key sum the value fields */
>/* known bug: if new or old value is null, the sum will be null */
>
> select 1 from "table" where "pk" = new.pk into :pk_var;
> if (:pk_var = 1) then begin
> select "value" from "table" where "pk" = new.pk into :value_var;
> new.value = new.value + value_var;
> delete from "table" where "pk" = new.pk;
> end
>/* proceed with insert operation */

<siiiigh>

NO.

An insert is an insert. An update is an update. The activity occurs while
the operation is actually happening. Use triggers for things you want to
occur during the operation.

What you want is to test (before *anything* happens) whether the row needs
to be inserted (because it doesn't exist) or updated (because it does
exist). By the time a trigger fires, it is TOO LATE.

The SP is the CORRECT way to do it:

CREATE PROCEDURE INS_OR_UPDATE (
PK integer;
VAL varchar(20))
as
begin
if (exists (select PK from aTable
where PK = :PK)) then
update aTable
set VAL = :VAL
where PK = :PK;
else
insert into aTable (PK, VAL)
values (:PK, :VAL);
end

Now, quite possibly, you'll have triggers that do stuff when the operation
happens (be it an insert or an update) - that stuff will happen when the SP
submits the insert or update request to the engine.

I am now going to bed. Good night.

./heLen