Error in Trigger (PL/SQL: SQL Statement Ignored)
856453Apr 19 2011 — edited Apr 19 2011Here is the trigger:
CREATE OR REPLACE TRIGGER DRUGREPLACEMENT
AFTER INSERT ON PHARMACEUTICALS
REFERENCING NEW AS newDrugs
FOR EACH ROW
WHEN (newDrugs.drugname = newDrugs.genericname)
BEGIN
UPDATE prescription
SET pharmaceuticalid = newDrugs.drugID
WHERE pharmaceuticalid IN(SELECT pharmaceuticalid FROM prescription, pharmaceuticals WHERE drugid = pharmaceuticalid AND newDrugs.genericname = pharmaceutical.genericname);
END;
Error is:
Error(2,1): PL/SQL: SQL Statement ignored
Error(3,24): PL/SQL: ORA-00904: "NEWDRUGS"."DRUGID": invalid identifier
pharmaceuticalid is a number in my prescription table.
drugid is a number; genericname and drugname are both varchars in my pharmaceuticals table. Any idea why I am getting these errors? (I tried putting quotes around newdrugs.drugid but then it just said "newdrugs.drugid" is invalid. Thanks for your help
-Brian