Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 15: Creating and Using Triggers

Home Contents Index Master Index New Book

Generating Error Messages

When a trigger fails because of an SQL statement, the database server returns the SQL error number that applies to the specific cause of the failure.

When the triggered action is an SPL routine, you can generate error messages for other error conditions by using one of two reserved error numbers. The first one is error number -745, which has a generalized and fixed error message. The second one is error number -746, which allows you to supply the message text, up to a maximum of 71 characters.

Applying 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,



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.



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



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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

     
745: Trigger execution has failed.

Figure 15-4
Error Message -745 with Fixed Message

as Figure 15-4 shows.

If you trigger the erring routine 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 routine that your Informix application development tool provides for retrieving the text of an SQL error message.

Generating 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 Chapter 14, "Creating and Using 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 Figure 15-5 shows.



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.

Figure 15-5
Error Number -746 with User-Specified Message Text

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 (SQLCA). See the manual for your SQL API for in-depth information about using the SQLCA.

Informix Guide to SQL: TutorialChapter 15: Creating and Using Triggers Home Contents Index Master Index New Book

Generating Error Messages

Applying a Fixed Error Message

Generating a Variable Error Message

Informix Guide to SQL: Tutorial
Chapter 15: Creating and Using Triggers

Home Contents Index Master Index New Book