0

I am currently working on a project to simulate a Nursing Home database. So what I want is if a new resident is entered into the database, the trigger will fire and loop through the number of rooms until it finds a room where Occupant has a null value. The trigger should then set the new resid.id as the occupant value and the room_num as the room_num value on the new resident row. Of course if the for loop doesn't find a a vacant room it should output a message saying so.

I apologise for my sloopy code but I've been messing around with this one for a while and haven't been getting any results. At the moment my current error is 

Error at line 6: PL/SQL: ORA-00923: FROM keyword not found where expected

Would really appreciate any help you lovely people could offer ! 

Create OR REPLACE TRIGGER Room_Assign
BEFORE INSERT ON Residents
FOR EACH ROW

BEGIN
FOR Room_Num in 1..MAX(Room_Num) loop
if Rooms.Occupant = NULL
THEN
update Rooms Set Occupant = new.Resid_ID where Occupant = NULL;
SELECT Rooms.Room_Num INTO Residents.Room_Num;
Exit;

END IF;
END loop;
IF new.Resid_ID = NULL
 DBMS_OUTPUT.PUT_LINE("There are no vacant rooms.")
 END;
 /
Improve this question

2 Answers 2

Reset to default
2

In any kind of enterprise, even the enterprise of "my own fun little project", this would be a prime example of "how to make applications unusable by adding triggers".

Do not use a trigger.

Create a stored procedure, ADD_RESIDENT(), and do all of your logic in that procedure: insert into resident table (including tracking-columns noting time of insert, account which did the insert, etc.), define a cursor that selects all unoccupied rooms FOR UPDATE, fetch the first row returned, update that row with your occupant, close your cursor, and raise exception if no unoccupied rooms were found. Put everything you want in this procedure: debugging, auditing, logging, whatever - just no COMMIT and no "EXCEPTION WHEN OTHERS".

Ensure your end-user-facing application account has read-only access to your database tables, and can only make changes (i.e. insert residents) via execution of stored procedures like this. If you haven't created a separate user_account for this role, pause and do so now.

I promise you will find yourself spending less time building your application in this way - even a single "toy" application built just for your edification. And I also promise you'll be on far fewer !@#$-lists, should any other developers ever come along to contribute to your application.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Cheers I'll make a procedure out of it !
0

It seems you are missing FROM Room in the select clause. Try this:

Create OR REPLACE TRIGGER Room_Assign
BEFORE INSERT ON Residents
FOR EACH ROW

BEGIN
FOR Room_Num in 1..MAX(Room_Num) loop
if (Rooms.Occupant IS NULL)
THEN
update Rooms Set Occupant = new.Resid_ID where Occupant IS NULL;
SELECT Rooms.Room_Num INTO Residents.Room_Num FROM Rooms;
Exit;

END IF;
END loop;
IF (new.Resid_ID IS NULL)
 DBMS_OUTPUT.PUT_LINE("There are no vacant rooms.");
END IF;
END;
 /
Improve this answer

6 Comments

'Error at line 6: PL/SQL: ORA-00933: SQL command not properly ended' Is my new error but we're making progress! :)
Yeah, that's everything I have so far
New error: 'Error at line 14: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; The symbol ";" was substituted for "END" to continue.' I added in and END IF statement after the print statement
Edited again. Pls, check.
Back to Error at line 6: PL/SQL: ORA-00923: FROM keyword not found where expected I think I'm gonna take a stab at converting it into a procedure
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.