CREATE TABLE CARD_FILE
          (IDs        NUMBER(3),
           Surname    VARCHAR2(15),
           Dept_code  NUMBER(3));
           
INSERT INTO CARD_FILE
VALUES (1, 'Smith', 10);
INSERT INTO CARD_FILE
VALUES (2, 'Tomma', 10);
INSERT INTO CARD_FILE
VALUES (3, 'Gerk', 20);
INSERT INTO CARD_FILE
VALUES (4, 'Krake', 20;
INSERT INTO CARD_FILE
VALUES (6, 'Dugal', 30);
INSERT INTO CARD_FILE
VALUES (7, 'Kate', 30);
INSERT INTO CARD_FILE
VALUES (8, 'Dugal', 30);
INSERT INTO CARD_FILE
VALUES (9, 'Dugal', 50);
INSERT INTO CARD_FILE
VALUES (10, 'Dugal', 50);

CREATE OR REPLACE
TRIGGER mod_dept 
AFTER UPDATE OF dept_code
ON card_file FOR EACH ROW
DECLARE pragma autonomous_transaction;
dept_code NUMBER(4);
BEGIN
  IF :NEW.dept_code<>:OLD.dept_code THEN
  BEGIN UPDATE card_file 
  SET dept_code=:NEW.dept_code
  WHERE dept_code=:OLD.dept_code; 
  END;
  END IF;
  COMMIT;
END;

UPDATE CARD_FILE SET Dept_code=40
WHERE Surname='Oldmann';

SQL> SELECT * FROM CARD_FILE
  2  WHERE Surname='Tomma';

       IDS SURNAME                                        DEPT_CODE
---------- --------------------------------------------- ----------
         2 Tomma                                                 10

SQL> UPDATE CARD_FILE SET Dept_code=40
  2  WHERE Surname='Tomma';

1 row updated.

SQL> SELECT * FROM CARD_FILE
  2  WHERE Surname='Tomma';

       IDS SURNAME                                        DEPT_CODE
---------- --------------------------------------------- ----------
         2 Tomma                                                 40