【Oracle高手请进】在Oracle的一个表上创建一个触 找服装进销存管理
仓库管理软件版1楼: CREATE OR REPLACE TRIGGER ORSCMDDL.OP_BILL_PROFILE_TRIG
AFTER UPDATE OF Payment_Due_Dt ON OP_BILL_PROFILE
FOR EACH ROW
DECLARE
ROW_A NUMBER(10);
V_acct_id op_acct.acct_id%TYPE;
V_payment_due_dt op_bill_profile.payment_due_dt%TYPE;
CURSOR cur_op_bill_profile(v_acctid in varchar2) is select acct.acct_id, bill.payment_due_dt from op_acct acct, op_bill_profile bill where acct.acct_id = bill.acct_id
and acct.master_acct_id = v_acctid;
BEGIN
ROW_A := 0;
BEGIN
Select count(0) into ROW_A from OP_ACCT where acct_id = :NEW.ACCT_ID and acct_class_ind = ＇＇1＇＇ and acct_ty = ＇＇2＇＇;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20101, ＇＇UPDATE Failed: Customer Number entered not found？？？＇＇);
END;
If ROW_A > 0 then
-- Insert all the accounts under the customer into OP_FIELD_AMEND table
OPEN cur_op_bill_profile(:NEW.ACCT_ID);
LOOP
FETCH cur_op_bill_profile INTO V_acct_id, V_payment_due_dt;
EXIT WHEN cur_op_bill_profile%NOTFOUND;
insert into OP_FIELD_AMEND(INT_SEQ, TABLE_NM,COLUMN_NM, COLUMN_ID, OLD_VALUE, NEW_VALUE, MOD_DT, MOD_STATUS, MOD_REMARKS, HOUSEKEEP_IND, LAST_UPDATE_ID, LAST_UPDATE_DT)
values(ORSCMDDL.op_field_amend_seq.NEXTVAL, ＇＇OP_BILL_PROFILE＇＇, ＇＇PAYMENT_DUE_DT＇＇, V_acct_id, V_payment_due_dt, :NEW.PAYMENT_DUE_DT, SYSDATE, ＇＇1＇＇, NULL, NULL, ＇＇user＇＇, TA_PEGASUS_SYSDATE_FUNC);
END LOOP;
CLOSE cur_op_bill_profile;
END if;
END;
---------------------------------------------------------------------
就是在一个表中实现了主从关系
比如：
acct_id master_id due_DT
C170013201 NuLL 14
001 C170013201 45
002 C170013201 14
C170013202 NuLL 30
003 C170013202 60
004 C170013202 14
.....................
在这个表上创建触发器。
如果C开头的纪录(就是主记录)的due_dt字段更新了，就把从记录的将要变化的情况写到一个新表，记录变化情况．
插入新表为：
COLUMN_ID old_value new_value
(acct_id)
001 45 60
002 14 60
------------------------------------------------------------------------------
问题是现在触发器中不允许再去读创建触发器的表（op_bill_profile ），业务又不能够改变，请问我该怎么做呢？
AFTER UPDATE OF Payment_Due_Dt ON OP_BILL_PROFILE
FOR EACH ROW
DECLARE
ROW_A NUMBER(10);
V_acct_id op_acct.acct_id%TYPE;
V_payment_due_dt op_bill_profile.payment_due_dt%TYPE;
CURSOR cur_op_bill_profile(v_acctid in varchar2) is select acct.acct_id, bill.payment_due_dt from op_acct acct, op_bill_profile bill where acct.acct_id = bill.acct_id
and acct.master_acct_id = v_acctid;
BEGIN
ROW_A := 0;
BEGIN
Select count(0) into ROW_A from OP_ACCT where acct_id = :NEW.ACCT_ID and acct_class_ind = ＇＇1＇＇ and acct_ty = ＇＇2＇＇;
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20101, ＇＇UPDATE Failed: Customer Number entered not found？？？＇＇);
END;
If ROW_A > 0 then
-- Insert all the accounts under the customer into OP_FIELD_AMEND table
OPEN cur_op_bill_profile(:NEW.ACCT_ID);
LOOP
FETCH cur_op_bill_profile INTO V_acct_id, V_payment_due_dt;
EXIT WHEN cur_op_bill_profile%NOTFOUND;
insert into OP_FIELD_AMEND(INT_SEQ, TABLE_NM,COLUMN_NM, COLUMN_ID, OLD_VALUE, NEW_VALUE, MOD_DT, MOD_STATUS, MOD_REMARKS, HOUSEKEEP_IND, LAST_UPDATE_ID, LAST_UPDATE_DT)
values(ORSCMDDL.op_field_amend_seq.NEXTVAL, ＇＇OP_BILL_PROFILE＇＇, ＇＇PAYMENT_DUE_DT＇＇, V_acct_id, V_payment_due_dt, :NEW.PAYMENT_DUE_DT, SYSDATE, ＇＇1＇＇, NULL, NULL, ＇＇user＇＇, TA_PEGASUS_SYSDATE_FUNC);
END LOOP;
CLOSE cur_op_bill_profile;
END if;
END;
---------------------------------------------------------------------
就是在一个表中实现了主从关系
比如：
acct_id master_id due_DT
C170013201 NuLL 14
001 C170013201 45
002 C170013201 14
C170013202 NuLL 30
003 C170013202 60
004 C170013202 14
.....................
在这个表上创建触发器。
如果C开头的纪录(就是主记录)的due_dt字段更新了，就把从记录的将要变化的情况写到一个新表，记录变化情况．
插入新表为：
COLUMN_ID old_value new_value
(acct_id)
001 45 60
002 14 60
------------------------------------------------------------------------------
问题是现在触发器中不允许再去读创建触发器的表（op_bill_profile ），业务又不能够改变，请问我该怎么做呢？
2楼: http://oracle.chinaitlab.com/optimize/26427.html
关于触发器修改自身数据表的案例，应该可以满足你 如服装进销存管理
3楼: to Beyondbill:
首先感谢你的热心帮助，不过你给的那个例子和我的问题是不一样的．
我是要读取创建触发器的那张表．．．用你给的那个方法我还是不能实现！
请你再认真看看我的问题．
4楼: 同时感谢Beyondbill！
5楼: [:D][:D]
6楼: 接受答案了.
联系我们
