如何在Oracle SQL中通过触发器引发应用程序错误[关闭]

已关闭，此问题需要details or clarity。目前不接受答复。
**想改善这个问题吗？**通过editing this post添加详细信息并澄清问题。

昨天关门了。
Improve this question
考虑一下

table ABCD123
Having fields
is_alpha char(1) check(is_alpha in ('Y','N')),
is_alphanum char(1) check(is_alpha in ('Y','N')),
alpha varchar2(10),
no number ;

CREATE OR REPLACE TRIGGER check_trigger

BEFORE INSERT ON ABCD123

FOR EACH ROW

BEGIN

  IF :new.is_alpha = 'Y' AND (:NEW.alpha IS NULL) THEN

    RAISE_APPLICATION_ERROR(-20001, 'Error: Field cannot be null for is_chemo true.');

  END IF ; 

  

  IF :new.is_alphanum = 'Y' AND (:NEW.alpha IS NULL OR :NEW.no IS NULL ) THEN

    RAISE_APPLICATION_ERROR(-20001, 'Error: Field cannot be null for is_alphanum true.');

  END IF; 

END;

如果我尝试插入值Y到is_alpha，Y到is_alphanum，然后离开字段而不输入任何内容，那么我预计错误将是

ORA-20001 Error:Field cannot be null for is_alpha true.'
ORA-20001 Error:Field cannot be null for is_alphanum true.'

一个选项可能是为各种缺失值设置一个 flag，然后决定显示哪个消息。因为，你的代码将引发它遇到的第一个错误，所有其他错误都将被忽略。

SQL> CREATE TABLE abcd123
  2  (
  3     is_alpha      CHAR (1) CHECK (is_alpha IN ('Y', 'N')),
  4     is_alphanum   CHAR (1), 
  5     alpha         VARCHAR2 (10),
  6     no            NUMBER
  7  );

Table created.

修改触发器：

SQL> CREATE OR REPLACE TRIGGER check_trigger
  2     BEFORE INSERT
  3     ON abcd123
  4     FOR EACH ROW
  5  DECLARE
  6     l_alpha_missing     NUMBER (1) := 0;
  7     l_alphanum_missing  NUMBER (1) := 0;
  8     l_msg               VARCHAR2 (200);
  9  BEGIN
 10     IF     :new.is_alpha = 'Y'
 11        AND (:new.alpha IS NULL)
 12     THEN
 13        l_alpha_missing := 1;
 14     END IF;
 15
 16     IF     :new.is_alphanum = 'Y'
 17        AND (   :new.alpha IS NULL
 18             OR :new.no IS NULL)
 19     THEN
 20        l_alphanum_missing := 1;
 21     END IF;
 22
 23     l_msg :=
 24        CASE
 25           WHEN     l_alpha_missing = 1
 26                AND l_alphanum_missing = 1
 27           THEN
 28              'Error: ALPHA and NO values are missing, while IS_ columns are TRUE'
 29           WHEN     l_alpha_missing = 1
 30                AND l_alphanum_missing = 0
 31           THEN
 32              'Error: ALPHA is missing, while IS_ALPHA is TRUE'
 33           WHEN     l_alpha_missing = 0
 34                AND l_alphanum_missing = 1
 35           THEN
 36              'Error: NO is missing, while IS_ALPHANUM is TRUE'
 37        END;
 38
 39     IF l_msg IS NOT NULL
 40     THEN
 41        raise_application_error (-20001, l_msg);
 42     END IF;
 43  END;
 44  /

Trigger created.

测试：

SQL> INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('Y', 'Y');
INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('Y', 'Y')
            *
ERROR at line 1:
ORA-20001: Error: ALPHA and NO values are missing, while IS_ columns are TRUE
ORA-06512: at "SCOTT.CHECK_TRIGGER", line 37
ORA-04088: error during execution of trigger 'SCOTT.CHECK_TRIGGER'

SQL> INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('Y', 'N');
INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('Y', 'N')
            *
ERROR at line 1:
ORA-20001: Error: ALPHA is missing, while IS_ALPHA is TRUE
ORA-06512: at "SCOTT.CHECK_TRIGGER", line 37
ORA-04088: error during execution of trigger 'SCOTT.CHECK_TRIGGER'

SQL> INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('N', 'Y');
INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('N', 'Y')
            *
ERROR at line 1:
ORA-20001: Error: NO is missing, while IS_ALPHANUM is TRUE
ORA-06512: at "SCOTT.CHECK_TRIGGER", line 37
ORA-04088: error during execution of trigger 'SCOTT.CHECK_TRIGGER'

SQL> INSERT INTO abcd123 (is_alpha, is_alphanum) VALUES ('N', 'N');

1 row created.

SQL> INSERT INTO abcd123 (is_alpha, alpha) VALUES ('Y', null);
INSERT INTO abcd123 (is_alpha, alpha) VALUES ('Y', null)
            *
ERROR at line 1:
ORA-20001: Error: ALPHA is missing, while IS_ALPHA is TRUE
ORA-06512: at "SCOTT.CHECK_TRIGGER", line 37
ORA-04088: error during execution of trigger 'SCOTT.CHECK_TRIGGER'

SQL> INSERT INTO abcd123 (is_alpha, alpha) VALUES ('Y', 'Littlefoot');

1 row created.

SQL>

如何在Oracle SQL中通过触发器引发应用程序错误[关闭]

1条答案

相关问题

热门标签

最新问答