Hi,
I need to create a trigger that will trap the insert commands on a
table and if the row already exists, it updates the information.
I started with this exemple but im getting syntax error "An unexpected
token "CREATE TRIGGER Test_INS INSTEAD OF" was found following
"BEGIN-OF-STATEMENT"."
CREATE TRIGGER Test_INS INSTEAD OF INSERT ON v_TESTEXPORTTOD B2
REFERENCING NEW AS NewData
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF exists (select 1 from TESTEXPORTTODB2 where INTTEST =
NewData.INTTEST ) then
UPDATE GOHIERBE.TESTEX PORTTODB2
set DTETEST = NewData.DTETEST ,
STRTEST = NewData.STRTEST
where INTTEST = NewData.INTTEST ;
ELSE
INSERT INTO TESTEXPORTTODB2 VALUES (NewData.INTTES T,
NewData.DTETEST , NewData.STRTEST );
END IF;
END
This is the table:
CREATE TABLE TestExportToDB2
(
intTest INTEGER,
dteTest date,
strTest varchar (50)
)
This is the view:
Create view SELECT * from TestExportToDB2
Any one knows what i am doing wrong?
Thank you for your help
END OF POST
1  3110 
1. Q: Are you on DB2 UDB V8 for Linux, Unix and Windows? That's a prereq.
2. Q; What is your statement termination character? try db2 -td% and use
% at the end.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: joe |
last post by:
hi i am trying to write a insted of insert trigger to
create a unique id when i insert a record in my database.
can anyone give me an example with out using identity. thanks
|
by: Stuart McGraw |
last post by:
Is Microsoft full of #*$#*% (again) or am I badly misunderstanding
something?
Quote from Microsoft's T-SQL doc:
> INSTEAD OF triggers are executed instead of the triggering action.
> These triggers are executed after the inserted and deleted tables
> reflecting the changes to the base table are created, but before any
> other actions are taken. They are executed before any constraints,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> so can perform...
|
by: Dan |
last post by:
I am loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. When the process is
finished, I'll have only good rows in B, and exeption rows in C.
I am investigating INSTEAD OF triggers, however my question to the
group is, is there a better or best practice for this scenario? This
must be common. Any high-level tips...
|
by: William of Ockham |
last post by:
Hi,
I was asked to recreate a new clean database for our developers because
the current one they use is not entirely up to date. So I created a new
database and I run into the followin strange problem. First some facts:
System: DB2 V8.1 Fixpack5 Redhat Linux 8.0 dual processor
Database A is the current database and all DDL I currently have executes
fine. Database B is the new one and is created on the same instance as A.
|
by: Chris |
last post by:
I'm attempting to get Instead Of triggers working. My platform in
Solaris, DB2 verison 8.1.x (not sure which).
If I create two simple tables with 1 row each:
create table test_cc_1 (col1 char(2), col2 integer);
insert into test_cc_1 values ('ME', 1);
create table test_cc_2 (col1 char(2), int integer, ltime timestamp);
insert into test_cc_2 values('ME',293,NULL);
| | |
by: leedo |
last post by:
Hi,
I am almost going crazy with this. I have a table that I bulk insert into from another database using VS2005.
I need to change the data in the records before committing the values in the fields. To do that I created an (Instead of Insert) trigger.
The trigger basically check conditions using IF statements and accordingly does the appropriate action, which is simply switching values between two fields (swapping).
My problem is...
|
by: zachster17 |
last post by:
Hello all,
I have 2 tables (tblAddProviderProfessional and tblAddProvider). There are other tables such as tblAddProviderFacility and so forth. tblAddProvider is a table that has similar data for all types of requests to add provider into the database. The other tables (i.e. tblAddProviderProfessional) has data that is unique to Professionals being added into the database. I'm trying my first attempt at Instead of triggers and can't quite...
|
by: Maryan |
last post by:
Hi everybody,
i have a view, which contains some columns of two tables. I would like to insert informtions to this view but since this view is created through a join, it is impossible to insert informations to it.
So i decided to use an instead of trigger to do that but i don't know how?
CREATE TABLE Person (
PersonID integer NOT NULL,
Lastname varchar(255) NOT NULL,
Firstname ...
|
by: asf93555 |
last post by:
Running under SQL2000 I can not get an INSTEAD trigger to function. I've even copied the example directl from books online - no joy . . .
Server: Msg 170, Level 15, State 1, Procedure IO_Trig_INS_Employee, Line 2
Line 2: Incorrect syntax near 'INSTEAD'.
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
