目的就是想删除inp_bill_detail表中的记录时,自动向inp_bill_del
表中写入删除的记录。
代码如下:
create or replace trigger inp_bill_del_insert
before delete on inp_bill_detail
for each rowBEGIN
INSERT INTO inp_bill_del
(patient_id,visit_id,item_no,item_class,item_name,item_code,item_spec,amount,unit,ordered_by,performed_by,costs,charges,billing_date_time,operator_no,rcpt_no,special_charges,free_limit,special_fee,insur_up_sign)
VALUES
(:old.patient_id,:old.visit_id,:old.item_no,:old.item_class,:old.item_name,:old.item_code,:old.item_spec,:old.amount,:old.unit,:old.ordered_by,:old.performed_by,:old.costs,:old.charges,:old.billing_date_time,:old.operator_no,:old.rcpt_no,:old.special_charges,:old.free_limit,:old.special_fee,insur_up_sign);
END ;
编译不通过,提示:
Warning: Trigger created with compilation errors.
请帮忙看看。
表中写入删除的记录。
代码如下:
create or replace trigger inp_bill_del_insert
before delete on inp_bill_detail
for each rowBEGIN
INSERT INTO inp_bill_del
(patient_id,visit_id,item_no,item_class,item_name,item_code,item_spec,amount,unit,ordered_by,performed_by,costs,charges,billing_date_time,operator_no,rcpt_no,special_charges,free_limit,special_fee,insur_up_sign)
VALUES
(:old.patient_id,:old.visit_id,:old.item_no,:old.item_class,:old.item_name,:old.item_code,:old.item_spec,:old.amount,:old.unit,:old.ordered_by,:old.performed_by,:old.costs,:old.charges,:old.billing_date_time,:old.operator_no,:old.rcpt_no,:old.special_charges,:old.free_limit,:old.special_fee,insur_up_sign);
END ;
编译不通过,提示:
Warning: Trigger created with compilation errors.
请帮忙看看。
AFTER DELETE ON INP_BILL_DETAIL
FOR EACH ROW
BEGIN
INSERT INTO INP_BILL_DEL
(PATIENT_ID,
VISIT_ID,
ITEM_NO,
ITEM_CLASS,
ITEM_NAME,
ITEM_CODE,
ITEM_SPEC,
AMOUNT,
UNIT,
ORDERED_BY,
PERFORMED_BY,
COSTS,
CHARGES,
BILLING_DATE_TIME,
OPERATOR_NO,
RCPT_NO,
SPECIAL_CHARGES,
FREE_LIMIT,
SPECIAL_FEE,
INSUR_UP_SIGN)
VALUES
(:OLD.PATIENT_ID,
:OLD.VISIT_ID,
:OLD.ITEM_NO,
:OLD.ITEM_CLASS,
:OLD.ITEM_NAME,
:OLD.ITEM_CODE,
:OLD.ITEM_SPEC,
:OLD.AMOUNT,
:OLD.UNIT,
:OLD.ORDERED_BY,
:OLD.PERFORMED_BY,
:OLD.COSTS,
:OLD.CHARGES,
:OLD.BILLING_DATE_TIME,
:OLD.OPERATOR_NO,
:OLD.RCPT_NO,
:OLD.SPECIAL_CHARGES,
:OLD.FREE_LIMIT,
:OLD.SPECIAL_FEE,
:OLD.INSUR_UP_SIGN);
END;
2.你可以试下下面的create or replace trigger inp_bill_del_insert
before delete on inp_bill_detail
for each rowBEGIN INSERT INTO inp_bill_del
VALUES
(:old.patient_id,
:old.visit_id,
:old.item_no,
:old.item_class,
:old.item_name,
:old.item_code,
:old.item_spec,
:old.amount,
:old.unit,
:old.ordered_by,
:old.performed_by,
:old.costs,
:old.charges,
:old.billing_date_time,
:old.operator_no,
:old.rcpt_no,
:old.special_charges,
:old.free_limit,
:old.special_fee,
:old.insur_up_sign);END;
3还不可以的话你查下有没有使用序列
我试了,还是不行。最后那个字段我加上了,还是不行。
我查一下,是有序列,item_no字段用到了序列,那会有什么影响吗?
Warning: Trigger created with compilation errors.
Connected.
Input truncated to 1 charactersWarning: Trigger created with compilation errors.SQL> /Warning: Trigger created with compilation errors.SQL> show error;
No errors.
SQL>
语法没问题的。
ORA-04098: trigger 'SYSTEM.INP_BILL_DEL_INSERT' is invalid and failed re-validation
No changes made to database.DELETE FROM "INP_BILL_DETAIL" WHERE "PATIENT_ID" = :1 AND "VISIT_ID" = :2 AND "ITEM_NO" = :3 AND "ITEM_CLASS" = :4 AND "ITEM_NAME" = :5 AND "ITEM_CODE" = :6 AND "ITEM_SPEC" = :7 AND "AMOUNT" = :8 AND "UNITS" = :9 AND "ORDERED_BY" = :10 AND "PERFORMED_BY" = :11 AND "COSTS" = :12 AND "CHARGES" = :13 AND "BILLING_DATE_TIME" = :14 AND "OPERATOR_NO" = :15 AND "RCPT_NO" = :16 AND "SPECIAL_CHARGES" = :17 AND "FREE_LIMIT" IS NULL AND "SPECIAL_FEE" IS NULL AND "INSUR_UP_SIGN" IS NULL
触发器是在system下的,表是在inpbill用户下的
SQL> @ C:\inpbilltrigger.txt;
Connected.(这里用的是inpbill用户登录)Warning: Trigger created with compilation errors.