oracle 创建触发器以插入另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13355171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create trigger to insert into another table
提问by FirstTimer
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.
*原因：触发器（或此语句中引用的用户定义的 plsql 函数）试图查看（或修改）正在被触发它的语句修改的表。
*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:
我相信在插入值时从 Table Reading 检索数据时会出现此问题：
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.
有没有办法解决这个问题？或者有没有更好的方法来做到这一点？我需要在进入表Reading 后在表BILL 中插入一个新行，其中ReadNo 需要引用我刚刚插入的ReadNo。
回答by Petr Pribyl
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
您不能在行触发器中从同一个表中检索记录。您可以使用 :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;
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
如果您需要从 READING 表中查询其他记录，您必须使用语句触发器、行触发器和 PLSQL 集合的组合。AskTom.oracle.com 上就是一个很好的例子
回答by J?cob
Add exception handling in your trigger and see what is happening, by doing it would be easy for you to track the exceptions.
在您的触发器中添加异常处理并查看发生了什么，这样您就可以轻松跟踪异常。
CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW
DECLARE
varCustID Varchar(10);
BEGIN
-- your code
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20299)));
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20298)));
END;
回答by Smart003
we can combined insert and select statement
我们可以结合插入和选择语句
CREATE OR REPLACE TRIGGER AFTERINSERTCREATEBILL
AFTER INSERT
ON READING
FOR EACH ROW
DECLARE
varCustID Varchar(10);
BEGIN
insert into bill
values
select SEQBILLNO.NEXTVAL,
SYSDATE,
'UNPAID' ,
100 ,
CustID,SEQREADNO.CURRVAL
From Address A
Join Meter M
on A.postCode = M.postCode
Where M.MeterID = :new.MeterID;
END;
try the above code.
试试上面的代码。
回答by RC.
Make sure that you have the necessary permissions on all the tables and access to the sequences you're using in the insert.
确保您对所有表拥有必要的权限，并有权访问您在插入中使用的序列。
I haven't done Oracle in awhile, but you can also try querying dba_errors (or all_errors) in order to try and get more information on why your SP isn't compiling.
我有一段时间没有使用 Oracle，但您也可以尝试查询 dba_errors（或 all_errors）以尝试获取有关您的 SP 未编译的原因的更多信息。
Something to the tune of:
有点像：
SELECT * FROM dba_errors WHERE owner = 'THEOWNER_OF_YOUR_SP';
