Solved

if-then in trigger is not wroking

Posted on 2008-06-23
8
462 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
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 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
 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

810 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