Trigger INSERTING into a row

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.