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

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;
/

​x
 
CREATE OR REPLACE TRIGGER tblTriggerAFTER UPDATE ON employeesFOR EACH ROWBEGIN    UPDATE employees     SET revisions = revisions + 1     WHERE ID = ID;END;/​

It is suppose to only update that certain Employees ID revisions

Answer

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;

 
CREATE TRIGGER tblTrigger AFTER INSERT ON employees​FOR EACH ROW​UPDATE employees SET revisions = revisions + 1 WHERE ID = NEW.ID;​

Advertisement

Answer