oracle-databasemergetriggersora-00904

I have problem compile trigger using merge


can You explain me, where I did mistake in my code ?

CREATE OR REPLACE TRIGGER mda_01.tr_01_t01003 AFTER
    INSERT ON mda_01.t01004_bo_semafor_log
    FOR EACH ROW
DECLARE
    PRAGMA autonomous_transaction;
BEGIN
    IF inserting THEN
        MERGE INTO mda_01.t01003_bo_semafor a
        USING (
                  SELECT
                      :new.semafor_name,
                      :new.semafor_ts,
                      :new.semafor_status,
                      :new.semafor_desc
                  FROM
                      dual
              ) b
         ON ( b.semafor_name = a.semafor_name )
        WHEN MATCHED THEN UPDATE
        SET a.semafor_ts = b.semafor_ts,
            a.semafor_status = b.semafor_status
        WHEN NOT MATCHED THEN
        INSERT (
            semafor_name,
            semafor_ts,
            semafor_status,
            semafor_desc )
        VALUES
            ( b.semafor_name,
              b.semafor_ts,
              b.semafor_status,
              b.semafor_desc );

    END IF;

    COMMIT;
END;
/

I get an error:

LINE/COL ERROR


5/6 PL/SQL: SQL Statement ignored

16/8 PL/SQL: ORA-00904: "B"."SEMAFOR_NAME": invalid identifier

Errors: check compiler log

Thanks, Paul


Solution

  • I solved the problem. I added aliases, and trigger was compiled with no errors:

     SELECT
                          :new.semafor_name as semafor_name,
                          :new.semafor_ts as semafor_ts,
                          :new.semafor_status as semafor_status,
                          :new.semafor_desc as semafor_desc
                      FROM
                          dual