Trigger INSERTING into a row

oracleplsqltrigger

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?

Best Answer

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.

Related Question