Hi,

I am trying to create a After Insert Trigger on a table.
In the trigger I required to call a stored procedure but its giving some error.

I am attaching the screenshots for your ready reference.

Please provide me some pointers regarding this.

Thanking you in anticipation.

Amit Kanodia

i dont think ..its the right ways to call procedure from Triggers

example:

CREATE TRIGGER trig1 AFTER UPDATE ON t1
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
WHEN (n.c1 > 100);
BEGIN ATOMIC
DECLARE rs INTEGER DEFAULT 0;
CALL proc(n.c1, n.c2);
GET DIANOSTICS rs = RETURN_STATUS;
VALUES(CASE WHEN rc < 0 THEN RAISE_ERROR('70001', 'PROC CALL failed'));
END@

check :
http://publib.boulder.ibm.com/infoce...d/t0011377.htm

Hi Rahul,

Thanks for ur reply. Actually I am new to DB2. Can you spare few minutes to explain the example you have quoted. For example the When Clause and passing the parameters to the proc as n.c1 etc.

Thanks & Regards,

Amit Kanodia

The WHEN clause contains a predicate which is evaluated first. If the predicate is evaluated to "true", then the trigger body is executed. Otherwise, the body is not executed. Essentually, this syntax is a left-over from the original implementation of triggers in DB2. IF statements achieve the same these days.

And as usual, have a look at the DB2 manuals because they explain the syntax and their semantics (although it is not always simple to find the right place): http://publib.boulder.ibm.com/infoce...n/r0000931.htm