Hi i am struggling with a trigger in Oracle 9i. i have a statement to which i have to create the SQL for the answer. the statement is:
A member can have on loan a maximum of 5 items at any one time.
The table that is mainly included in this is:
CREATE TABLE hire(
hireID INTEGER PRIMARY KEY,
memberID INTEGER NOT NULL,
stockID INTEGER NOT NULL,
staffID INTEGER NOT NULL,
date_hired DATE NOT NULL,
date_due DATE NOT NULL,
amount_paid VARCHAR2(6) NOT NULL,
CONSTRAINT fk_mID FOREIGN KEY (memberID) references member(memberID),
CONSTRAINT fk_hirestkID FOREIGN KEY (stockID) references stock(stockID),
CONSTRAINT fk_hirestfID FOREIGN KEY (staffID) references staff(staffID));
My trigger so far is:
CREATE OR REPLACE TRIGGER
FOR EACH ROW
SELECT member.memberID, COUNT (*)
WHERE member.memberID ='&memberID'
AND hire.date_return is null
GROUP BY (memberID)
IF (dummy < 5)
INSERT INTO hire
PRINT 'Member has too many items on loan.'
I can't get it to work & i don't know if it is even right.
Please can you help me
Because, variable should have a datatype; SELECT should have INTO clause; INSERT statement is incomplete; what is BEGIN doing within your IF-THEN-ELSE; what is PRINT?
There's just too many wrong things here which imply that you have no idea what you are doing (and how to do it). Writing a (working) trigger would be counter-productive because you wouldn't learn anything from it. I believe you should pay more attention during classes (it is some kind of a homework, isn't it?), learn how to use PL/SQL and then do the job.