Sql trigger not functioning-CodePudding

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;

So this was my trigger definition But when I try to NUll values its accepted

I'm learning

CodePudding user response：

It is not = null, but 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>

Though, why trigger? That's a not null constraint (most probably a primary key):

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>

As of your second trigger (posted in a comment): if you show errors, then:

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>

This is the 2nd line in excecutable part of the trigger:

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

It says that you should've used := and not : = (i.e. no space in between), but then you get another error:

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: (...)

You can't enclose strings into double quotes - use single ones. But, there's another error (consecutive pipe sign for concatenation), and yet another (no space between :new and .total), until finally it compiles:

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>