Generate a variable error message

Error number -746 allows you to provide the text of the error message. Like the preceding example, the following one also generates an error if new_qty is greater than old_qty multiplied by 1.50. However, in this case the error number is -746, and the message text Too many items for Mfr. is supplied as the third argument in the RAISE EXCEPTION statement. For more information on the syntax and use of this statement, see the RAISE EXCEPTION statement in Create and use SPL routines.

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 -746, 0, 'Too many items for Mfr.';
   END IF
END PROCEDURE;

If you use DB-Access to submit the triggering statement, and if new_qty is greater than old_qty, you will get the result that the following figure shows.

Figure 1. Error Number -746 with User-Specified message Text

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.

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

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














 746: Too many items for Mfr.

If you invoke the trigger through an SQL statement in an SQL API, the database server sets sqlcode to -746 and returns the message text in the sqlerrm field of the SQL communications area (SQL;CA). For more information about how to use the SQL;CA, see your SQL API publication.