SQL Trigger help

[transplantedNYr]

So I have these two very basic tables.

CODE PROJNAME
------------ --------------
101 ProjectX
222 ProjectY
355 ProjectZ
973 ProjectAlpha

ID NAME PROJCODE HOURS
----- ------------ -------------- ------------
55055 Smith 101 20
55055 Smith 222 10
39002 Bob 973 25
00001 Preston 355 5
10000 Logan 355 5
00777 Bond 222 20

When an employee is removed from the second table and leaves no employee working on any project we should remove that project from the first table.

So for example if I delete job 355 nothing happens because one person is still working on that project.

Now when I delete ProjCode 101 or update ProjCode 101 to some other existing project I should remove this Code/projName from First table.

Then the tables will look as such.

CODE NAME
------------ --------------
222 ProjectY
355 ProjectZ
973 ProjectAlpha

ID NAME PROJCODE HOURS
----- ------------ -------------- ------------
55055 Smith 973 20
55055 Smith 222 10
39002 Bob 973 25
10000 Logan 355 5
00777 Bond 222 20

I got the hint part of the problem.
Start with a query that returns the value of projects not being worked on by any person. (Not in the intersect Oracle uses minus for this).

--Start my trigger
CREATE OR REPLACE TRIGGER DeleteProject AFTER DELETE ON ASSIGNMENT
FOR EACH ROW
BEGIN

--findvalue to be removed. Example if 101 changed or deleted, that is the value the following query will return. Also before any updates or deletes I verified that 0 rows returned for below query.

SELECT code FROM Project
minus
SELECT ProjCode FROM assignment
WHERE NOT Exists (SELECT * FROM Project WHERE ID = Code);

My real question is how do I use the above to delete 101 from the first table. Here is my two tries that were unsuccessful.

CREATE OR REPLACE TRIGGER DeleteProject AFTER DELETE ON ASSIGNMENT
FOR EACH ROW
BEGIN

SELECT code FROM Project
minus
SELECT ProjCode FROM assignment
WHERE NOT Exists (SELECT * FROM Project WHERE ProjCode = Code);

IF (code IS NOT NULL) THEN (Tried code > 0 to no avail)
DELETE FROM PROJECT WHERE (ProjCode = code);
END IF;
END;

Try 2
CREATE OR REPLACE TRIGGER DeleteProject AFTER DELETE ON ASSIGNMENT
FOR EACH ROW
BEGIN

SELECT code FROM Project
minus
SELECT ProjCode FROM assignment
WHERE NOT Exists (SELECT * FROM Project WHERE ID = Code);

This is the part I am having trouble with.

DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || code);

IF (projCode IS NOT NULL) THEN
DELETE FROM PROJECT WHERE (ProjCode = code);
END IF;
END;

Also tried: :(

CREATE OR REPLACE TRIGGER DeleteProject AFTER DELETE ON ASSIGNMENT
FOR EACH ROW
Declare
delCode NUMBER(5,0);
BEGIN
SELECT code into delCode FROM Project
minus
SELECT ProjCode FROM assignment
WHERE Exists (SELECT * FROM Project WHERE delCode = Code);

IF (delCode IS NOT NULL) THEN
DELETE FROM PROJECT;
DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
END IF;
END;

[transplantedNYr]

Some progress made.

Previously I could not get the "print statement to work within the if statement". Now the print statement works, implying that my condition of the trigger is working.

The below code works as delCode is greater than 1. I added a second query to give me the value of code to be deleted as well as the code to find the count of the same query. This query with the count function is what opened up the if correctly to print what I wanted.

Now I just need to figure out how to delete the value from the table.

CREATE OR REPLACE TRIGGER DeleteProject AFTER UPDATE or DELETE ON ASSIGNMENT
FOR EACH ROW
Declare
countCode integer;
delCode integer;
BEGIN
select count(code) into countCode FROM (SELECT code FROM Project
minus
SELECT ProjCode FROM assignment);

select code into delCode FROM Project
minus
SELECT ProjCode FROM assignment;

IF (countCode > 0) THEN
DELETE FROM PROJECT WHERE code = :old.projcode;
DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
END IF;
END;