Adding procedures to triggers...

[shorti]

Need some help understanding how to tie together a trigger and a
procedure together. I want to do a certain procedure if an insert
occurs on TABLE1:

TABLE1 contains COL1, COL2, COL3, COL4

CREATE TRIGGER trigger100 NO CASCADE BEFORE INSERT ON table1
REFERENCING NEW AS newdata FOR EACH ROW MODE DB2ROW
BEGIN
CALL procedure1(?, ?) handle RETURN here??

Then I have the procedure:

CREATE PROCEDURE procedure1(??)
RESULT SET 1
LANGUAGE SQL
BEGIN
....some code.....
IF newdata.COL1 < 7 is THEN
UPDATE an existing record using information from newdata
RETURN 2;
ELSEIF newdata.COL1 = 0 and an existing_record.COL1 = 0 THEN
UPDATE existing record.....
RETURN 1;
ELSEIF newdata.COL1 4 and newdata.COL2 8 THEN
INSERT the original request
RETURN 0;
END

The orignal request was:

INSERT INTO table1 (COL1, COL2, COL3, COL4) VALUES ( 4, 24, 'xxx',
'yyy')

so the questions are:

1) how does the procedure get the data from the original INSERT
statement since it is needed to determine if it should insert or
update a record? can this be passed in as a parameter?

2) How can I get the trigger to handle the various RETURN codes from
the procedure? I could add an IN rc INT to the parameter but I
noticed the RETURN option and was wondering how the caller gets this
info. I want to use the RETURN values to make the trigger SIGNAL an
sqlstate depending on the outcome.

I am sure I will have more questions but that will get me started in
the right direction I hope.

[Serge Rielau]

shorti wrote:

Need some help understanding how to tie together a trigger and a
procedure together. I want to do a certain procedure if an insert
occurs on TABLE1:

TABLE1 contains COL1, COL2, COL3, COL4

CREATE TRIGGER trigger100 NO CASCADE BEFORE INSERT ON table1
REFERENCING NEW AS newdata FOR EACH ROW MODE DB2ROW
BEGIN
CALL procedure1(?, ?) handle RETURN here??

Then I have the procedure:

CREATE PROCEDURE procedure1(??)
RESULT SET 1
LANGUAGE SQL
BEGIN
....some code.....
IF newdata.COL1 < 7 is THEN
UPDATE an existing record using information from newdata
RETURN 2;
ELSEIF newdata.COL1 = 0 and an existing_record.COL1 = 0 THEN
UPDATE existing record.....
RETURN 1;
ELSEIF newdata.COL1 4 and newdata.COL2 8 THEN
INSERT the original request
RETURN 0;
END

The orignal request was:

INSERT INTO table1 (COL1, COL2, COL3, COL4) VALUES ( 4, 24, 'xxx',
'yyy')

so the questions are:

1) how does the procedure get the data from the original INSERT
statement since it is needed to determine if it should insert or
update a record? can this be passed in as a parameter?

Yes, but you need to pas in each column separately:
CALL procedure1(newdata.col1, newdata.col2, ...)

>
2) How can I get the trigger to handle the various RETURN codes from
the procedure? I could add an IN rc INT to the parameter but I
noticed the RETURN option and was wondering how the caller gets this
info. I want to use the RETURN values to make the trigger SIGNAL an
sqlstate depending on the outcome.

Now there is one I haven't used in while. IIRC it's:
GET DIAGNOSTICS retval = RETURNS_STATUS;
You need to DECLARE retval INTEGER; after the BEGIN ATOMIC

DB2ROW? This is DB2 for iSeries?

>
I am sure I will have more questions but that will get me started in
the right direction I hope.

There are always more questions... ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[shorti]

>

There are always more questions... ;-)

Cheers
Serge

Ah, yes...you are ever so wise.....because here I am again with yet
another....

A side questions. When you do an OPEN cursor, the cursor is placed
before the record selected by the DECLARE, correct? Can that record
be accessed? In other words, can I SELECT from WHERE CURRENT OF that
cursor even though a FETCH has not been performed?

I want to retrieve data from the record before and after the one
specified in the UPDATE statement by the calling component.

TABLE1
---------------------------------------------
COL1 COL2
ROW1 1 2 <---c1 here upon OPEN
(I want to capture these values)
ROW2 4 8 <---c1 will be here
after FETCH
ROW3 10 12 <---have another cursor
capture record after perhaps

example:

DECLARE c1 CURSOR FOR
SELECT col1, col2 FROM table1 WHERE col1 = 4 AND col2 = 8;
OPEN c1 ; <---opens to record before the one specified
in declare
IF SQLCODE = 0 THEN
SET before_col1 = (SELECT col1 from table1 WHERE CURRENT OF c1)
<------can I do this?
SET before_col2 = (SELECT col2 from table1 WHERE CURRENT OF c1)
<-------can I do this?
FETCH c1 INTO this_col1, this_col2; <-----then move c1 to
the correct record to use later on
......do a bunch of stuff here...

[Serge Rielau]

Serge Rielau wrote:

CREATE TRIGGER trg BEFORE UPDATE ON T REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN (EXISTS (SELECT 1 FROM T
WHERE (N.c1 BETWEEN T.c1 AND T.c2
OR N.c2 BETWEEN T.c1 AND T.c2

OR N.c1 <= T.c1 AND N.c2 >= T.c2

)
AND NOT (O.c1 = T.c1 AND O.c2 = T.c2)))
SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Row range overlaps'

Small correction....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[shorti]

Thanks Serge,

I believe that would work and much shorter than my prodedure!