Trigger after update ORACLE SQL

Question

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;

Привет. Am I right assuming, you just need to not allow users to insert zeroes or update to zeroes? I mean whenever a user inserts or updates to zero, the new value has to be null? — ekochergin
– ekochergin, Commented Oct 13, 2022 at 8:29
Is it possible SELECT INTO statement returns more than 1 row? It should only return one row because of the "INTO" clause. — HRK
– HRK, Commented Oct 13, 2022 at 8:45

MT0 · Accepted Answer · 2022-10-13 13:26:52Z

2

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

answered Oct 13, 2022 at 13:26

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger after update ORACLE SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related