Hello!

I have a before insert Trigger and I want to catch if there is a
duplicate Key Error. If the Key already exists I'd like to update else
insert the row.

OK I am at the point I did the updates but if I raise an Error the
update get rolled back.

What do I have to define in the Trigger after the updates?

THX!
Christian Meier

OK I saw its not possible to update in a before trigger so I have to
find another workaround ...

tomtailor@freesurf.fr wrote:
> Hello!
>
> I have a before insert Trigger and I want to catch if there is a
> duplicate Key Error. If the Key already exists I'd like to update else
> insert the row.
>
> OK I am at the point I did the updates but if I raise an Error the
> update get rolled back.
>
> What do I have to define in the Trigger after the updates?
You won't be able to bend an INSERT to a MERGE using a BEFORE trigger.
The only way I can think up to achieve what you want is to use an
INSTEAD OF trigger. Today these triggers are defined on VIEWs only.

So: CREATE VIEW T AS SELECT * FROM TBASE;
CREATE TRIGGER trg1 INSTEAD OF INSERT ON T
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
MERGE INTO TBASE
USING VALUES(N.PK, N.c1, N.c2) AS S(PK, C1, C2)
ON TBASE.PK = S.PK
WHEN NOT MATCHED THEN INSERT VALUES(S.PK, S.C1, S.C2)
WHEN MATCHED THEN UPDATE
SET (C1, C2) = (S.C1 + TBASE.C1, S.C2. + TBASE.C2)

That ought to do....
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

ok, took some time but now it works ...

thx!

Christian