Skip to Main Content

SQL & PL/SQL

Error in Trigger (PL/SQL: SQL Statement Ignored)

856453Apr 19 2011 — edited Apr 19 2011
Here 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
This post has been answered by Toon Koppelaars on Apr 19 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 17 2011
Added on Apr 19 2011
5 comments
2,829 views