Solved

TRIGGER - Not updating past first statement

Posted on 2011-03-18
2
285 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
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

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

932 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