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

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Triggers and External Stored Procedures That Make SQL Calls

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” on page 717), Teradata Database 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.