Hello. I know that the topic of mutation was touched more than once, but still I can not find a solution to my problem. I need to register a trigger using a loop that checks the mail of people for the presence of the @ badge and if not, then a message is displayed that the mail is wrong.

What I have:

create or replace trigger BeforeUpdate BEFORE UPDATE on SUPLIER for each row declare email varchar2(28); counter number; fin number; line number := 0; BEGIN select count(id_suplier) into counter from suplier; select count(semail) into fin from suplier; LOOP line := line + 1; select semail into email from suplier where id_suplier = line; if email not like '%@%' then DBMS_OUTPUT.PUT_LINE('Suplier no.' || line || ' has wrong email type'); end if; exit when line = fin; end LOOP; END; /

When the trigger is activated, a mutation is obtained. There was an idea to create a separate table for mailboxes, but then the cycle is lost.

I would be grateful for any help :)

  • You write a trigger for each row , i.e. for each row being modified, it is called separately. There is no point in the cycle to check all the records in the table when modifying each of them. The trigger should check only the current record, i.e. field :new.semail . no cycles and select is necessary, the DB will do everything for you - Mike
  • In general, a line-by-trigger trigger does not have the right to access the table on which it is written. - Mike
  • And by the way, you know that if the semail fields are filled for all rows, then count (id_suplier) is equal to count (semail) and equal to the number of records in the table. Nor does this number have any relation to ID records, because they can not start from 1 and go in a row. So such a cycle, no matter where it is located, will not work. - Mike
  • @Mike, yes, I know, but given, unfortunately, such a task (purely for the purpose of studying the syntax) and according to the condition ID goes from 1 and in order - YuiKan
  • Then do it in the general trigger on the table, without for each row - Mike

1 answer 1

As already noted in the comments, it is somewhat meaningless to refer to the entire table being changed in the line-by-line trigger. Only to the line that is specifically modified by: new and: old pseudo-entries. Cycles, if there are no references to a variable table in them, nothing to do with it. Mutation in the first select in your code. But if you really want, you can:

 create table mails (id number, mail varchar2(64)); --Table MAILS created. insert into mails select * from ( select 1, 'my@mail.ru' from dual union all select 2, 'other(at)mail.ru' from dual ); --2 rows inserted. create or replace trigger mails_trig before update of mail on mails for each row declare misformatted number; begin select count(1) into misformatted from mails where mail is not null and not regexp_like(mail, '\w+@\w+\.\w+') ; --dbms_output.put_line('alrady misformatted emails ' || misformatted || ' checked by update row ' || :old.id || '/' || :old.mail); end; / --Trigger MAILS_TRIG compiled update mails set mail = '_' || mail where id=1; SQL Error: ORA-04091: table SH1.MAILS is mutating, trigger/function may not see it ORA-06512: at "SH1.MAILS_TRIG", line 4 ORA-04088: error during execution of trigger 'SH1.MAILS_TRIG' 04091. 00000 - "table %s.%s is mutating, trigger/function may not see it" *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. --Error report - --SQL Error: ORA-04091: table SH1.MAILS is mutating, trigger/function may not see it

How not to agree, we will rewrite

 create or replace trigger mails_trig before update of mail on mails for each row declare function foundMisformatted return boolean is misformatted number; pragma autonomous_transaction; begin select count(1) into misformatted from mails where mail is not null and not regexp_like(mail, '\w+@\w+\.\w+') ; dbms_output.put_line('mails_trig: alrady misformatted emails ' || misformatted || ' - checked by update row ' || :old.id || '/' || :old.mail); return misformatted>0; end; begin if foundMisformatted then --raise_application_error(-20009, 'misformatted mails.mail found'); --ΠΈΠ»ΠΈ, Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ Π±ΠΎΠ»Π΅Π΅ ΠΆΠΈΠ·Π½Π΅Π½ΠΎΠ΅ - пускай Π½Π΅ΠΎΠΏΡ‹Ρ‚Π½Ρ‹ΠΉ ΠΊΠΎΠ»Π»Π΅Π³Π° Π΄Π΅Π½Ρ‘ΠΊ ΠΎΡˆΠΈΠ±ΠΊΡƒ ΠΏΠΎΠΈΡ‰Π΅Ρ‚ dbms_output.put_line('mails_trig: warning: misformatted mails.mail found'); :new.mail := :old.mail; return; end if; -- do anything end; / --Trigger MAILS_TRIG compiled update mails set mail = '_' || mail where id=1; mails_trig: alrady misformatted emails 1 - checked by update row 1/my@mail.ru mails_trig: warning: misformatted mails.mail found drop trigger mails_trig; drop table mails;