A trigger to find the sum of one field in a different table and error if it's over a certain value in oracle

I have two tables

moduleprogress which contains fields:

studentid

modulecode

moduleyear

modules which contains fields:

modulecode

credits

I need a trigger to run when the user is attempting to insert or update data in the moduleprogress table.

The trigger needs to:

1. look at the studentid that the user has input and look at all modules that they have taken in moduleyear "1".
2. take the modulecode the user input and look at the modules table and find the sum of the credits field for all these modules (each module is worth 10 or 20 credits).
3. if the value is above 120 (yearly credit limit) then it needs to error; if not, input is ok.

Does this make sense? Is this possible?

@a_horse_with_no_name

This looks like it will work but I will only be using the database to input data manually so it needs to error on input. I'm trying to get a trigger similar to this to solve the problem(trigger doesn't work) and forget that "UOS_" is before everything. Just helps me with my database and other functions.

CREATE OR REPLACE TRIGGER  "UOS_TESTINGS"     
BEFORE UPDATE OR INSERT ON UOS_MODULE_PROGRESS    
REFERENCING NEW AS NEW OLD AS OLD    
DECLARE    
    MODULECREDITS INTEGER;    
BEGIN    
    SELECT    
            m.UOS_CREDITS,    
            mp.UOS_MODULE_YEAR,    
            SUM(m.UOS_CREDITS)    
    INTO    MODULECREDITS    
    FROM    UOS_MODULE_PROGRESS mp JOIN UOS_MODULES m    
    ON      m.UOS_MODULE_CODE = mp.UOS_MODULE_CODE    
    WHERE   mp.UOS_MODULE_YEAR = 1;    


    IF MODULECREDITS >= 120 THEN    
        RAISE_APPLICATION_ERROR(-20000, 'Students are only allowed to take upto 120 credits per year');    
    END IF;    
END;  

I get the error message :

8 23 PL/SQL开发者_如何学Go: ORA-00947: not enough values

4 1 PL/SQL: SQL Statement ignored

I'm not sure I understand your description, but the way I understand it, this can be solved using a materialized view, which might give better transactional behaviour than the trigger:

CREATE MATERIALIZED VIEW LOG 
  ON moduleprogress WITH ROWID (modulecode, studentid, moduleyear) 
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG 
  ON modules with rowid (modulecode, credits) 
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_module_credits
REFRESH FAST ON COMMIT WITH ROWID
AS
SELECT pr.studentid,
       SUM(m.credits) AS total_credits
FROM moduleprogress pr 
  JOIN modules m ON pr.modulecode = m.modulecode
WHERE pr.moduleyear = 1
GROUP BY pr.studentid;

ALTER TABLE mv_module_credits
   ADD CONSTRAINT check_total_credits CHECK (total_credits <= 120)

But: depending on the size of the table this might however be slower than a pure trigger based solution.

The only drawback of this solution is, that the error will be thrown at commit time, not when the insert happens (because the MV is only refreshed on commit, and the check constraint is evaluated then)