Oracle 9i SQL*Plus problem

Using Oracle 9i SQL*Plus I am trying to write a trigger which will fire if more than 20 entities are "posted" into a particular table. Here is the code I have so far:

CREATE OR REPLACE TRIGGER q2
BEFORE UPDATE OR INSERT ON BED

DECLARE
num_of_beds VARCHAR2(2);

begin

SELECT COUNT(*) INTO num_of_beds FROM BED WHERE Ward_name = ????;

IF num_of_beds >= 20 THEN

DBMS_OUTPUT.PUT_LINE('Trigger fired');

END IF;

end;

Does anyone know the SQL needed to replace the question marks so that the trigger would detect which ward has been specified if a user runs an INSERT command?

Is it :new.ward_name?

I am doing a university assignment, so DB interaction will be slight (no more than one user at a time!) and the tables I am using are as follows:

DROP TABLE STAFF;
DROP TABLE BED;
DROP TABLE WARD;

CREATE TABLE STAFF
(
Staff_id varchar2(10) NOT NULL,
Name varchar2(35) NOT NULL,
address VARCHAR2(50) NOT NULL,
teleno varchar2(10) NOT NULL,
NIno varchar2(10) NOT NULL,
Notes VARCHAR2(500) NOT NULL,
Department Varchar2(20),
Staff_responsibility Varchar2(20),
Date_started Date,
Date_left Date,
CONSTRAINT STAFF_PK
PRIMARY KEY (Staff_id)
);

CREATE TABLE BED
(
Bed_no INTEGER NOT NULL,
Ward_name varchar2(10) NOT NULL,
Centre_name varchar2(10) NOT NULL,
CONSTRAINT BED_PK
PRIMARY KEY (Bed_no,Ward_name,Centre_name)
);

CREATE TABLE WARD
(
Ward_name varchar2(10) NOT NULL,
Centre_name varchar2(10) NOT NULL,
CONSTRAINT WARD_PK
PRIMARY KEY (Ward_name,Centre_name)
);

And I have to write a trigger to enforce the following constraint:

There must be no more than 20 beds to a ward.

I am planning to implement 3 wards for test purposes.

Any help would be much appreciated!

Cheers :-)

You cannot user new or old option if the defined trigger is table level, you have to make followoing modification in your trigger to convert it into row level trigger then use old and new keyword:

CREATE OR REPLACE TRIGGER q2
BEFORE UPDATE OR INSERT ON BED
FOR EACH ROW
DECLARE
num_of_beds VARCHAR2(2);

begin

SELECT COUNT(*) INTO num_of_beds FROM BED WHERE Ward_name = :new.ward_name;

IF num_of_beds >= 20 THEN

DBMS_OUTPUT.PUT_LINE('Trigger fired');

END IF;

end;



Regards,