1 2 Previous Next 18 Replies Latest reply: Apr 10, 2012 2:51 AM by BluShadow RSS

    Calling a function from within a trigger

    929488
      .

      Edited by: 926485 on Apr 9, 2012 10:41 PM
        • 1. Re: Calling a function from within a trigger
          rp0428
          Welcome to the forum!
          Whenever you post please provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
          >
          P_id
          >
          This is not defined in the trigger anywhere.

          You are using string literals, 'P_id','D_id' in the code. If these are column values you probably want to reference them from the table
          :new.patient_id
          
          :new.drug_id
          And then pass those values to your function and use them in the output statements.
          • 2. Re: Calling a function from within a trigger
            Frank Kulash
            Hi,

            Welcome to the forum!
            926485 wrote:
            Dear all,

            This is my first time posting to this forum. I have created a function for a medical database that compares the allergy code stored in a patient's table with the allergy code stored in a Drug table and returns a code of 0 if there is no match, or the actual allergy code (e.g., A03) if the two match). This is the function, I've tested it and it runs fine.

            CREATE OR REPLACE function Allergy_Alert(P_id in char, D_id in char)
            return CHAR
            as
            code CHAR(3);
            BEGIN
            SELECT p.Allergy_ID into code
            FROM Patient p, Drug d
            WHERE p.PHN = P_id and d.Rx = D_id and p.Allergy_ID = d.Allergy_ID;
            RETURN code;
            exception
            when no_data_found then return 0;
            END allergy_alert;
            The CHAR datatype is nothing but trouble. Use VARCHAR2 instead of CHAR.
            Whether the function returns a CHAR or a VARCHAR2, don't return a NUMBER. Change the EXCEPTION section to say
            when no_data_found then return '0';
            This isn't causing your current problem, but it will cause other problems in the future.
            I would now like to call this function from within a trigger (as required for my assignment) and I created the following trigger; however Oracle keeps giving syntax errors when I run the trigger.. Any help (possibly by tomorrow) would be much appreciated!
            Help the poeple who want to help you. Whenever you have a problem, post a complete test script that people can run to re-create the problem and test their ideas. In this case, you should include CREATE TABLE statements for all the tables involved, and INSERT statements for a little data for the tables used in the function. (Or, since you feel confident, as I do, that that the function is not causing the problem, post a simplified function that has the same arguments, but doesn't reference any tables.)
            Post a few DML statements that shoudl make the trigger fire, and the results you want from each of those statements.
            Always say which version of Oracle you're using.
            If the code you post causes an error, then post the complete error message, including line numbers.
            CREATE OR REPLACE trigger Drug_Alert
            BEFORE INSERT ON Diagnosis FOR EACH ROW
            BEGIN
            IF Allergy_Alert('P_id','D_id')='0'
            THEN
            DBMS_OUTPUT.PUT_LINE('Drug'||D_id||'was prescribed');
            ELSE
            DBMS_OUTPUT.PUT_LINE('Drug'||D_id||'may cause patient'||P_id||'side effect'||Allergy_Alert('P_id','D_id');
            END IF;
            END; /


            Thank you.
            You're referencing local variables called d_id and p_id, but these aren't defined anywhere.
            If you mean to reference columns in the row being INSERTed, then you probably want to use :NEW.d_id and :NEW.p_id instead of local variables.
            The values in single-quotes (e.g. 'D_id') are string literals, and have nothing to do with the values being INSERTed.

            Dbms_output.put_line can be handy for debugging a trigger, but, when you get the trigger into production, what will be catching and displaying that output? If you need to be capturing warnings, then they should probably be saved in some table; either a log table, or in a column in the row being modified.
            • 3. Re: Calling a function from within a trigger
              929488
              .

              Edited by: 926485 on Apr 9, 2012 10:41 PM
              • 4. Re: Calling a function from within a trigger
                929488
                .

                Edited by: 926485 on Apr 9, 2012 10:41 PM
                • 5. Re: Calling a function from within a trigger
                  rp0428
                  >
                  I have added a DECLARE section in the trigger to define P_id and D_id, and am still getting an error
                  >
                  You have many errors in your trigger
                  CREATE OR REPLACE trigger Drug_Alert
                  BEFORE INSERT ON Diagnosis FOR EACH ROW
                  DECLARE
                  P_id CHAR(5);
                  D_id CHAR(3); 
                  SET P_id := Patient.PHN;
                  SET D_id := Drug.Rx;
                  You have two 'SET' lines in the declare section. PL/SQL doesn't have 'SET' and you can't set variables in the declare section except for default values.
                  SET P_id := Patient.PHN;
                  You can't reference the patient table or columns like this.

                  The trigger on the diagnosis table only has access to the columns in that table. So you need to use different columns in the table to call your function. The table is defined
                  CREATE TABLE diagnosis(
                  Diag_id NUMBER(8) CONSTRAINT diagnosis_PK PRIMARY KEY,
                  PHN CHAR(5),
                  Diag_Date date NOT NULL,
                  SCT_Code CHAR(10),
                  GP_ID CHAR(5),
                  Rx CHAR(3),
                  The PHN column is the only way to access the patient table so you need to pass this value to your function and reference it as ':NEW.PHN'

                  The Rx column is the only way to access the drug table so you need to pass this value to your function also and reference it as ':NEW.RX'.
                  • 6. Re: Calling a function from within a trigger
                    850247
                    Hi,


                    Remove the below lines in DECLARE Section from your code.

                    SET P_id := Patient.PHN;
                    SET D_id := Drug.Rx;



                    And you are expecting the value "Patient.PHN" from the Table "Patient" and the value "Drug.Rx" from the Table "Drug". And your trigger on the Table "Diagnosis"


                    So, that you cannot directly write in DECLARE or BEGIN.

                    You should use PL/SQL SELECT Statement in BEGIN Section to retrive the values.

                    Example:


                    SELECT Patient.PHN INTO P_id FROM Patient WHERE <condition>;

                    ----------make sure that this query should return single record value only



                    Regards,
                    Sailaja
                    • 7. Re: Calling a function from within a trigger
                      rp0428
                      >
                      You should use PL/SQL SELECT Statement in BEGIN Section to retrive the values.
                      >
                      No he shouldn't - that is what the function is for.

                      The function already queries the proper tables - he just needs to pass the correct values to it.
                      • 8. Re: Calling a function from within a trigger
                        850247
                        Hi,


                        Correct, just I saw Function logic.......
                        Thank you.


                        Regards,
                        Sailaja
                        • 9. Re: Calling a function from within a trigger
                          929488
                          .

                          Edited by: 926485 on Apr 9, 2012 10:42 PM
                          • 10. Re: Calling a function from within a trigger
                            rp0428
                            >
                            IF Allergy_Alert(':NEW.PHN',':NEW.Rx'
                            >
                            Lose the single quotes.
                            • 11. Re: Calling a function from within a trigger
                              Frank Kulash
                              Hi, Julie,
                              926485 wrote:
                              Hi,

                              Thank you for the suggestions.

                              I have added the :NEW.PHN and :NEW.Rx references, and just want to know if I am on the right track with this,
                              You're getting closer.
                              as I am not completely clear on the syntax for creating a trigger with :OLD and :NEW options. My teacher only gave a very simple example using :OLD and :NEW, mainly to display a line that a trigger was fired... nothing as complex as calling a function involving different tables...

                              CREATE OR REPLACE trigger Drug_Alert
                              BEFORE INSERT ON Diagnosis FOR EACH ROW
                              DECLARE
                              P_id CHAR(5);
                              D_id CHAR(3);
                              You're not using these local variables (p_id and d_id) anywhere. Why have them at all?
                              BEGIN
                              IF Allergy_Alert(':NEW.PHN',':NEW.Rx')='0'
                              If you're expecting to find a patient whose phn startw with the character 'p', then the call above won't find it, because the first character of the first argument is ':'. Also, all phns are 5 characters long, but the string ':NEW.PHN'' is 8 characters long. Lose the single-quotes around the arguments; you only need single-quotes around '0' at the end.
                              When you use :NEW.PHN in the argument to put_line, below, you did it correctly, without single-quotes around :NEW.PHN.
                              When you use :NEW.PHN as an argument to allergy_alert, you don't need the single quotes.
                              THEN
                              DBMS_OUTPUT.PUT_LINE('Drug'||:NEW.PHN||'was prescribed');
                              You pobably want some spaces befor and after the value:
                              DBMS_OUTPUT.PUT_LINE('Drug ' ||:NEW.PHN||' was prescribed');
                              ELSE
                              DBMS_OUTPUT.PUT_LINE('Drug'||:NEW.Rx||'may cause patient'||:NEW.PHN||'side effect'||Allergy_Alert(':NEW.PHN',':NEW.Rx');
                              Remember, anything inside sigle-quotes is a literal: it liteally means what it says, without reference to any variables or keywords.
                              END IF;
                              END;/

                              Thanks
                              Julie
                              Always format your code. Use white space to make the separate components look separate. Indent to show where major sections begin and end.
                              For example:
                              CREATE OR REPLACE trigger     Drug_Alert
                              BEFORE INSERT ON            Diagnosis 
                              FOR EACH ROW
                              BEGIN
                                  IF Allergy_Alert (:NEW.PHN, :NEW.Rx) = '0'
                                  THEN
                                   DBMS_OUTPUT.PUT_LINE (  'Drug '
                                                  || :NEW.PHN
                                                  || ' was prescribed'
                                                  );
                                  ELSE
                                    DBMS_OUTPUT.PUT_LINE (  'Drug '
                                                    || :NEW.Rx
                                                   || ' may cause patient '
                              ...
                              When posting any formatted text (including, but not limited to code) on this site, type these 6 characters:

                              \
                              (all lower-case, inside curly brackets) before and after each formatted section, to preserve the sapcing.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                              • 12. Re: Calling a function from within a trigger
                                929488
                                .

                                Edited by: 926485 on Apr 9, 2012 10:42 PM
                                • 13. Re: Calling a function from within a trigger
                                  929488
                                  .

                                  Edited by: 926485 on Apr 9, 2012 10:47 PM
                                  • 14. Re: Calling a function from within a trigger
                                    sb92075
                                    opening parenthesis must match with closing parenthesis
                                    CREATE OR replace TRIGGER drug_alert 
                                      before INSERT ON diagnosis 
                                      FOR each ROW 
                                    BEGIN 
                                        IF Allergy_alert(:NEW.phn, :NEW.rx) = '0' THEN 
                                          dbms_output.Put_line ('Drug' 
                                                                ||:NEW.phn 
                                                                ||' was prescribed'); 
                                        ELSE 
                                          dbms_output.Put_line ('Drug' 
                                                                ||:NEW.rx 
                                                                ||' may cause patient' 
                                                                ||:NEW.phn 
                                                                ||' side effect' 
                                                                ||Allergy_alert(:NEW.phn, :NEW.rx)); 
                                        END IF; 
                                    END; 
                                    
                                    / 
                                    1 2 Previous Next