Sfrgttk

I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one.

CREATE TABLE STUDENT(

SID     CHAR(7),

SName       VARCHAR2(20),

SMajor      CHAR(3)

CHECK (SMajor in ('CSC', 'MIS', 'TDC')),

CONSTRAINT PK_STUDENT

PRIMARY KEY (SID) 

);







CREATE TABLE MAJOR(

MName       CHAR(3) PRIMARY KEY,

MCount      NUMBER(3) ); 

INSERT INTO MAJOR VALUES ('CSC', 0);

INSERT INTO MAJOR VALUES ('MIS', 0);

INSERT INTO MAJOR VALUES ('TDC', 0);

SELECT * FROM MAJOR;

COMMIT;

My trigger:

CREATE OR REPLACE TRIGGER addingS

AFTER INSERT ON STUDENT

    FOR EACH ROW

    DECLARE 

        counter NUMBER (3);

    BEGIN

        DBMS_OUTPUT.PUT_LINE('Adding student.');

        counter := counter + 1;

        UPDATE MAJOR

        SET MCOUNT = counter

        WHERE MCOUNT = 0; 

    END;

    /

It is not updating, now my MCOUNT's value is empty, why is that?

I think that counter is NULL because you don't assign it a value. And after you increment it, nothing changes because NULL + 1 = NULL. Also your UPDATE will not work if MCOUNT is not 0.

Unrelated: I suggest to use a foreign key instead of CHECK.

You define a variable, counter. However, you never set it to a value, which means its value is NULL. 1 + NULL yields NULL.

Note that this won't work as you want, anyway:

• Even if you initialize counter to 0, that going to be done for each row. So, for every student, you'll have counter set to 1, and you'd set MCOUNT to 1, not the number of students with the major so far.


• Since you're only setting MCOUNT if it is currently zero, even if counter was set to the number of students with the major, the UPDATE statement would only be run once, and MCOUNT would still always be 1.


• Finally, you're not checking the students SMAJOR, and only updating the MAJOR record for that major. Whatever value MCOUNT holds for one major, it will hold for all of them.

A couple of notes:

• You can use the current value of MCOUNT to update MCOUNT. You do need to either make sure MCOUNT is either never NULL, or treat a NULL value in MCOUNT as a zero.


• You need to make sure you're only updating MCOUNT for the major you're concerned with.

Something like

    UPDATE MAJOR

       SET MCOUNT = NVL(MCOUNT, 0) + 1

     WHERE MAJOR = NEW.SMAJOR

should work well.

Also, of course, note that you need to handle changing counts if a student changes their major, or if a student is removed from the system.

For starters, you could use a trigger that picks up the mcount value from table MAJOR, and increments it (via an UPDATE) whenever a student is added to the STUDENT table. However, this will raise a NO_DATA_FOUND exception when the MAJOR table is empty. Thus, we start populating the MAJOR table (with an INSERT) in this situation.

-- create the 2 tables, then compile the trigger



create or replace trigger addstudent

  after insert on student

  for each row

declare

  counter number(3) := 0 ;

begin

  select mcount into counter

  from major

  where mname = upper( :new.smajor ) 

  for update ;



  dbms_output.put_line( 'Student added to ' || :new.smajor || ' group.' );

  counter := counter + 1;

  update major

  set mcount = counter

  where mname = :new.smajor ; 

exception

  when no_data_found then

    dbms_output.put_line( 'Table MAJOR: no entry for ' || :new.smajor || '!' ) ;

    insert into major ( mname, mcount ) values ( :new.smajor, 1 ) ;

    dbms_output.put_line( 'Table MAJOR: row for ' || :new.smajor || ' INSERTed.') ;    

end;

/



Trigger ADDSTUDENT compiled

Testing

begin

  insert into student ( sid, sname, smajor ) values ( 'a1', 'Frank', 'CSC' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a2', 'Lorna', 'CSC' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a3', 'Colin', 'CSC' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a4', 'Harriet', 'MIS' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a5', 'Michael', 'MIS' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a6', 'Dorothy', 'MIS' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a7', 'Harriet', 'TDC' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a8', 'Michael', 'TDC' ) ;

  insert into student ( sid, sname, smajor ) values ( 'a9', 'Dorothy', 'TDC' ) ;

end ;

/



-- output

Table MAJOR: no entry for CSC!

Table MAJOR: row for CSC INSERTed.

Student added to CSC group.

Student added to CSC group.

Table MAJOR: no entry for MIS!

Table MAJOR: row for MIS INSERTed.

Student added to MIS group.

Student added to MIS group.

Table MAJOR: no entry for TDC!

Table MAJOR: row for TDC INSERTed.

Student added to TDC group.

Student added to TDC group.



SQL> select * from major;

MNAME  MCOUNT  

CSC    3       

MIS    3       

TDC    3  

See dbfiddle here.

Even if this works initially, it may be better to use a VIEW (and ditch the trigger(s)). Maybe you don't need the MAJOR table at all ...

create or replace view major_vw

as

select 

  smajor

, count(smajor) as mcount

from student

group by smajor 

;



-- testing

SQL> select * from major_vw;

SMAJOR  MCOUNT  

MIS     3       

CSC     3       

TDC     3 

See dbfiddle.