Oracle trigger before insert if statement

Question

I've got the following trigger to check if there are available beds in a table called WARD before the user tries to input a value, if not then an error will be shown.

create or replace 
trigger checkBeds
  before insert or update on INPATIENT_STAY for
    each row
Begin
select BEDSAVAIL from WARD where wardnum=:new.WARDNUM;
if(BEDSAVAIL <=0) then
DBMS_OUTPUT.PUT_LINE('There are no beds available.');
end if;
END;

when this is run I get the following error:

Error(2,42): PLS-00049: bad bind variable 'NEW.WARDNUM'

WARD is linked to INPATIENT_STAY by a foreign key in INPATIENT_STAY called WARDNUM

Here's the structure of the WARD table

CREATE TABLE WARD 
(
  WARDNUM VARCHAR2(20 BYTE) NOT NULL 
, NAME VARCHAR2(40 BYTE) 
, LOCATION VARCHAR2(20 BYTE) 
, TOTALBEDS NUMBER 
, TELEXTENTION VARCHAR2(20 BYTE) 
, BEDSAVAIL NUMBER 
, CONSTRAINT WARD_PK PRIMARY KEY 
  (
    WARDNUM 
  ));

here's the structure of the INPATIENT_STAY table

CREATE TABLE INPATIENT_STAY 
(
  INPATIENTID VARCHAR2(20 BYTE) NOT NULL 
, PATIENTNUMBER VARCHAR2(20 BYTE) 
, WADNUM VARCHAR2(20 BYTE) 
, DATEONWAITINGLIST DATE 
, EXPECTEDDURATION VARCHAR2(20 BYTE) 
, EXPECTEDLEAVEDATE DATE 
, DATEPLACEDINWARD DATE 
, REQWARD VARCHAR2(20 BYTE) 
, DATELEFT DATE 
, CONSTRAINT INPATIENT_STAY_PK PRIMARY KEY 
  (
    INPATIENTID 
  )
);

How do I resolve this?

Dba · Accepted Answer · 2013-11-01 03:38:29Z

3

You need to select the value of BEDSAVAIL to a local variable and compare with it.

Try like this,

CREATE OR REPLACE 
TRIGGER checkbeds
BEFORE INSERT OR UPDATE ON inpatient_stay 
FOR EACH ROW
DECLARE 
     l_bedsavail ward.bedsavail%TYPE;
BEGIN
     SELECT bedsavail 
     INTO   l_bedsavail
     FROM   ward 
     WHERE  wardnum=:NEW.wadnum;
     IF(l_bedsavail <= 0) THEN
          raise_application_error( -20001, 'There are no beds available.'); --to restrict the insetion`.
     END IF;
END;
/

Edited:

There is a mistake in the column_name in INPATIENT_STAY. The name is WADNUM instead of WARDNUM. Please rename the column name in the table or made changes in the Trigger.

I have updated my answer with column_name WADNUM.

WHERE  wardnum=:NEW.wadnum;

To restrict the insertion or updation on table INPATIENT_STAT, you can raise an application error like,

raise_application_error( -20001, 'There are no beds available.')

edited Nov 1, 2013 at 3:38

answered Nov 1, 2013 at 2:39

Dba

6,6791 gold badge26 silver badges33 bronze badges

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

5 Comments

Dba Over a year ago

Are you sure the column WARDNUM is there in tables WARD & INPATIENT_STAY?

Dba Over a year ago

Or please verify that your columns are created without double quotes. Please share your complete create table script for WARD AND INPATIEND_STAY.

Pindo Over a year ago

The trigger compiles ok, but i am still allowed to insert data if the bedsavail is 0. how do i prevent this?

Dba Over a year ago

You mean you need to restrict the insertion on table INPATIENT_STAY , if ward.bedsavail <= 0?

Dba Over a year ago

@Pindo, to restrict the insertion, you can raise an application error instead of DBMS_OUTPUT. See the updated answer.

Collectives™ on Stack Overflow

Oracle trigger before insert if statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related