Triggers and External Stored Procedures That Make SQL Calls - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
Product Category
Teradata Vantage™
If an external stored procedure uses CLIv2 or JDBC to submit an SQL statement that is not allowed to be executed from within a trigger (see Triggered Action Statements), the system returns one of the following:
  • A failure message to a CLIv2-based external stored procedure.
  • An exception to a JDBC-based external stored procedure.

The system returns the message to the external procedure to provide it with an opportunity to post the error and then to either cleanly close or disconnect any external files or connections it had established. The only remaining course of action is for the procedure to return something to the caller.

The following outcomes occur depending on what the external stored procedure returns.

IF the external procedure … THEN …
returns with its own error it can set the SQLSTATE to its own exceptions code and the original fail condition will not be known to the caller of the procedure.
returns no error (the SQLSTATE value is ‘00000’) the system returns the original failure to the caller.
attempts to submit another request the system returns an error because the procedure submitted a request after receiving a trigger fail message.

The system terminates the triggering request in all of these cases.

The following table documents examples of three possible outcomes for the following scenario.

Suppose a trigger is defined as follows.

CREATE TRIGGER trig2
AFTER INSERT ON t2
FOR EACH ROW (CALL sqlxspex(2););

You then submit the following two requests from the external stored procedure using either CLIv2 or JDBC function calls, where the table named ThisIsNotATable does not exist:

INSERT INTO t2 VALUES (1,1);
DELETE FROM ThisIsNotATable ALL;

Because there is no table named ThisIsNotATable in the current database, the system returns an error to the procedure.

IF the procedure … THEN the outcome is as follows …
returns its own error the error message reports whatever SQLSTATE code and text message is coded in the procedure, for example:
*** Failure 7504 in UDF/XSP/UDM rgs.sqlxspex: SQLSTATE U0001:
                Statement# 1, Info =0
*** Total elapsed time was 1 second.

Note that the outcome of this example is not determined by the system, but is entirely dependent on how the procedure is coded.

The example is designed to show how an external procedure handles the error the system returns to the trigger. In this case, the procedure was coded to set the SQLSTATE to ‘U0001’ for a 7504 error, but any valid SQLSTATE code and user-written text message could have been used.

returns with a SQLSTATE of ‘00000’ the error message reports the original error that was reported to the external stored procedure:
*** Failure 3807 SQLXSPEX:Object 'ThisIsNotATable' does not exist.
                Statement# 1, Info =0
*** Total elapsed time was 1 second.

In this case, the original error is the failure of the DELETE request that the procedure attempted to execute against a table that does not exist.

ignores the error and attempts to submit another SQL request the procedure is terminated with the following error message:
*** Failure 7836 The XSP mydbase.sqlxspex submitted a request subsequent to receiving a trigger fail message.
                Statement# 1, Info =0
*** Total elapsed time was 1 second.