tomtailor@freesurf.fr wrote:[color=blue]
> 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?[/color]
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
Before insert Trigger 
