CODEWITHSUNDEEP
oracle-databasetriggers
Pindo
Pindo

Reputation: 1625

oracle trigger to update row

I've got the following trigger:

create or replace 
trigger updateBeds
  After insert or update on INPATIENT_STAY for
    each row
Begin
if(:new.INPATIENT_STAY.DATEPLACEDINWARD<=sysdate and :new.INPATIENT_STAY.expectedleavedate>=sysdate)
then 
  update WARD
    set BEDSAVAIL = TOTALBEDS - 1
    where WARDNUM = :new.INPATIENT_STAY.WARDNUM;
end if;
if(:new.INPATIENT_STAY.DATELEFT <= sysdate) then
  update WARD
    set BEDSAVAIL = TOTALBEDS +1
    where WARDNUM = :new.INPATIENT_STAY.WARDNUM;
end if;
END;

This trigger is meant to subtract 1 from the column totalbeds if the INPATIENT_STAY.DATEPLACEDINWARD and the INPATIENT_STAY.expectedleavedate is less than the system date. Also, if the INPATIENT_STAY.DATELEFT is less than or equal to the system date, the totalbeds should increase by 1. WARD is linked to INPATIENT_STAY by a foreign key in INPATIENT_STAY called WARDNUM

When i try to compile the trigger, i get the following errors:

Error(2,4): PLS-00049: bad bind variable 'NEW.INPATIENT_STAY'
Error(2,54): PLS-00049: bad bind variable 'NEW.INPATIENT_STAY'
Error(6,21): PLS-00049: bad bind variable 'NEW.INPATIENT_STAY'
Error(8,4): PLS-00049: bad bind variable 'NEW.INPATIENT_STAY'
Error(11,21): PLS-00049: bad bind variable 'NEW.INPATIENT_STAY'

I'm not sure what I'm doing wrong here. any help is much appreciated

The tables are:

 WARD
    WARDNUM VARCHAR2(20 BYTE)
    NAME VARCHAR2(40 BYTE)
    TOTALBEDS NUMBER
    BEDSAVAIL NUMBER

INPATIENT_STAY
   INPATIENTID VARCHAR2(20 BYTE)
   WARDNUM VARCHAR2(20 BYTE)
   EXPECTEDLEAVEDATE DATE
   DATEPLACEDINWARD DATE
   DATELEFT DATE

Upvotes: 0

Views: 329

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

To access columns' values from the inserted/updated row, just use :new.column_name - you shouldn't add the table name between the :new and column name, try this:

create or replace 
trigger updateBeds
  After insert or update on INPATIENT_STAY for
    each row
Begin
if(:new.DATEPLACEDINWARD<=sysdate and :new.expectedleavedate>=sysdate)
then 
  update WARD
    set BEDSAVAIL = TOTALBEDS - 1
    where WARDNUM = :new.WARDNUM;
end if;
if(:new.DATELEFT <= sysdate) then
  update WARD
    set BEDSAVAIL = TOTALBEDS +1
    where WARDNUM = :new.WARDNUM;
end if;
END;

Upvotes: 1

Related Questions