我把代码贴出来吧: ------------------------------------------ CREATE OR REPLACE TRIGGER ORSCMDDL.OP_BILL_PROFILE_TRIG AFTER UPDATE OF Payment_Due_Dt ON OP_BILL_PROFILE FOR EACH ROWDECLARE 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;
BEGINROW_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_DTC170013201 NuLL 14001 C170013201 45002 C170013201 14C170013202 NuLL 30003 C170013202 60004 C170013202 14.....................在这个表上创建触发器。 如果C开头的纪录(就是主记录)的due_dt字段更新了,就把从记录的将要变化的情况写到一个新表,记录变化情况. 插入新表为:COLUMN_ID old_value new_value(acct_id) 001 45 60002 14 60 ------------------------------------------------------------------------------ 问题是现在触发器中不允许再去读创建触发器的表(op_bill_profile ),业务又不能够改变,请问我该怎么做呢?
能不能给个例子
------------------------------------------
CREATE OR REPLACE TRIGGER ORSCMDDL.OP_BILL_PROFILE_TRIG
AFTER UPDATE OF Payment_Due_Dt ON OP_BILL_PROFILE
FOR EACH ROWDECLARE
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;
BEGINROW_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_DTC170013201 NuLL 14001 C170013201 45002 C170013201 14C170013202 NuLL 30003 C170013202 60004 C170013202 14.....................在这个表上创建触发器。
如果C开头的纪录(就是主记录)的due_dt字段更新了,就把从记录的将要变化的情况写到一个新表,记录变化情况.
插入新表为:COLUMN_ID old_value new_value(acct_id) 001 45 60002 14 60
------------------------------------------------------------------------------
问题是现在触发器中不允许再去读创建触发器的表(op_bill_profile ),业务又不能够改变,请问我该怎么做呢?