在 SQL 触发器中使用 SIGNAL

SIGNAL SQL 语句可以在触发器程序中用于向应用程序显示警告或错误。缺省情况下，当触发器程序使用 SIGNAL 产生错误时，应用程序将看到 SQLCODE -723 ， SQLSTATE 为 "09000"。通过访问 SQL 诊断区域的第二个条件区域，在 SIGNAL 语句上编码的用户提供的 SQLSTATE 也可供应用程序使用。

在以下示例中，我们将为 SALES 表建立 BEFORE UPDATE 触发器。

触发器包含在其中一个更新值显示为过大时将阻止更新完成的逻辑。触发器不是简单地导致更新失败，而是向正在执行更新的应用程序发出诊断信息信号。 SIGNAL 语句会发送用户特定的 SQLSTATE 值 'ZZT0P' 以及故障信息文本。


CREATE OR REPLACE TRIGGER TOYSTORE.SALES_UPDATE_TRIG
    BEFORE UPDATE ON TOYSTORE.SALES
    REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW FOR EACH ROW MODE DB2ROW
  BEGIN
    DECLARE V_MESSAGE_TEXT VARCHAR(1000);
    IF (NEW_ROW.SALES > 10000) THEN
      SET V_MESSAGE_TEXT = 'User: ' CONCAT USER CONCAT ', did you really sell: ' CONCAT
            NEW_ROW.SALES CONCAT ' units?';
      CALL SYSTOOLS.LPRINTF(V_MESSAGE_TEXT);
      SIGNAL SQLSTATE 'ZZT0P'
        SET MESSAGE_TEXT = V_MESSAGE_TEXT;
    END IF;
  END;

以下 SQL 过程执行 UPDATE ，这将导致触发触发器。将对处理程序进行编码，以从诊断区域中抽取详细信息，包括用户指定的 SQLSTATE 和消息文本。更新失败时，处理程序会抽取多个错误以了解触发器强制实施的失败。请注意，必须先运行 GET DIAGNOSTICS 语句以收集第一个和第二个条件区域的信息，然后再运行另一个 SQL 语句。运行任何 SQL 语句后，诊断区域包含有关最新 SQL 语句的信息。这就是在获取有关错误的所有条件信息之后必须定位 CALL 语句的原因。

CREATE OR REPLACE PROCEDURE TOYSTORE.UPDATEIT ()
BEGIN
  DECLARE NUMBER_OF_CONDITIONS INTEGER;
  DECLARE FIRST_SQLCODE INTEGER;
  DECLARE FIRST_SQLSTATE CHAR(5);
  DECLARE FIRST_MESSAGE_TEXT VARCHAR(200);
  DECLARE SECOND_SQLCODE INTEGER;
  DECLARE SECOND_SQLSTATE CHAR(5);
  DECLARE SECOND_MESSAGE_TEXT VARCHAR(200);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    GET DIAGNOSTICS NUMBER_OF_CONDITIONS = NUMBER;
    GET DIAGNOSTICS CONDITION 1
        FIRST_SQLCODE = DB2_RETURNED_SQLCODE, FIRST_SQLSTATE = RETURNED_SQLSTATE,
        FIRST_MESSAGE_TEXT = MESSAGE_TEXT;
    IF (NUMBER_OF_CONDITIONS = 2) THEN
      GET DIAGNOSTICS CONDITION 2
          SECOND_SQLCODE = DB2_RETURNED_SQLCODE, SECOND_SQLSTATE = RETURNED_SQLSTATE,
          SECOND_MESSAGE_TEXT = MESSAGE_TEXT;
      CALL SYSTOOLS.LPRINTF('Condition 2 SQLCODE:' CONCAT SECOND_SQLCODE CONCAT ' SQLSTATE:' CONCAT
          SECOND_SQLSTATE CONCAT ' and MESSAGE:' CONCAT SECOND_MESSAGE_TEXT);
    END IF;
    CALL SYSTOOLS.LPRINTF('Condition 1 SQLCODE:' CONCAT FIRST_SQLCODE CONCAT ' SQLSTATE:' CONCAT
        FIRST_SQLSTATE CONCAT ' and MESSAGE:' CONCAT FIRST_MESSAGE_TEXT);
  END;
  UPDATE TOYSTORE.SALES
    SET SALES = SALES + 20000
    WHERE SALES_PERSON = 'Forstie';
END;

最后，我们调用该过程。

CALL TOYSTORE.UPDATEIT();

当我们检查作业记录时，我们会看到标准的初始故障，其中 SQLCODE -723 和 SQLSTATE '09000' 出现在第一个条件区域中。在这种情况下，还有第二个条件区域，其中包含 SQLCODE -438 和用户特定的 SQLSTATE 'ZZT0P' 。

通过了解并使用 SIGNAL 和 GET DIAGNOSTICS ，应用程序可以使用此精细逻辑来使用来自触发器的错误信息。