Sql觸發器不起作用

CREATE OR REPLACE TRIGGER STUDENT
BEFORE INSERT
ON STUDENT

FOR EACH ROW
BEGIN
IF :NEW.ENO =NULL THEN
RAISE_APPLICATION_ERROR(-21900, 'ENROLL_NO IS MISSING');
END IF;
END;

所以這是我的觸發器定義但是當我嘗試 NULL 值時它被接受

我在學

uj5u.com熱心網友回復：

不是= null，而是is null：

SQL> CREATE OR REPLACE TRIGGER trg_bi_student
  2    BEFORE INSERT ON STUDENT
  3    FOR EACH ROW
  4  BEGIN
  5    IF :NEW.ENO is NULL THEN
  6       RAISE_APPLICATION_ERROR(-21900, 'ENROLL_NO IS MISSING');
  7    END IF;
  8  END;
  9  /

Trigger created.

SQL> insert into student (eno, name) values (1, 'Little');

1 row created.

SQL> insert into student (eno, name) values (null, 'Foot');
insert into student (eno, name) values (null, 'Foot')
            *
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -21900 is out of
range
ORA-06512: at "SCOTT.TRG_BI_STUDENT", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_STUDENT'


SQL>

不過，為什么要觸發？這是一個not null約束（很可能是主鍵）：

SQL> drop table student;

Table dropped.

SQL> create table student (eno number constraint pk_student primary key,
  2                        name varchar2(10));

Table created.

SQL> insert into student (eno, name) values (1, 'Little');

1 row created.

SQL> insert into student (eno, name) values (null, 'Foot');
insert into student (eno, name) values (null, 'Foot')
                                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT"."ENO")


SQL>

從您的第二次觸發（發表在評論中）開始：如果您顯示錯誤，則：

SQL> CREATE OR REPLACE TRIGGER TOTALMARKS
  2  BEFORE INSERT ON STUDENT
  3  FOR EACH ROW
  4  BEGIN
  5  :NEW.TOTAL: =:NEW.S1 :NEW.S2 :NEW.S3;
  6  DBMS_OUTPUT.PUT_LINE('TOTAL="| | :NEW. TOTAL) ;
  7  END;
  8  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER TOTALMARKS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/11     PLS-00103: Encountered the symbol " " when expecting one of the
         following:
         := . ( @ % ; indicator

SQL>

這是觸發器可執行部分的第二行：

:NEW.TOTAL: =:NEW.S1 :NEW.S2 :NEW.S3;
1234567890123456
          ^
          |
       11th character

它說你應該使用:=而不是: =（即之間沒有空格），但是你得到另一個錯誤：

SQL> CREATE OR REPLACE TRIGGER TOTALMARKS
  2  BEFORE INSERT ON STUDENT
  3  FOR EACH ROW
  4  BEGIN
  5  :NEW.TOTAL :=:NEW.S1 :NEW.S2 :NEW.S3;
  6  DBMS_OUTPUT.PUT_LINE('TOTAL="| | :NEW. TOTAL) ;
  7  END;
  8  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER TOTALMARKS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/22     PLS-00103: Encountered the symbol "TOTAL="| | :NEW. TOTAL) ;
         END;" when expecting one of the following: (...)

您不能將字串括在雙引號中 - 使用單引號。但是，還有另一個錯誤（連接的連續管道符號），還有另一個錯誤（和之間沒有空格:new）.total，直到最終編譯：

SQL> CREATE OR REPLACE TRIGGER TOTALMARKS
  2  BEFORE INSERT ON STUDENT
  3  FOR EACH ROW
  4  BEGIN
  5  :NEW.TOTAL :=:NEW.S1 :NEW.S2 :NEW.S3;
  6  DBMS_OUTPUT.PUT_LINE('TOTAL='|| :NEW.TOTAL) ;
  7  END;
  8  /

Trigger created.

SQL>

標籤：sql 甲骨文 甲骨文10g

上一篇：如何使用DRF從serializerMethodField僅回傳指定欄位

下一篇：檢索僅具有特定列值的表的行