用触发器在删除某一条记录前把该记录insert到备份表
我的表叫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;
/

------解决方案--------------------
很可能是表结构不相同
------解决方案--------------------
before delete
修改为after delete 试试？
------解决方案--------------------
加commit;试试
------解决方案--------------------
语法结构好象有问题啊
------解决方案--------------------
不能这样写的 在触发器里面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;
------解决方案--------------------
这是行触发器，这样不行的
------解决方案--------------------
触发器不能使用本表，所以你的触发器不能通过编译。
------解决方案--------------------
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 ); 

------解决方案--------------------

探讨

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