This discussion is archived
2 Replies Latest reply: Mar 25, 2013 9:42 PM by MTajuddin RSS

Trigger help

999069 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    MTajuddin Journeyer
    Currently Being Moderated
    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