Conditional logic in triggers... (if/else in a trigger)

[Lee Francis Wilhelmsen]

Hi

I want to create a trigger that does the following:

When a new row is inserted into table1 then update a row in table2. If the
row doesn't exist in table2 then create a new row.

My problem is the latter. Is there any way to get a trigger to first look
for a specific table row then create it if it can't find it?
Basically, is it possible to perform an if/else in a trigger...?

Best regards
Lee Francis Wilhelmsen

--
In theory, there is no difference between theory and
practice. But, in practice, there is.
-- Jan L.A. van de Snepscheut

[Serge Rielau]

Hi.

Yes, in V7.2 (non MPP) you can use IF THEN ELSE,

BEGIN ATOMIC
DECLARE rowcount INT;
....
UPDATE t SET ... WHERE ;
GET DIAGNOSTICS rowcount = ROW_COUNT;
IF rowcount = 0 THEN
INSERT ....
END IF;
END

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

[Lee Francis Wilhelmsen]

> Yes, in V7.2 (non MPP) you can use IF THEN ELSE,

First off, what's non MPP?

> BEGIN ATOMIC
> DECLARE rowcount INT;
> ....
> UPDATE t SET ... WHERE ;
> GET DIAGNOSTICS rowcount = ROW_COUNT;
> IF rowcount = 0 THEN
> INSERT ....
> END IF;
> END

Having trouble with the syntax. Seems the DECLARE statement is causing
trouble. Most of the errors I get have something to do with "An unexpected
token "END-OF-STATEMENT" was found following ... so I assume there's a
semicolon missing somewhere...

My trigger looks something like this:

CREATE TRIGGER new_hit
AFTER INSERT ON hit_test
REFERENCING NEW AS n_row
FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC
DECLARE rowcount INT;

UPDATE test SET count = count + 1
WHERE id = n_row.id
AND test.key = n_row.key
AND test.month = n_row.month
AND test.year = n_row.year;

GET DIAGNOSTICS rowcount = ROW_COUNT;
IF rowcount = 0 THEN

INSERT INTO test VALUES(1, n_row.key, n_row.month, n_row.year, 12);
END IF;
END;

Any idea what can be wrong? And where can I find detailed documentation
about this topic? Not really much in the DB2 documentation.... few examples

Lee Francis

[Lee Francis Wilhelmsen]

It seems the errors I'm getting have something to do with my DB2 Command
Center client because I can't get an example (I found from the IBM site) to
run either...

It seems no matter what I do I can't get the colons correct... It won't stop
complaining

For a "DECLARE current_due DECIMAL(10,2) DEFAULT 0;" statement, I get

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "0" was found following "ECIMAL(10,2)
DEFAULT".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=5.
SQLSTATE=42601

I'm starting to have nightmares about these DB2 client tools - they never
seem to work like you would expect.

Lee Francis

[Lee Francis Wilhelmsen]

> I'm starting to have nightmares about these DB2 client tools - they never
> seem to work like you would expect.

I found an article at
http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid
458709_tax285651,00.html and followed the advice. I replaced the termination
character from ";" to "#" and then it worked!!!

Now I'm so happy just to have beat the SQL trigger compiler that I have yet
to test if the actual code works as expected! Oh well, I hope this
information helps someone else...

regards
Lee Francis

[Serge Rielau]

Hi Lee,
to answer your previous question:
DB2 V7.2 EEE (with more than one node) does not yet support IF THEN ELSE.
MPP stands for Massive Parallel Processing which is the infrastructure DB2 EEE
uses.