Using IF inside an Oracle trigger to change the update value-CodePudding

I'm trying to change the value of the update field within an IF statement when the new value does not match the backup column value. Can't figure it out, anybody done something like this before?

edit : I've gone thru many different versions of this, it either saves the new value anyway or causes an error.


create or replace TRIGGER Test_Trig
BEFORE UPDATE OF EMPID ON table1
FOR EACH ROW 
DECLARE
  backup_value table1.empid2%type;
BEGIN
  select empid2 into backup_value from table1 b where b.empid2 = :old.empid;
  if (:new.empid != empid2) then
    :new.empid := backup_value
  end if;
END;

CodePudding user response：

To me, it looks as if you should utilize trigger's when clause (so that you'd skip if) and - as already commented - don't select from the same table as you'll get mutating table error.

Something like this:

Sample table:

SQL> create table table1 as
  2  select 1 empid, 2 empid2 from dual;

Table created.

Trigger:

SQL> create or replace trigger test_trig
  2    before update of empid on table1
  3    for each row
  4    when (new.empid <> old.empid2)
  5  begin
  6    :new.empid := :old.empid2;
  7  end;
  8  /

Trigger created.

Testing:

SQL> select * From table1;

     EMPID     EMPID2
---------- ----------
         1          2

SQL> update table1 set empid = 5;

1 row updated.

SQL> select * from table1;

     EMPID     EMPID2
---------- ----------
         2          2    --> EMPID = EMPID2 = 2 because new EMPID value (5) is different 
                             from existing EMPID2 value (2)

SQL> update table1 set empid = 1;

1 row updated.

SQL> select * from table1;

     EMPID     EMPID2
---------- ----------
         2          2   --> nothing happened because EMPID is now equal to
                            EMPID2

SQL>

Because of code you suggested, EMPID now can't get any other value than current EMPID2. Maybe that's what you wanted, I can't tell.

[EDIT, based on your comment]

Maybe you'd rather raise an error (instead of silently setting the EMPID value back to its previous value).

SQL> create or replace trigger test_trig
  2    before update of empid on table1
  3    for each row
  4    when (new.empid <> old.empid2)
  5  begin
  6    raise_application_error(-20000, 'You can not modify EMPID value. Contact AskingForAFriend');
  7  end;
  8  /

Trigger created.

SQL> select * from table1;

     EMPID     EMPID2
---------- ----------
         1          2

SQL> update table1 set empid = 5;
update table1 set empid = 5
       *
ERROR at line 1:
ORA-20000: You can not modify EMPID value. Contact AskingForAFriend
ORA-06512: at "SCOTT.TEST_TRIG", line 2
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRIG'


SQL>