Forum Stats

  • 3,874,160 Users
  • 2,266,675 Discussions
  • 7,911,745 Comments

Discussions

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

856453
856453 Member Posts: 3
edited Apr 19, 2011 5:30PM in SQL & PL/SQL
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

Best Answer

Answers

  • Toon Koppelaars
    Toon Koppelaars Member Posts: 2,607
    Answer ✓
    Brian,

    I think uou'll need to put a colon (:) infront of the 'newDrugs' alias that you introduced for the regular :new alias.

    Toon
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    NEW/OLD are bind variables. In trigger body you must prefix them with colon. Change:
    SET pharmaceuticalid = newDrugs.drugID
    to
    SET pharmaceuticalid = :newDrugs.drugID
    And:
    AND newDrugs.genericname = pharmaceutical.genericname
    to
    AND :newDrugs.genericname = pharmaceutical.genericname
    SY.
    Solomon Yakobson
  • 856453
    856453 Member Posts: 3
    Thanks that was it!
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    edited Apr 19, 2011 5:20PM
    One more thing. Your trigger is selecting from triggering table. So single row inserts will work, but multiple row inserts into PHARMACEUTICALS will fail with famous "table is mutating" error. There is no need to select from PHARMACEUTICALS. Change:
    UPDATE ************
    SET pharmaceuticalid = newDrugs.drugID
    WHERE pharmaceuticalid IN(SELECT pharmaceuticalid FROM ************, pharmaceuticals WHERE drugid = pharmaceuticalid AND newDrugs.genericname = pharmaceutical.genericname);
    to
    UPDATE ************
      SET pharmaceuticalid = newDrugs.drugID
      WHERE pharmaceuticalid IN (
                                 SELECT pharmaceuticalid
                                   FROM ************
                                   WHERE drugid = :newDrugs.pharmaceuticalid
                                     AND :newDrugs.genericname = pharmaceutical.genericname
                                );
    SY.
    Solomon Yakobson
  • 856453
    856453 Member Posts: 3
    You were right I did get that error. I fixed it by just doing a BEFORE instead of AFTER although I do know this runs into concurrency issues if the database crashes after finishing the updates to prescription but before the original insert finishes. I would do what you suggested but for this instance it does matter in the WHERE drugid = :newDrugs.pharmaceuticalid that I compare it to the original pharmaceuticalid and not the new one that is being inserted. The database is setup so that each drug has it's own ID, and prescriptions use a drugid from the pharmaceutical table. The trigger is so that if someone inserts a drug into the table where the name of the drug is the same as its generic name, then everyone who is on a drug where the generic version of it (so like drug name: Motrin, generic name: ibuprofen if I inserted drug name: ibuprofen, generic name: ibuprofen) has their prescriptions switched to ibuprofen. I know this is not realistic but it simply has to demonstrate I have a decent understanding of what a trigger does.
    -Brian
This discussion has been closed.