Solved

TRIGGER - Not updating past first statement

Posted on 2011-03-18
2
289 Views
Last Modified: 2012-05-11
Hi, I have a trigger below that has 4 other table update statements in it.

When executing the trigger only the first shows that it executed.

Are you not allowed more than one update in a trigger. What can be done to have all 4 fire successfully or if any one fails to rollback all? (thought that was normal trigger execution)

Thanks..
create or replace TRIGGER "MY_BU"
    BEFORE UPDATE
    ON MY
    FOR EACH ROW
 declare   
    ln_POP_MY_ID number;
BEGIN   
    if(:new.FRR_ID =2)then    
       
        --MY_GEN_XREF_V (BD_SRC_SEC)
            UPDATE BD_SRC_SEC
               SET FRR_ID=2
             WHERE (SRC_ID = :new.MY_ID   -- MY_ID (SRC_ID)
                       OR VALUE_ID = :new.MY_ID ) -- GEN_ID (VALUE_ID) assigned to MY_ID (SRC_ID)
               AND SRC_TYPE_ID = 7
               AND SEC_ATTR_ID = 2
               AND FRR_ID =1;        
    
        --BD_POP_FRR_XREF
           UPDATE BD_POP_FRR_XREF
              SET FRR_ID =2
            WHERE MY_ID = :new.MY_ID
              AND FRR_ID =1;

        --MY_PROGRAM_XREF_V (BD_SRC_SEC)
            UPDATE BD_SRC_SEC
               SET FRR_ID=2
             WHERE SRC_ID = :new.MY_ID   -- MY_ID (SRC_ID)
               AND SRC_TYPE_ID = 7
               AND SEC_ATTR_ID = 5
               AND FRR_ID =1;

        --BD_PROGR_SUPPL_XREF
            UPDATE BD_TRACE_XREF
               SET FRR_ID=2
             WHERE PUBL_ID =:new.MY_ID
               AND FRR_ID =1;
   end if;
  :new.last_updated_by := NVL(v('APP_USER'), USER);
  :new.last_updated_on := SYSDATE;
END;

Open in new window

0
Comment
Question by:bcarlis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 50 total points
ID: 35169763
You are allowed as much SQL and PL/SQL as you want in a trigger. What's your evidence to claim that only the first update is executed? Maybe your WHERE predicates don't match anything, so no rows are updated in the other ones.

Check SQL%ROWCOUNT after each one and/or enclose them into blocks with EXCEPTION handlers. However, if any of them raises an error it will be propagated to the SQL that fired the trigger in the first place -you don't have to do anything if you want just that.
0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 75 total points
ID: 35171772
hi

move al your logic into a procedure and use the trigger to cal the procedure
that will make thinks manageneble and easy and Working :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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