how to make a message after updating a value without canceling the update

Question

I have to check that the update of a note is not inferior to 10% with a trigger. if it is greater than 10% I have to adjust it to 10% and write an error message , and if the new note is under 0 i have to set it to 0

when i try raise_application error, the update is just cancel. and my order option DBMS does not work the update is done with the good value but without message

CREATE OR REPLACE TRIGGER C3_update
BEFORE UPDATE OF note on Inscription
FOR EACH ROW
WHEN (NEW.note < OLD.note*0.9)
begin
    if(:NEW.note > 0) then
    :NEW.note := :OLD.note*0.9;
    dbms_output.enable;
    dbms_output.put_line ('la note ne peut descendre de plus de 10%');
    --or
    --RAISE_APPLICATION_ERROR(-20111,'the note can not be less than 10%');
    end if;

    if(:NEW.note < 0) then 
    :NEW.note := 0;
    RAISE_APPLICATION_ERROR(-20011,'the note can not be under 0');
    end if;
  end;
  /

i would like to set the note and print message error rigth now i just cancel the update or i update the note in the good way but the message do not print

Answer 1

From my point of view, this is a wrong approach. Business rule shouldn't be enforced by a database trigger. If I were you, I'd put it into a front-end application (the same place where a new note value is being entered).

Doing so, you'd even be able to notify user what's happening and let them decide whether they want to accept a new value (which you'd suggest, based on those conditions), or not (and, possibly, enter a new, valid value).

As you've noticed, DBMS_OUTPUT.PUT_LINE doesn't work. It would, if tool you use to update the note value is capable of displaying it. (By the way, did you SET SERVEROUTPUT ON?) If you're using, for example, Oracle Forms, then you'd see nothing.

RAISE_APPLICATION_ERROR, when called, ends the subprogram and returns a user-defined error number and message. However, it also nullifies the update you make. Even if you set the trigger to be an autonomous transaction, it won't work. For example:

SQL> drop table inscription;

Table dropped.

SQL> create table inscription (note number);

Table created.

SQL> insert into inscription (note) values (2);

1 row created.

SQL> create or replace trigger c3_update
  2    before update of note on inscription
  3    for each row
  4    when (new.note < old.note * 0.9)
  5  declare
  6    pragma autonomous_transaction;
  7    l_note inscription.note%type;
  8    l_info varchar2(100);
  9  begin
 10    if :new.note > 0 then
 11       :new.note := :old.note * 0.9;
 12       l_info := 'the note can not be less than 10%';
 13    elsif :new.note < 0 then
 14       :new.note := 0;
 15       l_info := 'the note can not be under 0';
 16    end if;
 17    -- Let's hope that COMMIT will save the updated value
 18    select note into l_note from inscription;
 19    dbms_output.put_line('Note before commit = ' || l_note);
 20    commit;
 21    select note into l_note from inscription;
 22    dbms_output.put_line('Note after commit = ' || l_note);
 23
 24    -- Raise the "error", hoping that it'll just display a message,
 25    -- but updated value will remain "as is"
 26    raise_application_error(-20001, l_info);
 27  end;
 28  /

Trigger created.

SQL> set serveroutput on
SQL> update inscription set note = -1;
Note before commit = 2
Note after commit = 2
update inscription set note = -1
       *
ERROR at line 1:
ORA-20001: the note can not be under 0
ORA-06512: at "SCOTT.C3_UPDATE", line 22
ORA-04088: error during execution of trigger 'SCOTT.C3_UPDATE'


SQL> select * from inscription;

      NOTE
----------
         2

See? Instead of 0, note keeps its original value (2). It never even got value 0 because trigger didn't end successfully. How to check that? By commenting the RAISE command:

SQL> create or replace trigger c3_update
  2    before update of note on inscription
  3    for each row
  4    when (new.note < old.note * 0.9)
  5  declare
  6    pragma autonomous_transaction;
  7    l_note inscription.note%type;
  8    l_info varchar2(100);
  9  begin
 10    if :new.note > 0 then
 11       :new.note := :old.note * 0.9;
 12       l_info := 'the note can not be less than 10%';
 13    elsif :new.note < 0 then
 14       :new.note := 0;
 15       l_info := 'the note can not be under 0';
 16    end if;
 17    -- Let's hope that COMMIT will save the updated value
 18    select note into l_note from inscription;
 19    dbms_output.put_line('Note before commit = ' || l_note);
 20    commit;
 21    select note into l_note from inscription;
 22    dbms_output.put_line('Note after commit = ' || l_note);
 23
 24    -- Raise the "error", hoping that it'll just display a message,
 25    -- but updated value will remain "as is"
 26    --raise_application_error(-20001, l_info);
 27  end;
 28  /

Trigger created.

SQL> set serveroutput on
SQL> update inscription set note = -1;
Note before commit = 2
Note after commit = 2

1 row updated.

SQL> select * from inscription;

      NOTE
----------
         0

It does the job, but - the message can't be displayed (I already told you why).

If you meant to include UPDATE inscription set note = its_new_value into the trigger itself, don't - you'll get a deadlock as the initial update fires a trigger which performs update which calls a trigger which performs update etc.

Therefore, as far as I can tell, you can't do what you want, not with a trigger.