to*******@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
