Oracle突变触发器 [英] Oracle Mutating Trigger

问题描述

我正在编写一个简单的触发器，该触发器应该仅发送一条消息，其中包含更新的行数以及Gender的旧值和Gender的更新值。但是，当我运行更新时，我收到表正在突变的错误，并且表可能看不到它，但我不确定为什么。

I am writing a simple trigger that is supposed to just send a message with the updated Count of rows as well as the old value of Gender and the updated value of Gender. When i run an update however I am getting the error that the table is mutating and the table might not be able to see it but I'm not exactly sure why.

触发器

create or replace trigger updatePERSONS
after update 
on PERSONS
for each row
declare
n int; 
oldGender varchar(20):= :OLD.Gender;
newGender varchar(20):= :NEW.Gender;

begin
select Count(*)
into n
from PERSONS;

if (oldGender != newGender) then
dbms_output.put_line('There are now '|| n || ' rows after update. Old gender: ' || oldGender
|| ', new Gender: ' || newGender);

end if;
End;

`

我知道它必须在开始之后使用select语句执行操作，但是我还能如何获得行数？

i know it has to do with the select statement after begin but how else would i get count of rows?

推荐答案

@San指出，一行个人的级别触发器通常不能查询个人表。

As @San points out, a row-level trigger on persons cannot generally query the persons table.

您将需要两个触发器，一个行级触发器（可以查看旧性别和新性别）和一个语句级触发器（可以进行计数）。如果您使用的是11g，也可以使用行级和语句级块创建复合触发器。

You'd need two triggers, a row-level trigger that can see the old and new gender and a statement-level trigger that can do the count. You could also, if you're using 11g, create a compound trigger with both row- and statement-level blocks.

create or replace trigger trg_stmt
  after update
  on persons
declare
  l_cnt integer;
begin
  select count(*)
    into l_cnt
    from persons;

  dbms_output.put_line( 'There are now ' || l_cnt || ' rows.' );
end;

create or replace trigger trg_row
  after update
  on persons
  for each row
begin
  if( :new.gender != :old.gender )
  then
    dbms_output.put_line( 'Old gender = ' || :old.gender || ', new gender = ' || :new.gender );
  end if;
end;

这篇关于Oracle突变触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！