2 Replies Latest reply: Mar 25, 2013 11:42 PM by M Tajuddin RSS

    Trigger help

    999069
      Hi Guys, Im just getting started with Oracle Application Express and need help creating a trigger.

      I have 2 tables one called pf_memberbanks which has two fields BANK_ID and TEAM_INFO and one called pf_po_inbox which has the fields DB_BANK_ID and PO_SB_MESSAGE.

      Basically what im trying to create is a trigger that runs when data is entered into the pf_po_inbox table. It needs to check if the DB_BANK_ID exists in any of BANK_ID fields in the pf_memberbanks table. If it does set PO_SB_MESSAGE to ok or if it doesnt exist set PO_SB_MESSAGE to Error
        • 1. Re: Trigger help
          sybrand_b
          Your question seems to be unrelated to APEX, as there are no triggers in APEX. There are triggers in the database.
          Also tables in a relational database don't have 'fields', they have columns.
          Finally, from your description it appears you try to implement programmatically what every RDBMS has natively: a Foreign key.
          If that is what you want to do you should create a FOREIGN KEY through SQL, on pf_po_infobox to pf_memberbanks.
          Enforcing this programmatically is reallly a very very bad idea (tm) as your 'solution' won't scale.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Trigger help
            M Tajuddin
            Hello
            You can create a simple trigger to set the column values depending on other table column values, here is an example however I have not tested it -
            CREATE OR REPLACE TRIGGER RTG_NAME
               BEFORE INSERT
               ON pf_po_inbox
               REFERENCING OLD AS OLD NEW AS NEW
               FOR EACH ROW
            
            DECLARE
               BANK_ID   NUMBER;
            BEGIN
               SELECT BANK_ID
                 INTO BANK_ID
                 FROM pf_memberbanks
                WHERE BANK_ID = :NEW.DB_BANK_ID;
            
               IF BANK_ID IS NOT NULL
               THEN
                  -- set the column value if the BANK_ID exists
                 :NEW.PO_SB_MESSAGE := 'OK';
               ELSE
                  :NEW.PO_SB_MESSAGE := 'ERROR';
               END IF;
            END;
            Hope this helps.

            Regards,
            Tajuddin
            Blog: http://tajuddin.saradesh.com

            Edited by: M Tajuddin on Mar 26, 2013 12:42 PM