How to get the user insert for a trigger quarry?-CodePudding

Hey so I've been trying to make a trigger that checks the amount of males students in a program does not exceed 60% but every time I implement it I get an invalid identifier for the new.column_name. Here are the main tables that are involved with my trigger

CREATE TABLE Program (
ProgID number,
ProgName varchar(50) NOT NULL,
FacID number NOT NULL,
PRIMARY KEY (ProgID),
FOREIGN KEY (FacID) REFERENCES Faculty (FacID)
);

CREATE TABLE Student (
StudID number,
Fname varchar(50) NOT NULL,
Lname varchar(50) NOT NULL,
Gender varchar(50) NOT NULL Constraint  gen 
check(regexp_like(Gender,'^[M][a][l][e]$') or regexp_like(Gender, '^[F][e][m][a][l][e]$')),
Age int NOT NULL, 
Nationality varchar(50) NOT NULL,
Phone_Number number (8) NOT NULL UNIQUE,
Email varchar(50) NOT NULL UNIQUE, 
Sponser_Type varchar(50) NOT NULL,
Fee_Percentage_Paid number DEFAULT 0 NOT NULL, 
PRIMARY KEY (StudID));

CREATE TABLE Lecturer (
LecID number, 
Fname varchar(50) NOT NULL, 
Lname varchar(50) NOT NULL,
Gender varchar(50) NOT NULL, 
Age int NOT NULL,
Nationality varchar(50) NOT NULL,
Phone_Number number(8) NOT NULL UNIQUE,
Email varchar(50) NOT NULL UNIQUE, 
FacID number NOT NULL, 
PRIMARY KEY (LecID),
FOREIGN KEY (FacID) REFERENCES Faculty (FacID)
 );
 
CREATE TABLE Class (
ProgID number(10) NOT NULL,
LecID number NOT NULL, 
StudID number NOT NULL UNIQUE, 
Member_No number NOT NULL,
FOREIGN KEY (ProgID) REFERENCES Program (ProgID),
FOREIGN KEY (StudID) REFERENCES Student (StudID)
);

Here is the trigger I'm trying to implement.

delimiter $$
Create or REPLACE trigger gender_limit 
Before insert 
On Class 
For each row
Declare 
total_males NUMBER;
  Cursor counter IS select count (StudId) from (Select * from Class where ProgID = New.ProgID) a  
where StudID IN (Select StudID from Student where Gender = 'Male');  
BEGIN
OPEN counter;
FETCH counter INTO total_males;
    If (total_males = (40*0.6)) THEN    
    Begin
         Raise_application_error(-20003, 'Too many male students'); 
         Rollback;
    End;
    End if;
Close counter;
End$$
delimiter;

` And here is the error it shows.

Trigger GENDER_LIMIT compiled

LINE/COL ERROR

3/19 PL/SQL: SQL Statement ignored 3/82 PL/SQL: ORA-00904: "NEW"."PROGID": invalid identifier

CodePudding user response：

Your trigger seems overly complicated and if many rows are inserted at the same time then it is going to trigger for every row and may, potentially, re-validate the same data multiple times. Additionally, as a BEFORE INSERT trigger you are not going to count the new students being added so if you have fewer than 60% males before the insert and add a large number of males then the trigger will not fire.

Your condition for raising an error is total_males = (40*0.6) which means that it will not fire if it is either less than or greater than that amount, which seems wrong.

Instead, you could use an AFTER INSERT trigger and check that there are no programs that match:

CREATE TRIGGER gender_limit 
AFTER INSERT ON CLASS 
DECLARE
  has_too_many_males NUMBER;
BEGIN
  SELECT 1
  INTO   has_too_many_males
  FROM   class c
         INNER JOIN student s
         ON (c.studid = s.studid)
  GROUP BY c.progid
  HAVING COUNT(CASE s.gender WHEN 'Male' THEN 1 END) / COUNT(*) >= 0.6
  FETCH FIRST ROW ONLY;

  Raise_application_error(-20003, 'Too many male students'); 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Nothing matching found.
    NULL;
END;
/

fiddle

If you only wanted to check the programs with inserted rows then you could convert the trigger into a compound trigger.