插入触发器更新问题后 [英] After insert Trigger Update Problem

问题描述

你好朋友
目前，我正在从事一个银行项目...而我的关于触发器的问题，即.

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

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;

的信息...由于此命令突变问题已解决，程序正在运行..但未解决我的实际问题..它无法仅在新插入的行中更新分支代码.读到它是SQL的缺点，即它不能读刚刚插入的行.那么如何解决这个问题...

DPMASTER(存款主数据)包含Customer.i.e
的记录

...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仅包含一个记录....即.BRANCH_CODE& BRANCH_NAME.

....

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

BRANCH_CODE    BRANCH_NAME
      0001           MUMBAI

请提供此问题的解决方案...

Please Gives the Solution of this Problem...

推荐答案

我没有得到您的SYSPARA表仅包含一条记录的含义. > 好的，我从您的代码和您的解释中得到了什么，我正在向您发送代码，请对其进行检查...

如果您的问题是，您想通过新插入的SYSPARA表的BRANCH_CODE来更新DPMASTER的每个BRANCH_CODE，然后使用以下命令更改触发器代码...

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...

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;

您无需提及太多条件，因为:NEW.AC_NO将仅返回一条新插入的记录.

如果您的问题仍未解决，请回复我，并告诉我您所面临的错误.

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.

您好，

您可以只使用BEFORE INSERT触发器并设置BRANCH_CODE的值，而无需进行自动事务处理，而不是更新新记录. IE.您的触发器可能类似于:

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;

这篇关于插入触发器更新问题后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！