Trigger after update ORACLE SQL-CodePudding

I want to write a oracle sql trigger that updates one value on the table after an insert or update. I have to check if the value is 0 and if it is then to set this value to NULL. When I compile the trigger everything is ok but when I need to update the table it says that the table is mutating. I've put pragma autonomous_transaction but when I update it return 'ORA-01422 exact fetch return more than requested number of rows'. I made a before trigger that works but I want to write after update or insert.I tried to use INSERT INTO instead of UPDATE but it return ORA-01400.Is there any chance to write it with AFTER INSERT OR UPDATE trigger or it must be BEFORE That's my code:

CREATE OR REPLACE TRIGGER trigger
AFTER INSERT OR UPDATE ON table1
FOR EACH ROW
DECLARE
i NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT col1 INTO i FROM table1;

IF i = 0 THEN
  UPDATE table1 SET col1 = 0;
END IF;

 END;

CodePudding user response：

I made a before trigger that works but I want to write after update or insert.

You cannot use an AFTER trigger as the value has already been entered into the table and you cannot modify the entered value in an AFTER trigger.

You need to use a BEFORE trigger and use the :NEW record (rather than trying to select the value from the table):

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  IF :NEW.col1 = 0 THEN
    :NEW.col1 := NULL;
  END IF;
END;
/

fiddle