用触发器在删除某一条记录前把该记录insert到备份表

[pipiright]

我的表叫contract,备份表叫contract_deleted.
我想实现在删除contract里的记录时先把此纪录insert到备份表contract_deleted和contract_billing_deleted,下面是我写的触发器,可是有问题,如果把insert into contract_deleted 去掉的话是没问题的,请问是怎么回事?

CREATE OR REPLACE TRIGGER CONTRACT_BEFORE_DELETE before delete
on CONTRACT for each row
begin
insert into contract_deleted
(select CONTRACT_NUM, CUSTNAME, COMPANY_NAME, LOCATION, PROJECT_NAME, START_DATE, END_DATE, TERMINATION_DATE, BILLING_METHOD, BILLING_SCHEDULE, AUTO_RENEW, TCV, ONE_TIME_CHARGE, MONTHLY_CHARGE, CONTRACT_RECEIVED_DATE, REMARKS, CONTRACT_STATUS from contract where contract_num = :old.contract_num);

insert into contract_billing_deleted
(select contract_num, billing_date, bill_ref_num, amount, remarks from contract_billing where contract_num = :old.contract_num);

end;
/

[pipiright]

表contract_deleted和contract_billing_deleted是用表contract和contract_billing的同一个sql生成的,除了把主键去掉了,其他都一样
下面是出错提示.

ORA-04091: table TS.CONTRACT is mutating, trigger/function may not see it
ORA-06512: at "TS.CONTRACT_BEFORE_DELETE", line 2
ORA-04088: error during execution of trigger 'TS.CONTRACT_BEFORE_DELETE'

[pipiright]

没人看到吗?

[难得糊涂糊涂]

before delete
修改为after delete 试试？

[CathySun118]

很可能是表结构不相同

[hongtao945]

加commit;试试

[mrdxhh]

语法结构好象有问题啊

[zhangxf1981]

不能这样写的 在触发器里面select 本表有问题的 你试试这个
CREATE OR REPLACE TRIGGER CONTRACT_BEFORE_DELETE before delete
on CONTRACT for each row
begin
insert into contract_deleted
(select CONTRACT_NUM, CUSTNAME, COMPANY_NAME, LOCATION, PROJECT_NAME, START_DATE, END_DATE, TERMINATION_DATE, BILLING_METHOD, BILLING_SCHEDULE, AUTO_RENEW, TCV, ONE_TIME_CHARGE, MONTHLY_CHARGE, CONTRACT_RECEIVED_DATE, REMARKS, CONTRACT_STATUS from contract where contract_num = :old.contract_num);

insert into contract_billing_deleted values
(:old.contract_num, :old.billing_date, :old.bill_ref_num, :old.amount, :old.remarks );

end;

[ys2004888]

这是行触发器，这样不行的

[EdisonZhao]

触发器不能使用本表，所以你的触发器不能通过编译。

[zxl_2609]

insert into contract_deleted (:old.对应的字段);
insert into contract_billing_deleted values
(:old.contract_num, :old.billing_date, :old.bill_ref_num, :old.amount, :old.remarks );

[oracle_dba_11]

[Quote=引用 9 楼 EdisonZhao 的回复:]
触发器不能使用本表，所以你的触发器不能通过编译。
[/Quote]