oracle 如何在同一张表上查询和执行触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12753647/
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
How to query and execute trigger on the same table
提问by user955165
Hope someone can help with this. I am new to triggers and I am trying to create a trigger that checks to see if the record being modified has a specific value.
希望有人可以帮助解决这个问题。我是触发器的新手，我正在尝试创建一个触发器来检查正在修改的记录是否具有特定值。
example I have a table called Filing that has a filing_id and a filing_status, I want to prevent someone from updating or deleting any records in that table has a filing_status="FILED". so if i have the following
例如，我有一个名为 Fileing 的表，它有一个filing_id 和一个filing_status，我想防止有人更新或删除该表中的任何记录，该表中的任何记录都是filing_status="FILED"。所以如果我有以下几点
Filing_id Filing_status Val
--------- ------------- ---
0 Filed X
If someone tried to modify Val the trigger should stop it I have created the following trigger:
如果有人试图修改 Val 触发器应该停止它我创建了以下触发器：
CREATE or replace TRIGGER TRG_PREV_FILING
BEFORE DELETE or UPDATE
ON PF.FILING
FOR EACH ROW
declare
rowcnt number;
BEGIN
SELECT COUNT(filing_id) INTO rowcnt FROM PF.FILING
where status = 'FILED'
and filing_id = :new.filing_id;
if (rowcnt > 0)
then
raise_application_error (-20100, 'You can not delete Or Update initial record');
end if;
END;
The problem I am facing is I am getting:ORA-04091 which is "Table Filing is mutating, Trigger/function may not see it"
我面临的问题是：ORA-04091，即“表归档正在发生变化，触发器/函数可能看不到它”
So basically I can't query on the same table that I am executing the trigger on? Is that the problem in my case and does anyone know a work around this?
所以基本上我不能在我正在执行触发器的同一个表上查询？这是我的问题吗，有没有人知道解决这个问题的方法？
I appreciate any help
我感谢任何帮助
回答by Nick Krasnov
You do not have to query the table trigger is firing on to be able to do that kind of check. You can get value of a column that is being modified using :old. Here is an example:
您不必查询正在触发的表触发器即可进行此类检查。您可以使用 获取正在修改的列的值:old。下面是一个例子：
SQL> create table filing(
2 status varchar2(31),
3 val number
4 );
Table created
SQL> create or replace trigger TRG_FILING before delete or update on FILING
2 for each row
3 begin
4 if lower(:old.status) = 'filed'
5 then
6 raise_application_error(-20000, 'You cannot delete or modify this record');
7 end if;
8 end;
SQL> /
Trigger created
SQL> insert into FILING values('FILED', null);
1 row inserted
SQL> insert into filing values('OK', 1);
1 row inserted
SQL> commit;
Commit complete
SQL> select *
2 from filing;
STATUS VAL
------------------------------- ----------
FILED
OK 1
SQL> delete
2 from filing
3 where val is null;
ORA-20000: You cannot delete or modify this record
ORA-06512: at "HR.TRG_FILING", line 4
ORA-04088: error during execution of trigger 'HR.TRG_FILING'
回答by hol
The basic point is that you should design you database in a way that the trigger does its validation based on the updated/deleted row. If you have several rows with the same filing_idthen you can overwork you database design. Maybe you really only check against the own table in which case you can use :old. But when you have several rows to check (which I assume because you make a count) then you have to use two tables. Here is a suggestion.
基本点是您应该以触发器根据更新/删除的行进行验证的方式设计数据库。如果您有几行相同的行，filing_id那么您可能会过度使用数据库设计。也许您真的只检查自己的表，在这种情况下您可以使用:old. 但是当您有几行要检查时（我假设是因为您进行计数），那么您必须使用两个表。这是一个建议。
create table filing_status (filing_id number, status varchar2(10));
create table filing_content (filing_id number, content_id number, content varchar2(200));
CREATE or replace TRIGGER TRG_PREV_FILING
BEFORE DELETE or UPDATE
ON FILING_content
FOR EACH ROW
declare
rowcnt number;
BEGIN
SELECT COUNT(filing_id) INTO rowcnt FROM FILING_status
where status = 'FILED'
and filing_id = :new.filing_id;
if (rowcnt > 0)
then
raise_application_error (-20100, 'You can not delete Or update filed record');
end if;
END;
/
insert into filing_status values (1, 'FILING');
insert into filing_content values (1, 1, 'foo');
insert into filing_content values (1, 2, 'bar');
insert into filing_status values (1, 'FILED');
update filing_content set content = 'bahr' where filing_id = 1 and content_id = 2;
ERROR at line 1:
ORA-20100: You can not delete Or update filed record
ORA-06512: at "DEMO.TRG_PREV_FILING", line 9
ORA-04088: error during execution of trigger 'DEMO.TRG_PREV_FILING'
