How to get a SQL trigger to increment by 1 on an update?-CodePudding

I am trying to create a trigger that updates one column on a specific row when it is updated but I am getting this error

ORA-04091: table table_name is mutating, trigger/function may not see it

when I go to test the trigger.

This is my trigger code:

CREATE OR REPLACE TRIGGER tblTrigger
AFTER UPDATE 
ON employees
FOR EACH ROW
BEGIN
    UPDATE employees 
    SET revisions = revisions   1 
    WHERE ID = ID;
END;
/

It is suppose to only update that certain Employees ID revisions

CodePudding user response：

You need to get rid of the BEGIN...END like this:

CREATE TRIGGER tblTrigger AFTER INSERT ON employees

FOR EACH ROW

UPDATE employees SET revisions = revisions   1 WHERE ID = NEW.ID;