创建触发器以插入到另一个表中 [英] Create trigger to insert into another table

问题描述

执行下面的触发器时遇到一些问题:

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

varReadNo   Int;
varMeterID  Int;
varCustID   Varchar(10);

BEGIN 

SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL;

Select MeterID INTO varMeterID
From Reading 
Where ReadNo = varReadNo;

Select CustID INTO varCustID
From Address A
Join Meter M 
on A.postCode = M.postCode
Where M.MeterID = varMeterID;

INSERT INTO BILL VALUES 
(SEQBILLNO.NEXTVAL, SYSDATE, 'UNPAID' , 100 , varCustID , SEQREADNO.CURRVAL); 

END;

错误消息:

*原因:触发器(或在其中引用的用户定义的plsql函数 此语句)试图查看(或修改) 在被触发它的语句修改的过程中.

*操作:重写触发器(或函数)，使其不读取该表.

这是否意味着我不应该从表Reading中检索任何详细信息?

我认为在插入值时从Table Reading检索数据时会发生此问题:

SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL;

Select MeterID INTO varMeterID
From Reading 
Where ReadNo = varReadNo; 

总有办法解决吗?还是有更好的方法来做到这一点?输入表Reading后，我需要在表BILL中插入新行，其中ReadNo需要引用我刚刚插入的ReadNo.

解决方案

您无法在行触发器中从同一表中检索记录.您可以使用:new和:old从实际记录中访问值(这是您的情况吗?).然后可以将触发器重写为

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

  varCustID   Varchar(10);

BEGIN 

  Select CustID INTO varCustID
    From Address A
    Join Meter M 
      on A.postCode = M.postCode
    Where M.MeterID = :new.MeterID;

  INSERT INTO BILL VALUES 
  (SEQBILLNO.NEXTVAL, SYSDATE, 'UNPAID' , 100 , varCustID , SEQREADNO.CURRVAL); 

END;

如果需要从READING表中查询其他记录，则必须使用语句触发器，行触发器和PLSQL集合的组合.很好的例子是在 AskTom.oracle.com

I have some problem executing the trigger below:

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

varReadNo   Int;
varMeterID  Int;
varCustID   Varchar(10);

BEGIN 

SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL;

Select MeterID INTO varMeterID
From Reading 
Where ReadNo = varReadNo;

Select CustID INTO varCustID
From Address A
Join Meter M 
on A.postCode = M.postCode
Where M.MeterID = varMeterID;

INSERT INTO BILL VALUES 
(SEQBILLNO.NEXTVAL, SYSDATE, 'UNPAID' , 100 , varCustID , SEQREADNO.CURRVAL); 

END;

Error Message:

*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger (or function) so it does not read that table.

Does it mean that I am not suppose to retrieve any details from table Reading?

I believe that the issue occur as I retrieving data from Table Reading while it is inserting the value:

SELECT SeqReadNo.CurrVal INTO varReadNo FROM DUAL;

Select MeterID INTO varMeterID
From Reading 
Where ReadNo = varReadNo; 

It's there anyway to resolve this? Or is there a better method to do this? I need to insert a new row into table BILL after entering the table Reading where the ReadNo need to reference to the ReadNo I just insert.

解决方案

You cannot retrieve records from the same table in a row trigger. You can access values from actual record using :new and :old (is this your case?). The trigger could then be rewritten to

CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW 

DECLARE

  varCustID   Varchar(10);

BEGIN 

  Select CustID INTO varCustID
    From Address A
    Join Meter M 
      on A.postCode = M.postCode
    Where M.MeterID = :new.MeterID;

  INSERT INTO BILL VALUES 
  (SEQBILLNO.NEXTVAL, SYSDATE, 'UNPAID' , 100 , varCustID , SEQREADNO.CURRVAL); 

END;

If you need to query other record from READING table you have to use a combination of statement triggers, row trigger and a PLSQL collection. Good example of this is on AskTom.oracle.com

这篇关于创建触发器以插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！