Apply a fixed error message

You can apply error number -745 to any trigger failure that is not an SQL error. The following fixed message is for this error: -745 Trigger execution has failed.

You can apply this message with the RAISE EXCEPTION statement in SPL. The following example generates error -745 if new_qty is greater than old_qty multiplied by 1.50:

CREATE PROCEDURE upd_items_p2()
   DEFINE GLOBAL old_qty INT DEFAULT 0; 
   DEFINE new_qty INT;
   LET new_qty = (SELECT SUM(quantity) FROM items); 
   IF new_qty > old_qty * 1.50 THEN
      RAISE EXCEPTION -745;
   END IF
END PROCEDURE

If you are using DB-Access, the text of the message for error -745 displays on the bottom of the screen, as the following figure shows.

Figure 1: Error message -745 with fixed message

Press CTRL-W for Help 
SQL: New Run  Modify  Use-editor  Output  Choose Save  Info  Drop  Exit
Modify the current SQL statements using the SQL editor.

--------------------- stores8@myserver --------- Press CTRL-W for Help ----

INSERT INTO items VALUES( 2, 1001, 2, 'HRO', 1, 126.00);

















  745: Trigger execution has failed.

If your trigger calls a procedure that contains an error through an SQL statement in your SQL API, the database server sets the SQL error status variable to -745 and returns it to your program. To display the text of the message, follow the procedure that your HCL® OneDB® application development tool provides for retrieving the text of an SQL error message.