oracle sql developer table is mutating

Question

i am trying to get a trigger to display an entire table called records_t upon a deletion, insertion or update.

so i wrote this

create or replace trigger display_rec
after insert or update or delete on records_t
for each row
declare
    cursor cur is
    select recno, recdate, recamt  from records_t;
begin
    loop
        DBMS_OUTPUT.PUT_LINE (c.recno || '   ' || c.recdate || '   ' || c.recamt);
    end loop;
end;

when I run it I get an error that the records table is mutating. please help

Answer 1

You can't access rows from table that is just being modified in a row-level trigger because ... well, as you know, table is mutating.

Then, you said that trigger whose code you posted raised an error. No way, it is invalid and can't be compiled (so it can't work) because

• you declared a cursor, but there's no cursor variable
• A loop? Through what? You never opened the cursor nor fetched from it ...
  • ... and you never exited the loop nor closed the cursor

Generally speaking, it isn't the best idea to claim something and post code that doesn't support those claims.

If you switched to a statement-level trigger and fixed code so that it is actually valid, then it does what you wanted - displays contents of the table:

SQL> set serveroutput on
SQL> create or replace trigger display_rec
  2    after insert or update or delete on records_t
  3  begin
  4    for cur in (select recno, recdate, recamt  from records_t)
  5    loop
  6      DBMS_OUTPUT.PUT_LINE (cur.recno || '   ' || cur.recdate || '   ' || cur.recamt);
  7    end loop;
  8  end;
  9  /

Trigger created.

SQL> select * from records_t;

     RECNO RECDATE       RECAMT
---------- --------- ----------
      7782 09-JUN-81     2572.5
      7839 17-NOV-81       5250
      7934 23-JAN-82       1365

SQL> update records_t set recamt = 100;
7782   09-JUN-81   100
7839   17-NOV-81   100
7934   23-JAN-82   100

3 rows updated.

SQL>

Although it now works, its usage is doubtful. Unless you (or end users) modify table using a tool that is capable of displaying dbms_output.put_line contents (such as SQL*Plus or SQL Developer or some other GUI tools), you won't see anything. For example, if you updated table via Oracle Forms application or Oracle Apex one, trigger would silently display output to nothing. It wouldn't fail, but you wouldn't see what it wrote.

Therefore, just being curious: what is its real purpose?

Answer 2

The problem seems that the PL/SQL code declares the cursor "cur" that is not being used in the loop. Instead, the loop is using an undefined variable "c". This will cause a compilation error. Additionally, the loop is infinite and will cause the records to mutate.