Solved

if-then in trigger is not wroking

Posted on 2008-06-23
8
460 Views
Last Modified: 2013-12-07
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
Comment
Question by:dinhchung82
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848853
>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
 
LVL 17

Expert Comment

by:ram_0218
ID: 21848861
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 21849805
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 21852054
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 21852677
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
 
LVL 5

Author Comment

by:dinhchung82
ID: 21853043
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
 
LVL 17

Accepted Solution

by:
ram_0218 earned 50 total points
ID: 21855975
**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
 
LVL 5

Author Comment

by:dinhchung82
ID: 21872215
Thanks so much !
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now