Oracle Trigger with INSERT INTO table-CodePudding

I am trying to create an Oracle Trigger with After Update statement. It appears that I cannot create the trigger because I get prompted with Table does not exist error:

CREATE OR REPLACE TRIGGER USR1.CORE_FINISHED
AFTER UPDATE OF RUNTIME_STATUS
ON USR2.runtime_btc
FOR EACH ROW
WHEN ( new.RUNTIME_STATUS = 'FINISHED'
AND new.id = 'cr_daily')

BEGIN
INSERT INTO USR1.test_dq_log (BTC_NO, TBL_NAM, SCR_NAM)
SELECT BTC_NO, TBL_NAM, SCR_NAM
FROM USR2.EVENT_LG -- This is where the script is throwing errors
WHERE BTC_NO = :NEW.BTC_NO;
END;

What is weird is that the same expression when run outside of the trigger:

INSERT INTO USR1.test_dq_log (BTC_NO, TBL_NAM, SCR_NAM)
    SELECT BTC_NO, TBL_NAM, SCR_NAM
    FROM USR2.EVENT_LG 
    WHERE BTC_NO = 'any number here'

Seems to work smoothly and it inserts rows in the table!

CodePudding user response：

It looks as if you were granted privileges to insert into that table via role. Were you?

If so, it works at SQL level or anonymous PL/SQL blocks, but won't work in named PL/SQL procedures (e.g. stored procedures, functions, triggers) - you'll have to acquire that grant directly (not via role).