By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

Before insert Trigger

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


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

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
ok, took some time but now it works ...

thx!

Christian

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.