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.