• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

if-then in trigger is not wroking

My trigger is added more conditoin but this condition is not okie , it is still run , In fact it must stop.
help me  
CREATE OR REPLACE TRIGGER sms_t24
    AFTER INSERT
        ON doc
        FOR EACH ROW
 
DECLARE
        v_amount_lcy varchar2(50);
        v_currency varchar2(50);
  
 
BEGIN
 
select distinct currency.full_name  into v_currency from currency where   currency.code= :new.trans_curr ;
 
if :new.return_code = '0' then 
insert into vpb_stmt_entry@t24
(
  recid,
  account_number,
  update_time,
  amount_lcy,
  currency,
  category,
  trans_reference
)
 
 values
(
 :new.auth_code,
 :new.target_number,
 :new.AMND_DATE,
 :new.trans_amount,
 rpad(v_currency,3),
 :new.REQUEST_CATEGORY,
 :new.source_code
);
 
end if ;
 END;

Open in new window

0
dinhchung82
Asked:
dinhchung82
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>My trigger is added more conditoin but this condition is not okie , it is still run , In fact it must stop.
help me  

sorry, but I am not 100% sure what exactly is the problem?...
0
 
ram_0218Commented:
check the table doc whether it has a default value for return code? if yes, can you remove it or modify your IF statement appropriately?
0
 
sdstuberCommented:
I'm not positive of what the question is asking either;
but I "think" the asker is saying that if the "IF" condition in the trigger is TRUE
then it should do both inserts (the main one that caused the trigger to fire, and the sub-insert
we see inside the trigger.

If the IF condition is false then the trigger should prevent
the main insert from continuing.  The only way I know to do that is to raise an error
as in my example below.


CREATE OR REPLACE TRIGGER sms_t24
    AFTER
    INSERT
    ON doc
    FOR EACH ROW
DECLARE
 
    v_amount_lcy varchar2(50);
    v_currency varchar2(50);
BEGIN
    SELECT DISTINCT currency.full_name
    INTO v_currency
    FROM currency
   WHERE currency.code = :new.trans_curr;
    IF :new.return_code = '0'
    THEN
        INSERT INTO vpb_stmt_entry @ t24(recid,
                                         account_number,
                                         update_time,
                                         amount_lcy,
                                         currency,
                                         category,
                                         trans_reference)
      VALUES (:new.auth_code,
              :new.target_number,
              :new.amnd_date,
              :new.trans_amount,
              rpad(v_currency, 3),
              :new.request_category,
              :new.source_code);
    ELSE
        raise_application_error
            (-20001, 'Invalid return code: ' || :new.return_code);
    END IF;
END;

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dinhchung82Author Commented:
Hi Ram_0218 ,
You are correct , doc.return_code have defaul value = '0' .
How can i modify my if statement ? In fact, default value = '0' , but real value is not = '0' ( maybe = 61..).
Why does it compare with defalt value ?

Hi  Stsduber,
Yes , it is my question .
your raise_application_error is not solved my problem . My question is above .
Would you help me ?

0
 
sdstuberCommented:
what do you mean "default value"?  and what compare are you talking about?

Nothing in your trigger let alone the "IF" statements mentions a "default" column.

What is it you want this trigger to do?
When you say "yes, it is my question"  what are you saying "yes" to?
you're agreeing with something but I don't know what.
what is the "it" that is your question?

I'm sorry, I have no idea what you're asking.  Please clarify.
0
 
dinhchung82Author Commented:
Firstly , sorry for all because my question is not clear.

My scenerio :

I have Doc table in my database , I create database link  named t24 .
Whenever Doc table is inserted , i also insterted into VPB_STMT_ENTRY table in database link.

I use  IF condition and if its value  is true ,  VPB_STMT_ENTRY table shall be inserted .
But when If condition is false , VPB_STMT_ENTRY table still be inserted.
--> That is my quesiton .

After reading RAM_0218 comment , i see check that   Doc table with return_code field  have default value is "0".
desc DOC :

RETURN_CODE        NUMBER(9)               0

When Doc table is instered with return_code <> 0 , my trigger is still run and update to VPB_STMT_ENTRY  --> for my code , it is not allowed to insert into VPB_STMT_ENTRY --> that's the reason , i said that IF statement  compared with
default value , not new value of Return code we have just inserted for DOC Table .

I try to clarify , is it okie for you ?



0
 
ram_0218Commented:
**you may not want to throw raise_application_error because when you do so, your current transaction will be rollbacked which is not what you want i guess**

** if you'r so confident that for return_code you'r supplying value 61, then i would say i'm confident that your insert statement in the trigger is not FIRING!!! why would you think INSERT is happening? How sure are you?

** my initial post was to indicate you that had you skipped the column while doing INSERT your trigger fires (without you knowing) thats why i told you to check default value. but when you say you pass value 61, i dont think the insert gets fired may be what you see there is old data ;-)
0
 
dinhchung82Author Commented:
Thanks so much !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now