INSTEAD OF INSERT ON

[Alex]

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_TESTEXPORTTODB2
REFERENCING NEW AS NewData
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

IF exists (select 1 from TESTEXPORTTODB2 where INTTEST =
NewData.INTTEST) then

UPDATE GOHIERBE.TESTEXPORTTODB2
set DTETEST = NewData.DTETEST,
STRTEST = NewData.STRTEST
where INTTEST = NewData.INTTEST;

ELSE

INSERT INTO TESTEXPORTTODB2 VALUES (NewData.INTTEST,
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

[Knut Stolze]

Alex wrote:

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_TESTEXPORTTODB2
REFERENCING NEW AS NewData
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

IF exists (select 1 from TESTEXPORTTODB2 where INTTEST =
NewData.INTTEST) then

UPDATE GOHIERBE.TESTEXPORTTODB2
set DTETEST = NewData.DTETEST,
STRTEST = NewData.STRTEST
where INTTEST = NewData.INTTEST;

ELSE

INSERT INTO TESTEXPORTTODB2 VALUES (NewData.INTTEST,
NewData.DTETEST, NewData.STRTEST);

END IF;

END

This works fine for me on DB2 V8.1 FP2. Maybe you had some weird special
character at the beginning of your statement, which doesn't show in your
text editor?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Claus Samuelsen]

In DB2 8.1.2 there's a new SQL statement MERGE INTO

From the fine manual:

Example 5: Using dynamically supplied values for an employee row, update
the master employee table if the data corresponds to an existing
employee, or insert the row if the data is for a new employee. The
following example is a fragment of code from a C program.

hv1 =
"MERGE INTO employee AS t
USING TABLE(VALUES(CAST (? AS CHAR(6)), CAST (? AS VARCHAR(12)),
CAST (? AS CHAR(1)), CAST (? AS VARCHAR(15)),
CAST (? AS SMALLINT), CAST (? AS INTEGER)))
s(empno, firstnme, midinit, lastname, edlevel, salary)
ON t.empno = s.empno
WHEN MATCHED THEN
UPDATE SET
salary = s.salary
WHEN NOT MATCHED THEN
INSERT
(empno, firstnme, midinit, lastname, edlevel, salary)
VALUES (s.empno, s.firstnme, s.midinit, s.lastname, s.edlevel,
s.salary)";
EXEC SQL PREPARE s1 FROM :hv1;
EXEC SQL EXECUTE s1 USING '000420', 'SERGE', 'K', 'FIELDING', 18, 39580;

Alex wrote:

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_TESTEXPORTTODB2
REFERENCING NEW AS NewData
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

IF exists (select 1 from TESTEXPORTTODB2 where INTTEST =
NewData.INTTEST) then

UPDATE GOHIERBE.TESTEXPORTTODB2
set DTETEST = NewData.DTETEST,
STRTEST = NewData.STRTEST
where INTTEST = NewData.INTTEST;

ELSE

INSERT INTO TESTEXPORTTODB2 VALUES (NewData.INTTEST,
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