Home » Developer & Programmer » Forms » Forms 6i (if record exists then update) post insert trigger (oralce forms6i, 10g, 11g)
Forms 6i (if record exists then update) post insert trigger [message #666101] Sat, 14 October 2017 21:27 Go to next message
zaibi
Messages: 17
Registered: January 2017
Junior Member
hi everyone i need to create select statement in post_insert trigger. is it possible if yes then how???
i want to check another table records if it exists then it will update it otherwise insert as new record. Please help.
Thanks.
My block code is that i want to run

 DECLARE
  exsist_type    VARCHAR2(50);
  exsist_name    VARCHAR2(50);
  exsist_company VARCHAR2(100);
BEGIN
  SELECT pro_type,
         pro_name,
         company_name
  INTO   exsist_type,
         exsist_name ,
         exsist_company
  FROM   stock;
  
  IF :PURCHASE_DETAIL.pro_type <> exsist_type
    AND
    :PURCHASE_DETAIL.pro_name <> exsist_name
    AND
    :PURCHASE_DETAIL.company_name <> exsist_company THEN
    */
    IF :PURCHASE.radio_type = 'PURCHASE' THEN
      INSERT INTO stock
                  (
                              pro_type ,
                              pro_name ,
                              company_name ,
                              quantity ,
                              purchase_rate,
                              sale_rate ,
                              rack_num
                  )
                  VALUES
                  (
                              :PURCHASE_DETAIL.pro_type,
                              :PURCHASE_DETAIL.pro_name,
                              :PURCHASE_DETAIL.company_name,
                              :PURCHASE_DETAIL.quantity,
                              :PURCHASE_DETAIL.price,
                              :PURCHASE_DETAIL.sale_price,
                              :PURCHASE_DETAIL.rack_num
                  );
    
    END IF;
  ELSIF :PURCHASE_DETAIL.pro_type = exsist_type
    AND
    :PURCHASE_DETAIL.pro_name = exsist_name
    AND
    :PURCHASE_DETAIL.company_name = exsist_name THEN
    IF :PURCHASE.radio_type = 'PURCHASE' THEN
      UPDATE stock
      SET    stock.quantity = stock.quantity+:PURCHASE_DETAIL.quantity
      WHERE  stock.pro_type = :PURCHASE_DETAIL.pro_type
      AND    stock.pro_name = :PURCHASE_DETAIL.pro_name
      AND    stock.company_name= :PURCHASE_DETAIL.company_name;
    
    ELSIF :PURCHASE.radio_type = 'PRCH_RETURN' THEN
      UPDATE stock
      SET    stock.quantity = stock.quantity-:PURCHASE_DETAIL.quantity
      WHERE  stock.pro_type = :PURCHASE_DETAIL.pro_type
      AND    stock.pro_name = :PURCHASE_DETAIL.pro_name
      AND    stock.company_name = :PURCHASE_DETAIL.company_name;
    
    END IF;
  END IF;
END; 

[EDITED by LF: formatted code]

[Updated on: Mon, 16 October 2017 00:13] by Moderator

Report message to a moderator

Re: Forms 6i (if record exists then update) post insert trigger [message #666105 is a reply to message #666101] Mon, 16 October 2017 00:20 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Code you posted is invalid; it contains the end of comment block (*/) so it wouldn't even compile.

Furthermore, the first SELECT statement is likely to raise the TOO-MANY-ROWS error, unless your STOCK table contains just one row.

Apart from that, the requirement ("if it exists then it will update it otherwise insert as new record") sounds like an upsert, i.e. MERGE. As you specified different versions (from 6i to 11g), I'm pretty much sure that MERGE won't work in Forms 6i but might/should in later versions.

Finally, what happened when you ran that code? Did it work? Or not? If not, what happened? Did you get any error? If so, which one(s)?
Re: Forms 6i (if record exists then update) post insert trigger [message #666112 is a reply to message #666105] Mon, 16 October 2017 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can of course put the merge in a procedure in the DB and call that from post-insert.
That'll work regardless of forms version.
Re: Forms 6i (if record exists then update) post insert trigger [message #666123 is a reply to message #666112] Mon, 16 October 2017 12:12 Go to previous message
zaibi
Messages: 17
Registered: January 2017
Junior Member
Thank you Sir i Solve this issue by using select statemnet which count is used. thanks for reply.
Previous Topic: When open the form before error are display
Next Topic: Oracle Forms System Tray Icon
Goto Forum:
  


Current Time: Fri Mar 29 08:53:50 CDT 2024