DB2 Syntax for calling stored procedure within a trigger

budimirn · #1 (**permalink**) 10-21-10, 00:11

Hi,

Running DB2 v9.7 with full text search enabled and I am new to databases

I am trying to create an insert\update\delete trigger(s) on a table that contains a XML column.

Within that trigger I am trying to call a stored procedure which would update an index for that XML column.

This is what I have done so far:

I have created the index and am able to update it manually using:

Code:

db2ts "UPDATE INDEX myschema.myindex FOR TEXT"

I am able to test the indexing by performing an db2-fn:xmlcolumn-contains function and successfully retrieve the data I am after.

I tried updating the index using the following stored procedure:

Code:

CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', ?)  ;

That worked as expected.

I then tried to create a trigger that does nothing, but get triggered on an insert to my XML column.

That worked fine as well. I than attempted to combine the trigger with the stored procedure call with the following code:

Code:

CONNECT TO <database-name>^
CREATE TRIGGER <schema-name>."INDEXTRIGGER" AFTER INSERT ON <schema-name>.<table-name> REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
WHEN ( N.CID > 0 ) // *NOTE
BEGIN ATOMIC
CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', ?) ;
END^
CONNECT RESET^

Quote:

*I have no idea what condition to specify that would always evaluate to true besides testing that my auto increment “CID” (which has a default value of 1) is always greater than 0

I got the following error message:

Quote:

SQL0418N A statement contains a use of
an untyped parameter marker or a null value that is not valid.
LINE NUMBER=4. SQLSTATE=42610

If I understand this right some\all of the parameters within the call to the stored procedure are untyped and possibly need to be cast to whatever type the should be.

As I said I new to databases let alone to triggers\stored procedures. So I have no idea what I need to do to resolve this problem.

Could someone please tell me what I need to change within the call to the stored procedure to get it working within a trigger.

If the call worked on its own when run in the CLR why is it not working when placed within a trigger? Is the error message telling me that: at time of execution there is no way to know what the type of the parameters ‘…’ is and thus they need to be CAST to …?

Any help would be appreciated.

n_i · #2 (**permalink**) 10-21-10, 07:23

Quote:

BEGIN ATOMIC
CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', ?) ;
END^

You need to declare a variable which will serve as the output parameter of the stored procedure, the one you indicate with a question mark when calling the SP from the command line processor. I doubt you can declare variables in a trigger though, so you may have to create a wrapper SP, without parameters, to call SYSPROC.SYSTS_UPDATE.

budimirn · #3 (**permalink**) 10-22-10, 01:03

That did the job.

I tried before posting to declare the parameters but within the trigger and it was telling me that I couldn’t do that. I should have stuck with it and done it in a separate procedure.

I did the following and it worked.

Code:

CREATE PROCEDURE SP1
LANGUAGE SQL
BEGIN ATOMIC
DECLARE param5 VARCHAR(10000);
CALL SYSPROC.SYSTS_UPDATE('myschema', 'myindex', '', 'en_US', param5) ;
End

Than called that procedure from within my trigger.

Code:

CONNECT TO <database-name>
CREATE TRIGGER <schema-name>."INDEXTRIGGER" AFTER INSERT ON <schema-name>.<table-name> REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
WHEN (N.CID > 0)
BEGIN ATOMIC
CALL <shema-name>.SP1
END^
CONNECT RESET^

Thanks for your help

Internet Services

Web Development

Digital Marketing

Consumer Tech