SQL/MX 3.2 Guide to Stored Procedures in Java (H06.25+, J06.14+)

Invoking SPJs in NonStop SQL/MX

HP NonStop SQL/MX Release 3.2 Guide to Stored Procedures in Java—691166-001

5-17

Invoking SPJs in a Trigger

A trigger is a mechanism in the database that enables the database engine to perform

certain actions when a specified event occurs. SPJs are useful as triggered actions,

because they can help you encapsulate and enforce rules in the database. For more

information about the benefits of using SPJs, see Benefits of SPJs on page 1-4.

Nonstop SQL/MX supports a CALL statement in a trigger, provided that the SPJ in the

CALL statement does not have any OUT or INOUT parameters or return any result

sets. For information about OUT and INOUT parameters, see Returning Output Values

From the Java Method on page 3-2 and Output Parameter Arguments on page 5-6.

For more information about result sets, see Stored Procedure Result Sets on page 3-4.

Example

Consider a library environment, where each time a member borrows a book, the

member is charged a fee. If the fees exceeds the credit limit, the student is blocked.

The Database Administrator (DBA) needs to update the dues and block the students

who have crossed the limit of credit. Also, DBA needs to be informed if a user has

been blocked by some external means. One might think that a SIGNAL statement can

be used for this task to emit a message to the console for the same. The problem with

using SIGNAL statement in this case is that the encompassing transaction is aborted,

and therefore the dues will not be updated properly. DBA wants to update the dues but

at the same time, flag the member as being blocked. One solution is to use a CALL

statement and delegate the implementation of the external notification to a member-

defined routine.

Consider a stored procedure called INFORM_DBA_FUNC, that writes a message to a

file or sends an e-mail to the DBA with the list of members who have been blocked

(member names are sent as an argument to the routine).

CREATE TRIGGER INFORM_DBA AFTER UPDATE OF (ISBLOCKED) ON MEMBER

REFERENCING NEW AS NEWR

FOR EACH ROW

WHEN (NEWR.BLOCK <> 0)

CALL INFORM_DBA_FUNC (NEWR.MEMBERNAME);

For information about the CREATE TRIGGER syntax, see the HP Nonstop SQL/MX

Release 3.2 Reference Manual.