473,563 Members | 2,696 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Before insert Trigger

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
3 3450
OK I saw its not possible to update in a before trigger so I have to
find another workaround ...

Nov 12 '05 #2
to*******@frees urf.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
ok, took some time but now it works ...

thx!

Christian

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
22188
by: DTB | last post by:
I am having trouble creating an INSTEAD OF trigger in SQL Server to replicate a BEFORE UPDATE trigger from ORACLE. Here is a sample of the ORACLE BEFORE UPDATE trigger: CREATE TRIGGER myTRIGGER ON MYTABLE begin :new.DT := SYSDATE; if :new.NM is NULL then :new.NM := USER; end if; end myTRIGGER;
4
41572
by: brent.ryan | last post by:
How do I get the next int value for a column before I do an insert in MY SQL Server 2000? I'm currently using Oracle sequence and doing something like: select seq.nextval from dual; Then I do my insert into 3 different table all using the same uniqueID. I can't use the @@identity function because my application uses a connection pool...
3
8394
by: uninfmx | last post by:
Hi If one or mode records get deleted from t1 (see below), I'd like delete all the corresponding records from t2. There is no foreign key relationship between t2 and t1, so cascading delete is not an option. V8.2 AIX create table t1 ( x int
2
6426
by: 73blazer | last post by:
Perhaps my thinking is wrong but this is what I have: 1 table (Tab1) with 1 attribute (Attr1) Attr1 char(16) for bit data ----------------------------------------------- create trigger check no cascade before insert on Tab1 referencing new as N
2
1945
by: Robert Fitzpatrick | last post by:
Anytime I create an INSERT BEFORE trigger that includes a query on the same table as the trigger is assigned, the insert does not happen without error. I get 'INSERT 0 0'. It is like the query loses the insert information, is this something that can't be done? DECLARE checkit record; BEGIN SELECT INTO checkit...
5
3228
by: Mike Nolan | last post by:
I have a before insert trigger that updates a value in another table. It appears that I cannot depend upon that update having taken place in an after insert trigger on the first table. (The one with the insert.) Is there a way to force this or do I need to look for a different idea here? -- Mike Nolan
5
5342
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table schema1.emp ( fname varchar(15) not null, lname varchar(15) not null, dob date,
3
3471
by: Michel Esber | last post by:
Hello Environment: DB2 V8 LUW FP12. I have a function that returns a table. I am trying to use it inside a before trigger: create trigger TRG.T_MACHINE_RTM before insert on CAD.TBL_MACHINE
1
4028
by: gauravupreti | last post by:
Hi All, I have a table with a col. that accepts number. CREATE TABLE A (ID NUMBER NOT NULL) and another table with two columns as shown: CREATE TABLE B (ID NUMBER NOT NULL,
0
7664
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7885
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8106
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7638
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7948
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3626
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2082
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
923
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.