如何获得用户插入触发器?

Question

我一直在尝试编写一个触发器，检查程序中男性学生的数量不超过60%，但每次我实现它时，我都会得到new.column_name的无效标识符。

下面是与我的触发器

相关的主要表

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)
);

这是我要实现的触发器。

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;

下面是它显示的错误。

触发性别限制编译

线/坳误差

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

Answer 1

您的触发器似乎过于复杂，如果同时插入许多行，那么它将为每一行触发，并且可能会多次重新验证相同的数据。此外，作为BEFORE INSERT触发器，您不会计算正在添加的新学生，因此如果在插入之前男性人数少于60%，并且添加了大量男性，则触发器将不会触发。

引发错误的条件是total_males = (40*0.6)，这意味着如果它小于或大于该数量，它将不会触发，这似乎是错误的。

相反，您可以使用AFTER INSERT触发器并检查是否没有匹配的程序:

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;
/

小提琴

如果你只想检查有插入行的程序，那么你可以把触发器转换成复合触发器。