how to convert a mutating trigger to a stored procedure-CodePudding

I have the following trigger:

CREATE OR REPLACE TRIGGER planning_trig BEFORE
    UPDATE OF planned_remediation_date ON evergreen
    FOR EACH ROW
DECLARE
    planned_remediation_date DATE;
BEGIN
    SELECT
         planned_remediation_date
    INTO planned_remediation_date
    FROM
        evergreen
    WHERE
            hostname = :new.hostname
        AND instance_name = :new.instance_name
        AND product_home = :new.product_home
        AND it_service = :new.it_service
        AND sw_name = :new.sw_name;
    IF
        planned_remediation_date IS NOT NULL
        AND planned_remediation_date > trunc(sysdate)
    THEN
        UPDATE evergreen
        SET
            planning_status = 'planned';
    ELSE
        UPDATE evergreen
        SET
            planning_status = 'overdue';
    END IF;
END;
/

After an update of the row in my table evergreen I get this error:

ORA-04091: table PTR.EVERGREEN is mutating, trigger/function may not see it

I believe the error comes from the fact that I'm trying to update the very same table the trigger is firing on. I read that the best way to handle updates in the same table are stored procedures but I'm quite new to oracle and don't know how to achieve that. Another possibility I heard is AUTONOMOUS_TRANSACTION Pragma but not sure either if this applies to my case.

A little more background, the underlying table has a composite unique key (hostname,instance_name,product_home,it_service,sw_name) and I want to update an existing column on this table called planning_status based on the updated value of planned_remediation_date. If that value is not null and greater then today then update with planned else overdue.

CodePudding user response：

Expression planned_remediation_date IS NOT NULL is redundant. planned_remediation_date > trunc(sysdate) is never TRUE when planned_remediation_date is NULL.

Your trigger can be written much shorter:

CREATE OR REPLACE TRIGGER planning_trig 
    BEFORE UPDATE OF planned_remediation_date ON evergreen
    FOR EACH ROW
BEGIN
    IF :new.planned_remediation_date > trunc(sysdate) THEN
       :new.planning_status := 'planned';
    ELSE
       :new.planning_status := 'overdue';
    END IF;
END;
/

I guess you like to modify the updated row, not other rows in that table. Otherwise you would need a procedure or a COMPOUND trigger