Solved

TRIGGER - Not updating past first statement

Posted on 2011-03-18
2
284 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
2 Comments
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 50 total points
Comment Utility
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
Comment Utility
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

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

10 Experts available now in Live!

Get 1:1 Help Now