After insert Trigger Update Problem

Question

5.00/5 (1 vote)

See more:

Hello Friends
currently i am Working on one Banking project...And my problem Regarding Trigger i.e..

SQL

CREATE OR REPLACE TRIGGER INSERT_BRANCH_CODE
AFTER INSERT on DPMASTER
FOR EACH ROW
DECLARE
     PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE DPMASTER SET BRANCH_CODE = (SELECT BRANCH_CODE FROM SYSPARA) WHERE AC_NO IN (SELECT :NEW.AC_NO FROM DPMASTER) AND AC_TYPE IN (SELECT :NEW.AC_TYPE FROM DPMASTER ) AND AC_ACNOTYPE IN (SELECT :NEW.AC_ACNOTYPE FROM DPMASTER);
    COMMIT;
    END

I want to update branch Code in only New inserted Record after Customer record inserted into Database.....previously i am facing "after instert trigger mutation problem" but i got information about

PRAGMA AUTONOMOUS_TRANSACTION;

...due to this Command mutation problem is solve, program is Run..but it not Solve My Actual problem..it cant update Branch Code in just Newly Inserted Row..i have Read That it is a Drawback Of SQL i.e. it Cant Read Just Inserted Row..So How to Solve This Prob...

DPMASTER (Deposite Master) Contains record of Customer.i.e

AC_ACNOTYPE,  AC_TYPE, AC_NO,  NAME,  AMOUNT,  INT.RATE, MONTHS, BRANCH_CODE, etc

....

SYSPARA Contains only one record ....i.e..BRANCH_CODE & BRANCH_NAME.

BRANCH_CODE    BRANCH_NAME
      0001           MUMBAI

Please Gives the Solution of this Problem...

Posted 10-Feb-11 18:35pm

kumar_jadhav

Updated 12-Feb-11 6:07am

Wendelius

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen Kumar Upadhyay · Answer 1 · 2011-02-10T19:06:00

I didn't get the meaning that your SYSPARA table contain only one record.
ok what i got from your code and from your explanation I am sending you the code, please check it...

If your problem is, you want to update each BRANCH_CODE of DPMASTER by newly inserted BRANCH_CODE of SYSPARA table then chnage your Trigger code with the following...

SQL

CREATE OR REPLACE TRIGGER INSERT_BRANCH_CODE
AFTER INSERT on DPMASTER
FOR EACH ROW
DECLARE
     PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE DPMASTER SET BRANCH_CODE = (SELECT BRANCH_CODE FROM SYSPARA) WHERE AC_NO = (SELECT :NEW.AC_NO FROM DPMASTER);
    COMMIT;
    END;

you don't need to mention so much condition because :NEW.AC_NO will return only one record which is newly inserted.

Revert me back if your problem is not solved and tell me the excat error that you are facing.

Wendelius · Answer 2 · 2011-02-10T21:06:00

Hi,

Instead of updating the new record, could you simply use BEFORE INSERT trigger and set the value of the BRANCH_CODE without autonomous transactions. I.e. your trigger could be something like:

CREATE OR REPLACE TRIGGER INSERT_BRANCH_CODE
BEFORE INSERT on DPMASTER
FOR EACH ROW
BEGIN
   SELECT BRANCH_CODE 
   INTO :NEW.BRANCH_CODE
   FROM SYSPARA
   WHERE AC_NO = :NEW.AC_NO...;
END;

After insert Trigger Update Problem

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0