create table Employee
(
fname Varchar2(10),
minit Varchar2(1),
lname Varchar2(10),
ssn Number(10) NOT NULL,
dbate Date,
address Varchar2(30),
sex Varchar2(1),
Salary Number(6) constraint check_sal CHECK(salary>=0),
superssn Number(10) NOT NULL DEFAULT 00,
dno Number(4),
constraint pssn PRIMARY KEY (ssn),
constraint fk_mgr FOREIGN KEY (superssn)
references Employee(ssn) ON DELETE set default ON UPDATE cascade
);

create table Project
(
pname Varchar2(20),
pnumber Number(2) NOT NULL,
plocation Varchar2(10),
dnum Number(4),
constraint pn PRIMARY KEY (pnumber),
constraint fk_dnum FOREIGN KEY (dnum)
references Department(dnumber) ON DELETE cascade ON UPDATE cascade
);

create table Works_On
(
essn Number(10),
pno Number(2),
hours Number(2),
constraint pesspn PRIMARY KEY (essn, pno),
constraint fk_pno FOREIGN KEY (pno)
references Project( pnumber)
);


CREATE OR REPLACE PACKAGE WorksPack
AS w_essn Number;
END;
/

CREATE OR REPLACE TRIGGER Wk1
BEFORE INSERT OR UPDATE ON WORKS_ON
FOR EACH ROW
BEGIN
WorksPack.w_essn := :new.essn;
END;
/

CREATE OR REPLACE TRIGGER Wk2
AFTER INSERT OR UPDATE ON WORKS_ON
DECLARE
totalhrs Number(6,2);
BEGIN
select sum(hours) into totalhrs
from Works_On
where essn=WorksPack.w_essn
group by essn;
IF (totalhrs) IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Total hours is null');
ELSIF (totalhrs < 25 ) THEN
RAISE_APPLICATION_ERROR(-20002,'Total hours is less than 25 hours');
ELSIF (totalhrs > 45) THEN
RAISE_APPLICATION_ERROR(-20003,'Total hours is more than 45 hours');
ELSE
dbms_output.put_line('Hours worked complies with company policy');
END IF;
END Wk2;
/


Is what I have done for the above trigger correct?
Works_On table references Employee and Project.
I need to fire the trigger on Works-on when a row is inserted/updated.
Thanks.

Close but not correct.
You have to consider the possibility of multiple inserted/updated rows in one SQL statement. Therefore, your 'w_essn Number ' variable must be an array!

Also, you will need a BEFORE trigger to initialize this array.

Incase, I want to stick with insert/update of 1 row at a time, I don't need an array. I would like to keep it as 1 update/insert at a time.
In that case, what should I do? How should I correct my code?I am getting the following error when I try to update hours-

ERROR at line 1:
ORA-04098: trigger 'WORKD_ON11' is invalid and failed re-validation


Why is the name of the trigger coming as WORKD_ON11 when the name of my triggers are WK1 and WK2?
Is there a command to show all the triggers I have created so far in my database?
Thanks,

You cannot assume an SQL statement will only insert/update one row!
What about something like the following:

update works_on
set hours=8
where pno=123;
????

To look at triggers query: ALL_TRIGGERS or USER_TRIGGERS or DBA_TRIGGERS views.