plsqltoad

A column of my table is showing invalid identifier in toad


I am getting the following error on my trigger

[Warning] ORA-24344: success with compilation error
3/62    PL/SQL: ORA-00904: "PEOPLE"."FLATNO": invalid identifier
3/9     PL/SQL: SQL Statement ignored
7/66    PL/SQL: ORA-00904: "PEOPLE"."FLATNO": invalid identifier
7/9     PL/SQL: SQL Statement ignored
 (2: 0): Warning: compiled but with compilation errors

This is my trigger:

SET SERVEROUTPUT ON;
CREATE OR REPLACE TRIGGER TUTU
BEFORE INSERT OR DELETE OR UPDATE ON PEOPLE
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        UPDATE FLAT SET Status = 'Hired' WHERE FLAT.FLATNO = PEOPLE.FLATNO;
    END IF;

    IF DELETING THEN
        UPDATE FLAT SET Status = 'Not Hired' WHERE FLAT.FLATNO = PEOPLE.FLATNO;
    END IF;
END;
/

Here are my tables:

CREATE TABLE FLAT
(
    FlatNo NUMBER,
    Rent NUMBER,
    FlatSize VARCHAR(20),  
    FlatType VARCHAR(20),  
    Facing VARCHAR(15),
    RoomsNo NUMBER,
    BuildingNo NUMBER,
    Status VARCHAR(20),
    PRIMARY KEY(FlatNo),
    FOREIGN KEY(BuildingNo) REFERENCES BUILDINGS(BuildingNo)
);

CREATE TABLE PEOPLE 
(
    PeopleID NUMBER,
    Members NUMBER,
    Paid NUMBER,
    Due NUMBER,
    HireDate VARCHAR(15),
    Contact NUMBER UNIQUE,
    FlatNo NUMBER,
    PRIMARY KEY(PeopleID),
    FOREIGN KEY(FlatNo) REFERENCES FLAT(FlatNo)
);

Solution

  • My other answer was incorrect. To reference the row of the table that the trigger fires on, use the :NEW bind variable for the new value (or :OLD for the old value if appropriate - in case of delete there is no NEW value...), not the table name. Check the docs.

    CREATE OR REPLACE TRIGGER TUTU
    BEFORE INSERT OR DELETE OR UPDATE ON PEOPLE
    FOR EACH ROW
    BEGIN
        IF INSERTING THEN
            UPDATE FLAT SET Status = 'Hired' WHERE FLAT.FLATNO = :NEW.FLATNO;
        END IF;
    
        IF DELETING THEN
            UPDATE FLAT SET Status = 'Not Hired' WHERE FLAT.FLATNO = :OLD.FLATNO;
        END IF;
    END;
    /