After Insert Trigger sintax error - ORA-00922

Question

There is a table which i want to update each row after inserted, when the user_type of my schedule table is a "superuser". I tried to convert the table/column names to "simplify", so some names may not make sense, it's ok. The trigger code:

CREATE OR REPLACE TRIGGER "TR_UPT_SCHEDULE"
AFTER INSERT
ON SCHEDULE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE V_UserCode VARCHAR2(20);

    BEGIN
    --Find "super user" code.
    SELECT UserTypeCode INTO V_UserCode FROM
    (SELECT UL.User_Code,
       UFT.UserTypeCode,
       ROW_NUMBER() OVER (PARTITION BY UL.User_Code ORDER BY UF.UserTypeCode DESC) RN
FROM UserLogin UL
JOIN UserFunction UF
    ON UL.User_Code = UF.User_Code
JOIN UserFuncType UFT
    ON UFT.UserTypeCode = UF.UserTypeCode
WHERE UFT.FuncType = 'S'
        ) WHERE RN = 1;

EXCEPTION
WHEN NO_DATA_FOUND THEN
    V_UserCode := NULL;

    IF V_UserCode IS NOT NULL
          THEN UPDATE SCHEDULE
          SET :NEW.UserTypeCode = V_UserCode,
              OrigScheType = :NEW.UserTypeCode
          WHERE CompCode = :NEW.CompCode
          AND UserTypeCode = :NEW.UserTypeCode
          AND ScheOrigin = :NEW.ScheOrigin
          AND ScheCode = :NEW.ScheCode;
    END IF;
END;

When i try to create the trigger, is returned the fallowing error: ORA-00922 I'm pretty sure the problem is the UPDATE part, where tried compare in the WHERE clause, the Schedule table keys to be sure of which row i'm updating(inserted), but i could not realize the problem.

Answer 1

In triggers, you don't update tablename set :new.columnname = some value, you directly assign values to :new.columnname using PL/SQL assignments or select into. Therefore, from a syntax point of view, the final update should be replaced with something like:

:new.usertypecode := v_usercode;
:new.origschetype := v_usercode;

(I am assuming the update is only intended to apply to the current row. If the idea is to update multiple rows in the triggering table, you can't do that in a row-level trigger.)

However, this logic doesn't look like it will work:

exception
    when no_data_found then
        v_usercode := null;
    
        if v_usercode is not null then

v_usercode has to be null at this point, so you don't have a value to set :new.usertypecode to, and I'm not sure what you want the trigger to do.