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)
);
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;
/